MartinZeltin
5 months ago

How to convert this query to Eloquent ORM?

Posted 5 months ago by MartinZeltin

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

Please sign in or create an account to participate in this conversation.