How to query records count based on days?

@Sinnbeck How would the entire query look like?

@cookiemonster

$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(); 

@Sinnbeck Any way to accomplish this without using DB query?

@CookieMonster do you mean by using eloquent?

@Sinnbeck Yea just pure eloquent.

@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();  

@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.

@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();  

@Sinnbeck Somehow the date display from oldest to latest. Changing it to 'asc' has no effect as well.

@CookieMonster fixed

Let me know if you need help with the second part that you deleted. The trick is to use carbon period

@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?

@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);
 

@Sinnbeck Think you made typo. Should be:

foreach ($period as $date) {
            $date = $date->toDateString();
            $final[] = [
                'date' => $date,
                'count' => $sign_ups[$date] ?? 0,
        ];

@CookieMonster Fixed :) Copy paste of an old answer

@Sinnbeck It resets the order of the date. Do I just use array_reverse($final) to re-sort them to the latest to oldest?

@CookieMonster Just flip the order here

$period = CarbonPeriod::create(today(), '1 day', today()->subDays(7));

@Sinnbeck It wouldn't work since die dump returns empty. If I use array_reverse($final) then it can work.

@CookieMonster Oh you are right. Strange. I was sure that worked. Buy yeah you can indeed just reverse it :)

@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.

@CookieMonster I would personally do it afterwards on the final array :)

@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.

@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,
            ];
        }

@Sinnbeck Should I convert it to a collection like collect($datas)?

@CookieMonster You still cannot use object syntax then, as it is a collection of arrays. And no need to overcomplicate things ;)

@Sinnbeck I see. Realized I could not use @if(!$datas->isEmpty()) as well but only could use count($datas) when cast to an object.

@CookieMonster If you want those helpers you can do

$datas = collect($datas)->reverse()->values();

@Sinnbeck Thanks for the help :)

@CookieMonster Happy to help