How to sum column during week of the last record?

Question

I assume recording date of a user's the last activity as 13.04.2019 . I want to sum all records in the week belonging the last record.

ex:

created_at = 13.04.2019, activity = 3

created_at = 11.04.2019, activity = 3

created_at = 01.04.2019, activity = 3

//the above 3 records is not same week. I need last week to sum activites so the result of sum will be 6

How could I do this with Carbon or others

$sum_last_week = Model::where('user_id',$user->id)->orderby('id','desc')->sum('activity')
....

why not just to group by week https://stackoverflow.com/a/11759589/3256489 ? — 4givN, Jun 16 '20 at 22:22

score 1 · Answer 1 · edited Jun 16 '20 at 21:11

1

The whereBetween method verifies that a column's value is between two values.

$from = date('Y-m-d',strtotime('-7 days'));  
$to = date('Y-m-d');
$last_week = Model::whereBetween('created_at', [$from, $to])->get();

edited Jun 16 '20 at 21:11

Sammitch

answered Jun 16 '20 at 15:51

STA

score 1 · Accepted Answer · answered Jun 17 '20 at 07:54

1

First retrieve the last active date,

$model = Model::where('user_id', $user->id)->orderby('created_at',desc)->first();

Then, generate target range depending on above result

$testDate=$model->created_at;
$from = $testDate->startOfWeek()->format('Y-m-d H:i');
$to = $testDate->endOfWeek()->format('Y-m-d H:i');

Last, retrieve sum

$result =Model::whereBetween('created_at', [$from, $to])->sum('activity');

answered Jun 17 '20 at 07:54

zeref

created_at is a date column, so you don't need to ->format(), you can directly pass the Carbon instances. – KyleK Jun 18 '20 at 10:02

2 Answers2