How to get data of last 10 weeks in Laravel

Question

I want to get data ordered by last 10 weeks

what I mean is that I have a table that contain product that has been sold I want to arrange data so that the output should be

week 1 : [product_1,product_2 , ....] week 2 : [product_9,product_10 , ....]

and so on

I tried the below code to get it day by day

DB::raw("SELECT week(order_invoice.delivery_date) week_id,
                    SUM( IF(weekday(order_invoice.delivery_date) = 1, 1, 0) as day_1 ),
                    SUM( IF(weekday(order_invoice.delivery_date) = 2, 1, 0) as day_2),
                    SUM( IF(weekday(order_invoice.delivery_date) = 3, 1, 0) as day_3),
                    SUM( IF(weekday(order_invoice.delivery_date) = 4, 1, 0) as day_4),

But I believe that's wrong for 70 day

how i can solve this with laravel or even sql query

Possible solution... https://stackoverflow.com/questions/13278552/group-by-week-with-sql — SS_DBA, Jun 25 '19 at 12:06
Can you give an example of what the output would look like? Do you want all sales with a week id? — Jerodev, Jun 25 '19 at 12:06

score 0 · Answer 1 · answered Jun 25 '19 at 12:11

I'm assuming your model is OrderInvoice. For this to work correctly, make sure in the OrderInvoice model, you have delivery_date as part of the protected $dates array so that the property automatically gets parsed by Carbon.

$invoices = OrderInvoice::whereDate('delivery_date', >=, today()->startOfWeek()->subWeeks(10))->get(); // You can drop startOfWeek if you don't need it.

$weekData = [];
foreach ($invoices as $invoice) {
     $w = $invoice->delivery_date->week; // use the week number as our array key
     $weekData[$w][] = $invoice;
}

var_dump($weekData);

https://laravel.com/docs/5.8/queries#where-clauses For more info on whereDate. https://carbon.nesbot.com/docs/ For more info on Carbon.

in your `OrderInvoice` model, make sure you have a `protected $dates` array and ensure that 'delivery_date' is in it. https://laravel.com/docs/5.8/eloquent-mutators#date-mutators — FunkyMonk91, Jun 25 '19 at 12:52

How to get data of last 10 weeks in Laravel

1 Answers1