Level 51
Yeah, mostly you need to run 2 queries.
Or if you want it as a report for multiple weeks, you can group by week.
Mar 28, 2022
7
Level 4
How to query and compare past week and current week record?
I want to query my past week record compare to my current week record. My database store the data by day.
To get current week record i could simply query it with startOfWeek and endOfWeek to calculate the total_count.
For example, i want to get the record from past week vs current week. I have a column person_count which i will sum it for this week compare to sum of person_count in past week.
How could i achieve this? Or i need to write 2 query to achieve this?
Level 4
@MohamedTammam Thanks for the feedback. If i want to get only the current week record, and a column to show difference between past and current data, do i go with 2 query as well?
Level 55
@crazylife for sure no. You need to do it in 1 query. Bad suggestion
Level 4
@SilenceBringer Do you have any sample on how can i actually do it in one query?
Level 102
What do you mean by compare? One minus the other? And just 1 total?
Can you show what you have now?
Level 4
The below shown is what i have right now.
$current =Overview::where('id', $id)
->where(function ($query) {
$query->where('created_at', '>=', Carbon::now()->startOfWeek()->format('Y-m-d') . ' 00:00:00')
->where('created_at', '<=', Carbon::now()->endOfWeek()->format('Y-m-d') . ' 23:59:59');
})
->select(\DB::raw('SUM(review_count) as review_count, AVG(total_reach) as total_reach'))
->first();
$past = Overview::where('id', $id)
->where(function ($query) {
$query->where('created_at', '>=', Carbon::now()->subWeek()->startOfWeek()->format('Y-m-d') . ' 00:00:00')
->where('created_at', '<=', Carbon::now()->subWeek()->endOfWeek()->format('Y-m-d') . ' 23:59:59');
})
->select(\DB::raw('SUM(review_count) as review_count, AVG(total_reach) as total_reach'))
->first();
I want to get the record for current week and find the difference between current week and past week. For example, i want to get review_count, diff_review_count, total_reach, diff_total_reach. etc
Level 4
Is there anyway to merge this two query into one?
$current = Overview::where('id', $id)
->where(function ($query) {
$query->where('created_at', '>=', Carbon::now()->startOfWeek()->format('Y-m-d') . ' 00:00:00')
->where('created_at', '<=', Carbon::now()->endOfWeek()->format('Y-m-d') . ' 23:59:59');
})
->select(\DB::raw('SUM(review_count) as review_count, AVG(total_reach) as total_reach'))
->first();
$past = Overview::where('id', $id)
->where(function ($query) {
$query->where('created_at', '>=', Carbon::now()->subWeek()->startOfWeek()->format('Y-m-d') . ' 00:00:00')
->where('created_at', '<=', Carbon::now()->subWeek()->endOfWeek()->format('Y-m-d') . ' 23:59:59');
})
->select(\DB::raw('SUM(review_count) as review_count, AVG(total_reach) as total_reach'))
->first();
My expected result will be getting current week record and show the difference between current and past record ( current - past).
Please or to participate in this conversation.