CODEWITHSUNDEEP
laravellaravel-5
V4n1ll4
V4n1ll4

Reputation: 6099

Convert raw SQL to use Eloquent Query Builder

How can I convert the following complex SQL query to use the Eloquent query builder? I want to use methods such as join() and where(), get() etc.

The below query returns a list of locations along with counts for 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 the below code, which throws the 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');

Upvotes: 3

Views: 4532

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

First of all

I often see people asking for how to rebuild a complex SQL query in Laravels Query Builder. But not every operation which is possible in SQL or MySQL is implemented as a function in Laravels Query Builder. This means you can't rebuild every SQL query in Query Builder without using raw SQL.

What does this mean for your SQL query?

Some things like sub queries (the from (select ...) part) and the case when ... part is not implemented in Query Builder. At least therefore you will have to use the raw expression with the DB::raw() function. I'm not sure about the sum() if this is already possible but you will surely find that in the docs.

Other things like 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: Queries - Joins

And you can even mix up 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: Queries - Raw Expression

Example for a sub query:

$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 Query Builder and use raw expressions wherever you need to.

To debug a query while you build it Laravels query logging function can be very helpful.

Upvotes: 2

Related Questions