Unable to use update between two tables in postgresql using Query Builder

[Thavarajan]

Laravel Version

11 It might even exist from version 6

PHP Version

8.2

Database Driver & Version

postgres

Description

Hi team,
First of all thanks for this illuminated framework, found a bug for PostgreSQL update

DB::table('post')
  ->leftJoin('post_history', function ($join) {
      $join->on('post_history.post_id', '=', 'post.id');
  })
  ->where('post_history.post_status', postStatus::assigned)
  ->update([
      "post.last_comment" => DB::raw("post_history.comments")
  ]);

This query is working in Laravel 5.8, after migrating to laravel 11 this is not working
probably this commit

illuminate/database@4e1130c

make the above code unusable

Steps To Reproduce

Create the following table in any postgres database

post table with id, comment, last_comment
post_history table with id, post_id, post_status, comments

fill both tables with some record

run the code below

  DB::table('post')
        ->leftJoin('post_history', function ($join) {
            $join->on('post_history.post_id', '=', 'post.id');
        })
        ->where('post_history.post_status', postStatus::assigned)
        ->update([
            "post.last_comment" => DB::raw("post_history.comments")
        ]);

[crynobone]

Can you provide the generated SQL on Laravel 5 vs Laravel 11?

[Thavarajan]

from the code, below is my understanding
all the joins are converted into where clause, where the current code only works in the subquery basis this is wrong
the current query is produced like below

update "post" set "last_comment" = post_history.comments 
where "ctid" in (
    select "post"."ctid" 
from "post" left join "post_history" on "post_history"."fk_post_id" = "post"."id" 
where "post_history"."post_status" = 2)

currently, I do not have the 5.8 version,
but from the code available in the 5.8 version,
it may generate something like below

update "post" set "last_comment" = post_history.comments 
FROm "post_history"
where "post_history"."fk_post_id" = "post"."id" 
AND "post_history"."post_status" = 2 

this one is the better than the current one

[staudenmeir]

Hi @Thavarajan,
You need to switch to updateFrom() to get the original query:

DB::table('post')
  ->leftJoin('post_history', function ($join) {
      $join->on('post_history.post_id', '=', 'post.id');
  })
  ->where('post_history.post_status', postStatus::assigned)
  ->updateFrom([
      "post.last_comment" => DB::raw("post_history.comments")
  ]);

[Thavarajan]

@staudenmeir thanks let me check, we are using the same code base for mssqlservrr too, let me confirm this for cross database operation