lundi 28 janvier 2019

Eloquent: Alias from outer query can't be referenced in subquery

I have the following query:

DB::table('dining_tables as dt')
        ->whereNotExists(function($query){
            $query
                ->from('booking_allocations as ba')
                ->join('time_slots as ts', 'ba.slot_id', '=', 'ts.id')
                ->where('ba.booking_date', '=', '2019-01-27')
                ->where('ts.start_time', '>=', '12:00 PM')
                ->where('ts.end_time', '<=', '1:00 PM')
                ->where('ba.table_id', '=', 'dt.id')
                ->select(DB::raw('null'));
        })
        ->where('dt.capacity', '>=', 4)
        ->select('id')
        ->limit(1)
        ->get();

Notice that the alias dt created in the outer query is being referenced in the subquery in the following line:

->where('ba.table_id', '=', 'dt.id')

This leaves me with the following error:

Illuminate/Database/QueryException with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "dt.id" (SQL: select "id" from "dining_tables" as "dt" where not exists (select null from "booking_allocations" as "ba" inner join "time_slots" as "ts" on "ba"."slot_id" = "ts"."id" where "ba"."booking_date" = 2019-01-27 and "ts"."start_time" >= 12:00 PM and "ts"."end_time" <= 1:00 PM and "ba"."table_id" = dt.id) and "dt"."capacity" >= 4 limit 1)'

It's taken me half a day to realize the error can be circumvented by this replacement code:

->where('ba.table_id', '=', DB::raw('dt.id'))

NOTE: The wrapping of DB::raw() around the offending alias.

Now, the query works as expected. I would like to understand what might be happening here.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire