How to convert this query to Eloquent ORM?

I have this query but I'm not sure how I would go about converting it into an eloquent ORM so I don't have to use raw sq.

select 
    date(o.created_at) as date_of_month,
    sum(order_sum) as total_order_sum,
    sum(i.total_energy_used) as total_energy_used,
    max(o.created_at) as lastorderdate,
    max(s.last_order_sum) as last_order_sum
from
    orders o
join (
    select
        order_id,
        sum(energy_used) total_energy_used
    from
        order_items i
    group by
        order_id
) i on o.id = i.order_id
join (
    select
        order_sum as last_order_sum,
        created_at
    from
        orders o
    where
        created_at = (
            select max(created_at) from orders o1 where date(o1.created_at) = date(o.created_at)
        )
    ) s on date(s.created_at) = date(o.created_at) 
group by
    date(o.created_at);

Here is a fiddle: http://sqlfiddle.com/#!9/bf799d/22

jlrdw

6 years ago

Level 75

Active record converts to Normal sql and uses pdo with bindings at runtime.

Meaning an eloquent query is converted right back to the above.

See: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

And use proper bindings. But basically an eloquent query is just "built up" with some "trial and error" like any query, something like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

https://laravel.com/docs/5.8/queries

But if you have a good working query above, I'd use as is with the db facade, or getPdo().

Please or to participate in this conversation.