Laravel: write query to count sales with group by weeks-CodePudding

I have mysql database having a sales table, representing a list of products sold daily, with columns of product name, price and created_at. I could get the number of sales for a certain day by doing this for instance;

$sales = Sale::where('created_at', $certain_day')->count();

However, i need to track the sales per week for a number of weeks, say the number of products sold from the beginning of this week (sunday) till date, the number of sales last week, the number of sales two weeks ago and so on, for the last five weeks, returned as an array (something like this:[{30/12/2021: 10}, {25/12/2021: 15}, {18/12/2021: 22}]. How can i write laravel eloquent query to achieve this?

CodePudding user response：

Try something like this:

$from = date('2018-01-01');
$to = date('2018-05-02');

Sale::whereBetween('created_at', [$from, $to])->get();

Then you can use Carbon method for current week, previous week, next week

Sale::whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])->get();

@update

Sale::selectRaw('*,UNIX_TIMESTAMP(created_at) DIV '.$sec. ' as group_date')->groupBy('group_date');

For week,month and day you can do

->groupBy(function($data) {
// day
return Carbon::parse($data->created_at)->format('Y-m-d');
//month
return Carbon::parse($data->created_at)->format('Y-m');
//week
return Carbon::parse($data->created_at)->format('W');
})

CodePudding user response：

This following query gives you the last 5 weeks with the count of sales in each week:

SELECT FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS weeklyGrouping , COUNT(*) AS sales_count
FROM sales
GROUP BY weeklyGrouping
ORDER BY weeklyGrouping DESC
limit 5

The formula above assumes that Sunday is the first day of the week and in case your weeks start on Mondays, change the -1 to -2.

for descriptive information you can refer to this article.

EDIT (Eloquent Style)

$sales = Sale::select(DB::RAW('FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS weeklyGrouping'), DB::RAW('COUNT(*) AS sales_count'))
    ->groupBy(DB::raw('weeklyGrouping')),
    ->orderBy(DB::raw('weeklyGrouping'), 'DESC')
    ->take(5);