Broken update query with version v1.5.3

[bozhidarc]

After migrating from version v1.0.7 to v1.5.3, a query builder cte expression is now broken.
I'm using Laravel v8.83.26.

This is the code:

$q = DB::table("table_b")->select(
    DB::raw('distinct on col_1'), 
    'col_2',
    'col_3',
)
->where('col_4', '=', 55)

self::withExpression('my_cte', $unitsToUpdate)
    ->join('my_cte', DB::raw('my_cte.col_4'), '=', 'table_a.col_4')
    ->update([
        'col_1' => DB::raw('my_cte.col_3'),
    ]);

The think that made me wonder is what is this ctid column so after some digging I found in compileUpdateWithJoinsOrLimit() in vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/PostgresGrammar.php

And the generated query is:

update
    "table_a"
set
    "col_1" = my_cte.grouping,
where
    "ctid" in (with "my_cte" as (
    select
        distinct on
        col_1,
        "col_2",
        "col_3",
    from
        "table_b"
    where
        "col_4" = 55)
    select
        "table_a"."ctid"
    from
        "table_a"
    inner join "my_cte" on
        my_cte.col_4 = "table_a".col_4);

[staudenmeir]

Hi @bozhidarc,
What did the generated query look like before? What exact Laravel version were you using?

[bozhidarc]

Hi @staudenmeir,
We are in the process of migrating from v5.7.29 to v8 and the query looked like that.

with "my_cte" as (
    select
        distinct on
        col_1,
        "col_2",
        "col_3",
    from
        "table_b"
    where
        "col_4" = 55
)
update
    "table_a"
set
    "col_1" = my_cte.col_3
from
    "my_cte"
where
    my_cte.col_4 = table_a.col_4

[staudenmeir]

a query builder cte expression is now broken.

Is there an error or is it (just) producing a different result?

[bozhidarc]

The error it throws is Undefined table: 7 ERROR: missing FROM-clause entry for table "my_cte"..
This is because of the structure of the produced query.

[staudenmeir]

The underlying issue is actually a breaking change in Laravel 6 (caused by me, coincidentally):

• [6.x] Improve UPDATE and DELETE queries on PostgreSQL laravel/framework#29393 refactored UPDATE queries on PostgreSQL to fix a few issues with unsupported (edge) cases. Unfortunately, this broke queries like yours.
• [8.x] Re-add update from support for PostgreSQL laravel/framework#39151 introduced updateFrom() to replicate the original behavior.

I released a new version that adds support for updateFrom():

• Update to v1.5.5.
• Use updateFrom() instead of update() for your query.

[bozhidarc]

Thank you @staudenmeir!
Now it works as before.