Ajax to PHP: how to better parse for postgreSQL WHERE with CASE query

Question

So, I have this scenario: I need to query some activities with version numbers. Some of those have only one version, others have multiple. They all come from a form, which already uses jQuery to add new form elements and retrieve their values. PHP Ajax is already working.

I thought about building a the query around a $case php variable, kinda like:

$query = "SELECT DISTINCT activity_number, version_number, person_id, conclusion_date
FROM table1
WHERE fixed_parameter
$case
";

At first I was struggling with "I need to make the case variable as = when there's a single version being queried, but I need IN when there's multiple. Afterwards, it hit me that I can use IN for both.

But now I don't know if I should try to treat the multiple version cases on the javascript side or the PHP side, and which would be easier/better.

Currently, I'm sending everything, so I get something like this as form data being passed to PHP:

ativities: [{"activity_number":"12044","version_number":"4"},{"activity_number":"12044","version_number":"7"},{"activity_number":"41796","version_number":"0"}]

If this gets sent to PHP, I'll end up with:

AND CASE activity_number
WHEN 12044 THEN version_number IN (4)
WHEN 12044 THEN version_number IN (7)
WHEN 41796 THEN version_number IN (0)
END

and that obviously won't work. So I need to merge them by activity_number, to get something like {"activity_number":"12044","version_number":"4,7"}. I've dug around, found a couple weird scripts but they were too custom for their answers to easily fit in here.

The furthest I think I've got was using this answer Javascript - Reduce array with multiple-key objects but I don't actually understood it's logic, so I'm having a real hard time adapting it to my needs.

Thanks @AlonEitan, you've got any leads for me? Be mindful that this isn't going to be running out in the open, and there are measures for security in place. — João Ciocca, Sep 25 '18 at 22:33
https://stackoverflow.com/questions/14113256/group-array-values-based-on-key-in-php — Alon Eitan, Sep 25 '18 at 22:37

score 0 · Answer 1 · answered Sep 26 '18 at 15:59

Thanks to Alon Eitan's pointer I think I've managed to find a way. Took me a while to cover all scenarios, considering one element or an array of version numbers, but here's what worked:

foreach ($activities as $line) {
    $activity_number = $line->activity_number;
    $version_number = $line->version_number;
    if (!isset($out[$activity_number])):
        if (!empty($out)):
            $out[$activity_number] = $line->version_number;
        else:
            $out[$activity_number] = (array)$line->version_number;
        endif;
    elseif (isset($out[$activity_number])):
        if (is_array($out[$activity_number])):
            if (!in_array($version_number,$out[$activity_number])):
                array_push($out[$activity_number],$version_number);
            endif;
        else:
            if ($out[$activity_number] != $version_number):
                array_push($out[$activity_number],$version_number);
            endif;
        endif;
    endif;
}

foreach ($out as $key => $case){
    $caseAtividades .= "WHEN ".$key." THEN nu_versao IN (".implode(',',$case).")
    ";
}

This way, even if an activity is repeated out of order, their version number will still be checked for already being there, and if not, get included!

Ajax to PHP: how to better parse for postgreSQL WHERE with CASE query

1 Answers1