CODEWITHSUNDEEP
sqllaraveleloquent
David-Dj Bryant
David-Dj Bryant

Reputation: 29

Laravel/Eloquent, Return Value twice from same column

Newb alert :)

I'm trying to query my database and get a sum of all the total products purchased over two different date ranges, i.e.

Give me the total quantity purchased in the last 7 days and the last 30 days

+-----+----------+----------+
| SKU | Quantity | Date     |
+-----+----------+----------+
| ABC | 30       |07-23-2022|
| ABC | 40       |06-12-2022|
+-----+----------+----------+

Basically, I want to do something like this (which I know is completely wrong):

$sorted= Orders::groupBy('orders.SKU')
->selectRaw('sum(orders.quantity) as ordersMinusSevenDays)
->whereBetween('dateMinusSeven', [$from, $toTime])
->selectRaw('sum(orders.quantity) as ordersMinusThirtydays)
->whereBetween('dateMinusThirty', [$from, $toTime])
->get();

I have no idea what to even google for this :(

Upvotes: 0

Views: 187

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

We can start by writing a raw MySQL query for this. Then, port that over to Laravel code.

SELECT
    SKU,
    SUM(CASE WHEN Date >= NOW() - INTERVAL 7 DAY
             THEN Quantity ELSE 0 END) AS dateMinusSeven,
    SUM(CASE WHEN Date >= NOW() - INTERVAL 30 DAY
             THEN Quantity ELSE 0 END) AS dateMinusThirty
FROM orders
GROUP BY SKU;

The Laravel code for this might be:

$sorted = Orders::groupBy('orders.SKU')
    ->selectRaw('SUM(CASE WHEN Date >= NOW() - INTERVAL 7 DAY  THEN Quantity ELSE 0 END) AS dateMinusSeven, ' .
                'SUM(CASE WHEN Date >= NOW() - INTERVAL 30 DAY THEN Quantity ELSE 0 END) AS dateMinusThirty')
    ->get();

Upvotes: 1

Related Questions