I found the solution. The key is to type cast the WHEN ? THEN ? like:
$cases[] = "WHEN ?::integer THEN ?::integer";
I have a function updateOrderOfSteps($list) getting a list of ids and the order number in a list.
I can update the step_number field in the Step models using:
foreach ($list as $index => $step) {
Step::where('id', $step['value'])
->update(['step_number' => $step['order']]);
}
However, the above creates several SQL queries (1 for each step). For this I thought of using another approach
$ids = array_column($list, 'value');
$cases = [];
$params = [];
foreach ($list as $step) {
$cases[] = "WHEN ? THEN ?";
$params[] = (int) $step['value'];
$params[] = (int) $step['order'];
}
$ids = implode(',', $ids);
$cases = implode(' ', $cases);
$query = "UPDATE steps SET step_number = CASE id {$cases} END WHERE id IN ({$ids})";
DB::update($query, $params);
This produces the following $query and params
"UPDATE steps SET step_number = CASE id WHEN ? THEN ? WHEN ? THEN ? WHEN ? THEN ? END WHERE id IN (23,25,24)"
array:6 [
0 => 23
1 => 1
2 => 25
3 => 2
4 => 24
5 => 3
]
However, it gives a datatype mismatch error like:
SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "step_number" is of type integer but expression is of type text
LINE 1: UPDATE steps SET step_number = CASE id WHEN $1 THEN $2 WHEN ...
^
HINT: You will need to rewrite or cast the expression. (SQL: UPDATE steps SET step_number = CASE id WHEN 25 THEN 1 WHEN 23 THEN 2 WHEN 24 THEN 3 END WHERE id IN (25,23,24))
What steps can I take to even troubleshoot what is going on as I cannot even run toSql or how can I fix this?
Please or to participate in this conversation.