How to calculate current and past data to get the difference?

I have sample table structure looks like this

CREATE TABLE overviews  ( 
    id          int(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    total_users  int NOT NULL,
    ....
    created_at  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    updated_at  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    deleted_at  timestamp NULL,
    PRIMARY KEY(id)
)

I want to calculate the increment of total_users by current week and past week. For example, my expected output will be

past_total_users | current_total_users | difference
1000 | 2500 | 1500

At the moment, I am using \Carbon\Carbon::now()->subDays(7); to get last week record. and \Carbon\Carbon::now()->endOfWeek() to get current week record. Which mean i have 2 eloquent query to get the data. I am getting the last record of the week and then do calculation between this two record to get the difference.

Is there any better way to achieve this? Or i should make a sub-query to get the past record in select statement?

Nakov

4 years ago

Level 73

Try this:

Overview::query()
  ->selectRaw(\DB::raw('sum(case when week(created_at) = week(now()) then total_users end) as this_week, sum(case when week(created_at) = week(now()) - 1 then total_users end) as last_week, sum(case when week(created_at) = week(now()) then total_users end) - sum(case when week(created_at) = week(now()) - 1 then total_users end) as difference'))
  ->whereRaw('created_at >= date_sub(now(), interval 2 week)')
->first();

raw query, but it does the job in one query instead of doing multiple queries. You might need to reverse the cases for the difference, but at least try it out if it works :)

Another option is just to fetch the data for the last two weeks and calculate it in memory:

$lastWeek = Overview::whereBetween('created_at', [now()->subWeeks(2), now()->subWeek()])->sum('total_users');
$thisWeek = Overview::whereBetween('created_at', [now()->subWeek(), now()])->sum('total_users');

$result = $lastWeek - $thisWeek;

Jeffxy

4 years ago

Level 1

@Nakov Thanks for the feedback. But if i have multiple field that need to do the calculation, will it be better stick to the eloquent method instead of raw query?

Nakov

4 years ago

Level 73

@Jeffxy depends, you will measure performance how much memory the queries consume using collection only and how long it takes, but also how often you perform the check.

Please or to participate in this conversation.