Level 102
Sounds like you want to group by date?
->groupByRaw('DATE(created_at)')
Feb 25, 2022
30
Level 6
How to query records count based on days?
I have a table of Forms where people can sign up and their details are stored in the table. I am trying to display the statistics on how many number of people sign up on the particular day. For instance, my blade view should look like this:
Date Number of Sign Up
25 Feb 2022 1
24 Feb 2022 2
23 Feb 2022 0
22 Feb 2022 0
21 Feb 2022 5
20 Feb 2022 0
19 Feb 2022 0
18 Feb 2022 0
TOTAL 8
It displays the current day up until 7 days before so it will be dynamic.
Now I have to write some complicated query for the data in my controller:
$sign_ups = DB::table('forms')
->select('created_at', DB::raw('count(*) as sign_ups'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupBy('created_at')
->get();
Or a more Eloquent-ish query:
$sign_ups = Form::whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupBy('created_at')
->orderBy('created_at', 'DESC')
->get(array(
DB::raw('Date(created_at) as date'),
DB::raw('COUNT(*) as "sign_ups"')
));
The only issue here it does not distinct from the day but the time as well so every count returns as 1.
1st element
"date" => "2022-02-24"
"sign_ups" => 1
2nd element
"date" => "2022-02-24"
"sign_ups" => 1
3rd element
"date" => "2022-02-23"
"sign_ups" => 1
........
Another issue is that some days do not have any record so how would I populate it on the blade without record. How do I go about tackling this issue?
Level 6
@Sinnbeck How would the entire query look like?
Level 102
$sign_ups = DB::table('forms')
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as sign_ups'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupByRaw('DATE(created_at)')
->get();
1 like
Level 6
@Sinnbeck Any way to accomplish this without using DB query?
Level 102
@CookieMonster do you mean by using eloquent?
Level 6
@Sinnbeck Yea just pure eloquent.
Level 102
@CookieMonster almost the same
$sign_ups = Form::query()
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as sign_ups'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupByRaw('DATE(created_at)')
->get();
Level 6
@Sinnbeck In your first solution, how do sort it based on latest date?:
$sign_ups = DB::table('forms')
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as count'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupByRaw('DATE(created_at)')
->latest()
->get();
This returns Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column.
Level 102
@CookieMonster ah sorry I missed that. The same
$sign_ups = DB::table('forms')
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as count'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupByRaw('DATE(created_at)')
->orderByRaw('DATE(created_at) desc')
->get();
2 likes
Level 6
@Sinnbeck Somehow the date display from oldest to latest. Changing it to 'asc' has no effect as well.
Level 102
@CookieMonster fixed
Level 102
Let me know if you need help with the second part that you deleted. The trick is to use carbon period
Level 6
@Sinnbeck I thought of building an array with the dates but it would be too complicated.
This is how my view looks like:
<div class="content">
<div class="container-fluid">
<table class="table table-bordered">
<thead>
<tr>
<th scope="col">Date</th>
<th scope="col">Number of sign ups</th>
</tr>
</thead>
<tbody>
@foreach($sign_ups as $sign_up)
<tr>
<td>{{ $sign_up->date }}</td>
<td>{{ $sign_up->count }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
For now, it does only shows the collection from $sign_ups. How would you accomplish this?
Level 102
@CookieMonster I would prepare it first
$sign_ups = DB::table('forms')
->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as count'))
->whereBetween('created_at', [date('Y-m-d', strtotime('-7 days')), date('Y-m-d')])
->groupByRaw('DATE(created_at)')
->orderByRaw('DATE(created_at) desc')
->get()
->pluck('count', 'date'); //notice the pluck here to map it to key->value
$period = CarbonPeriod::create(today()->subDays(7), '1 day', today());
$final = [];
foreach ($period as $date) {
$date = $date->toDateString();
$final[] = [
'date' = $date,
'count' => $sign_ups[$date] ?? 0,
];
}
dd($final);
Level 6
@Sinnbeck Think you made typo. Should be:
foreach ($period as $date) {
$date = $date->toDateString();
$final[] = [
'date' => $date,
'count' => $sign_ups[$date] ?? 0,
];
Level 102
@CookieMonster Fixed :) Copy paste of an old answer
1 like
Level 6
@Sinnbeck It resets the order of the date. Do I just use array_reverse($final) to re-sort them to the latest to oldest?
Level 102
@CookieMonster Just flip the order here
$period = CarbonPeriod::create(today(), '1 day', today()->subDays(7));
Level 6
@Sinnbeck It wouldn't work since die dump returns empty. If I use array_reverse($final) then it can work.
Level 102
@CookieMonster Oh you are right. Strange. I was sure that worked. Buy yeah you can indeed just reverse it :)
Level 6
@Sinnbeck Perhaps this is another alternative:
array_reverse(CarbonPeriod::create(today()->subDays(7), '1 day', today()))->toArray();
It could be a bit less performant though.
Level 102
@CookieMonster I would personally do it afterwards on the final array :)
Level 6
@Sinnbeck Just one problem though. Since it is not passing the data to the view as a collection:
$datas = [];
foreach ($period as $date) {
$date = $date->toDateString();
$datas[] = [
'date' => $date,
'count' => $sign_ups[$date] ?? 0,
];
}
//sort the date based on latest to oldest
$datas = array_reverse($datas);
// dd(collect($datas));
View:
@foreach($datas as $data)
<tr>
<td>{{ $data->date }}</td>
<td>{{ $data->count }}</td>
</tr>
@endforeach
I cannot access the values without giving a Trying to get property 'date' of non-object error. Tried doing collect($datas) but it wouldn't work as well.
Level 102
@CookieMonster Yup. It is an array now, not an object
@foreach($datas as $data)
<tr>
<td>{{ $data['date'] }}</td>
<td>{{ $data['count'] }}</td>
</tr>
@endforeach
or cast to object if thats what you want
foreach ($period as $date) {
$date = $date->toDateString();
$datas[] = (object) [ //here
'date' => $date,
'count' => $sign_ups[$date] ?? 0,
];
}
Level 6
@Sinnbeck Should I convert it to a collection like collect($datas)?
Level 102
@CookieMonster You still cannot use object syntax then, as it is a collection of arrays. And no need to overcomplicate things ;)
1 like
Level 6
Level 102
@CookieMonster If you want those helpers you can do
$datas = collect($datas)->reverse()->values();
2 likes
Level 6
@Sinnbeck Thanks for the help :)
Level 102
@CookieMonster Happy to help
Please or to participate in this conversation.