CLab's avatar
Level 3

Getting Datatype mismatch error when trying to run raw SQL update

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?

0 likes
7 replies
CLab's avatar
Level 3

I found the solution. The key is to type cast the WHEN ? THEN ? like:

$cases[] = "WHEN ?::integer THEN ?::integer";
CLab's avatar
Level 3

@MohamedTammam probably. But since I will only use this once, I would like to avoid a dependency if possible.

MohamedTammam's avatar

@CLab I see it's doing what you did under the hood

I would use the package since it has 449 stars and seems to be well maintained. even if I do it only once.

CLab's avatar
Level 3

@MohamedTammam if it is doing something similar to what I did then why would I want to use the package? What is wrong with the way I have done it?

MohamedTammam's avatar

@CLab Nothing wrong, I just preferer to use a package if it's available to keep that part of the code maintained and documented.

That's just my preferences.

CLab's avatar
Level 3

@MohamedTammam Don't get me wrong - if there was more use for this in my application I would gladly install the package. But since I am only using this in one part of my app, I don't think it would add more value. Rather every dependency then slows down the upgrade process for Laravel versions. So I prefer to not add too many packages.

Please or to participate in this conversation.