Framework: Postgresql update: removed support for "update ... from ..." statement in 6.x and 7.x

[hyde1]

• Laravel Version: 6.0.0
• PHP Version: 7.4.4
• Database Driver & Version: PostgreSQL 10

Description:

It seems that this commit: 3495ebd7c830ed37b737003d58d991038b479616 removed support for update ... from ... statement with the PostgreSQL.

The following code:

$db->table('users')
    ->joinSub(function ($query) use ($strat_date, $end_date, $db) {
        $query->from('invoices')
            ->whereBetween('created_at', $start_date, $end_date)
            ->groupBy('user_id')
            ->select('user_id', $db->raw('SUM(amount) as amount, COUNT(*) as c, MAX(created_at) as created_at'));
    }, 'sq', 'users.id', '=', 'sq.user_id')
    ->update([
        'total_amount' => $db->raw('total_amount + sq.amount'),
        'nb_invoices' => $db->raw('nb_invoices + sq.c'),
        'last_invoice_date' => $db->raw('sq.created_at')
    ]);

gave the following statement, in version 5.8 (i added line breaks and indentation for lisibility):

update "users"
set "total_amount" = total_amount + sq.amount, "nb_invoices" = nb_invoices + sq.c, "last_invoice_date" = sq.created_at
from (select "user_id", MAX(created_at) as created_at, SUM(amount) as amount, COUNT(*) as c
    from "invoices" where "created_at" between ? AND ? group by "user_id"
) as "sq"
where "users"."id" = "sq"."user_id"

From version 6.0.0, it gives the request below which results in an SQL error:

update "users"
set "total_amount" = total_amount + sq.amount,
    "nb_invoices" = nb_invoices + sq.c,
    "last_invoice_date" = sq.created_at
where "ctid" in (
    select "users"."ctid"
    from "users"
    inner join (
        select "user_id", MAX(created_at) as created_at, SUM(amount) as amount, COUNT(*) as c
        from "invoices"
        where "created_at" between ? AND ?
        group by "user_id"
    ) as "sq" on "users"."id" = "sq"."user_id")

Which results in the following sql error:

SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "sq"

[driesvints]

Ping @staudenmeir

[vitsw]

I've just got the same problem during the upgrade to Laravel 6.

Unfortunately, I can't provide the example from the real codebase.

_But for instance_: I need to update some rows in a table using data from a subquery.

$aggregationQuery = CollectionItems::query()
                    ->select([
                        'collection_id',
                        'total_amount' => DB::raw('sum(amount)'),
                        'avg_something_else' => DB::raw('avg(something_else)'),
                    ])
                    ->groupBy('collection_id');

So to update target table I'm using Postgresql update ... from clause:

Collections::joinSub($aggregationQuery, 'calculated_results', 'id', 'calculated_results.collection_id')
            ->whereIn('id', [1, 5, 7])
            ->update([
                'total_amount'        => DB::raw('calculated_results.total_amount'),
                'avg_something_else ' => DB::raw('calculated_results.avg_something_else')
            ]);

In Laravel 5.8 it used to generate such query as below:

update collections
set
    total_amount = calculated_results.total_amount,
    avg_something_else = calculated_results.avg_something_else
from (
    select 
          collection_id, sum(amount) as total_amount, avg(something_else) as avg_something_else  
    from collection_items 
    group by collection_id
) as calculated_results where id = calculated_results.collection_id and id in (1, 5, 7)

now it generates

update "collections"
set
    total_amount = calculated_results.total_amount,
    avg_something_else = calculated_results.avg_something_else
where "ctid" in (
    select "collection_items"."ctid"
    from "collection_items"
    inner join (
        select collection_id, sum(amount) as total_amount, avg(something_else) as avg_something_else 
        from "collection_items"
        group by collection_id
    ) as "calculated_results" where "id" in (1, 5, 7) and "id" = "calculated_results"."collection_id")

and the following sql error:

SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "calculated_results"

[staudenmeir]

It looks like we have to revert the commit, I don't see a way to fix the issue.

Which versions do we target? The revert is also kind of a breaking change, people can be relying on the features that the commit added (support for outer joins and LIMIT clauses).

[driesvints]

This will re-introduce https://github.com/laravel/framework/issues/18694 then?

[staudenmeir]

@driesvints Yes.

In theory, we could restore the previous implementation and only use it for the queries that don't work with the current one. The issue is that we can't reliably detect those queries.

[driesvints]

@staudenmeir is there any way we can re-introduce support for update ... from ... in another way?

[driesvints]

Also: how much is update ... from ... actually used? I'm not that familiar with PostgreSQL.

[staudenmeir]

is there any way we can re-introduce support for update ... from ... in another way?

Do you mean as a separate method?

[driesvints]

Yes

[staudenmeir]

I had an idea for a different approach (the opposite of https://github.com/laravel/framework/issues/33265#issuecomment-652656188): We could restore the previous implementation as the default behavior and only use the code from 3495ebd when necessary (queries with outer joins or LIMIT clauses).

I'll look into that.

[peanutsmcgee]

I need to make this kind of query quite a bit, it's actually preventing me from upgrading past laravel 5.8.
It seems like a real tricky issue to solve. The old way can't do outer joins, but can reference joined values... while the new way is the opposite, allowing outer joins, but can't reference joined values.

In my particular situation, I need to update the values in table_1 to be an aggregate of values from table_2, but some rows in table_2 are filtered out, and I'd like the update query to set the table_1 values for those rows to null. This seems to require an outer join, otherwise the rows in table_1 are removed by the inner join, and not updated at all.

How I'm currently handling this on 5.8 is using sub-queries. I perform the outer join between table_1 and table_2 in a sub-query, and then inner join table_1 with a duplicate of itself in the update statement. It looks something like this

$subQuery2 = DB::table('table2')->where('column', $filter);

$subQuery1 = DB::table('table1')->leftJoinSub($subQuery2, 'sub2', 'sub2.primary_key, '=', 'sub1.foreign_key`);

$update = DB:table('table1')->joinSub($subQuery1, 'sub1', 'sub1.primary_key, '=', 'table1.primary_key`)
          ->update(['table1.value' => DB::raw('table2.value')]);

I'm not sure if this helps anyone, but figured I'd mention it just in case.

[taylorotwell]

I really don't know the best way to solve this. @staudenmeir really knows the most about this situation since he wrote the code to change this behavior.