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