Converting raw SQL to use Eloquent Query Builder
How can I transform the following complex SQL query to use the Eloquent Query Builder? I want to use methods like join()
and where()
, get()
etc.
The following query returns a list of locations along with a count for the vouchers that have been redeemed.
select
a.location_name,
'' as dates,
a.places,
sum(netvalue155),
sum(netvalue135) from
(
select
l.id,
l.location_name,
b.places,
case when v.net_value = 155 then 1 else 0 end as netvalue155,
case when v.net_value = 135 then 1 else 0 end as netvalue135
from locations l
left join bookings b on l.id = b.location_id
left join vouchers v on b.voucher_code = v.voucher_code
) a
right join locations l on l.id = a.id
group by a.location_name
EDIT
I am trying to use the below code which is throwing an error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sub.id' in on clause
$subQuery = DB::table('locations')
->select(
'locations.id',
'locations.location_name',
DB::raw('"'.$request->get('dates').'" as dates'),
DB::raw('sum(bookings.id) as number'),
DB::raw('round(sum(bookings.final_price/1.2), 2) as paidbycard'),
DB::raw('case when bookings.voucher_value = 155 then round(sum(bookings.voucher_value/1.2), 2) else 0.00 end as voucher155'),
DB::raw('case when bookings.voucher_value = 135 then round(sum(bookings.voucher_value/1.2), 2) else 0.00 end as voucher135'),
DB::raw('case when bookings.transfer_fee = 10 then round(sum(bookings.transfer_fee/1.2), 2) else 0.00 end as transfer_fee'))
->leftJoin('bookings', 'locations.id', '=', 'bookings.location_id');
$meatBookQuery = DB::table('orders')->select(DB::raw('sum(orders_items.price) as total'))
->join('orders_items', 'orders.id', '=', 'orders_items.order_id')
->where('orders_items.item_name', 'The Meat Book');
$booking = DB::table(DB::raw("({$subQuery->toSql()}) as sub, ({$meatBookQuery->toSql()}) as meatBook"))
->mergeBindings($subQuery)
->mergeBindings($meatBookQuery)
->select('sub.location_name', 'sub.dates', 'sub.number', 'sub.paidbycard', 'sub.voucher155', 'sub.voucher135', 'sub.transfer_fee', DB::raw('round(sum(sub.voucher155 + sub.voucher135 + sub.transfer_fee + sub.paidbycard), 2) as total'), 'meatBook.total')
->leftJoin('locations', 'locations.id', '=', 'sub.id')
->leftJoin('bookings', 'bookings.location_id', '=', 'sub.id')
->groupBy('sub.location_name');
source to share
First of all
I often see people asking how to rebuild a complex SQL query in Laravels Query Builder. But not every operation that is possible in SQL or MySQL is implemented as a function in Laravels Query Builder. This means that you cannot rebuild every SQL query in Query Builder without using raw SQL.
What does this mean for your SQL query?
In Query Builder, some functionality, such as auxiliary queries (part from (select ...)
) and part case when ...
, is not implemented. At least that's why you'll have to use a raw expression with a function DB::raw()
. I'm not sure sum()
if this is already possible, but you will definitely find it in the docs.
Other functions such as joins
are implemented as a function:
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'contacts.phone', 'orders.price')
->get();
see Laravel Documentation: Requests - Connections
And you can even mix Query Builder functions with raw expressions:
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
see Laravel Documentation: Requests - Initial Expression
Example for an additional request:
$subQuery = DB::table('locations')
->leftJoin('bookings', 'locations.id', '=', 'bookings.location_id')
->leftJoin('vouchers', 'bookings.voucher_code', '=', 'vouchers.voucher_code')
->select('locations.id', 'locations.location_name', 'bookings.places');
$query = DB::table(DB::raw("({$subQuery->toSql()} as sub"))
->mergeBindings($subQuery)
->select(...)
->rightJoin(...)
->groupBy('sub.location_name')
->get();
So, you can rebuild some parts of the query in the Query Builder and use the raw expressions wherever you need.
Debug the request while creating it. Laravels' logging feature can be very useful.
source to share