Laravel count multilevel groupBy

Question

table Items :

[created_at => 2020-08-12 06:00:00, type =>2]
[created_at => 2020-08-12 07:00:00, type =>1]
[created_at => 2020-08-12 08:00:00, type =>1]
[created_at => 2020-08-11 12:00:00, type =>3]
[created_at => 2020-08-11 21:00:00, type =>3]

I would like to write (Laravel 7) a query which returns something like:

[
["date"=>"2020-08-12","amount"=>3, "type1"=>2, "type2"=>1],
["date"=>"2020-08-11", "amount"=>2, "type3"=>2],
]

How to groupBy and count on multilevel ?

Kurt Friars · Answer 1 · 2020-08-13T16:06:02.350

You can achieve this with a litte bit of query builder, and a little bit of collections:

return DB::table('items')
         ->selectRaw('DATE_FORMAT(created_at, "%Y-%m-%d") as date, type, COUNT(type) as count')
         ->groupBy('date', 'type')
         ->orderBy('date', 'desc')
         ->get()
         ->reduce(function ($summary, $item) {
             if (! $summary->has($item->date)) {
                 $summary->put($item->date, collect([
                     'date' => $item->date,
                     'amount' => $item->count
                 ]));
             } else {
                 $amount = $summary->get($item->date)['amount'];
                 $summary->get($item->date)->put('amount', $amount + $item->count);
             }
             
             $summary->get($item->date)->put('type'.$item->type, $item->count);
             
             return $summary;
         }, collect())
         ->values()
         ->toArray();

You can check out this implode that has the working code

Thank you so much @KurtFriars. Unfortunately, 'amount' gives the number of different 'type'. To get the sum of the different 'type' counters, I slightly modified your code: `$summary->put($item->date, collect([ 'date' => $item->date, 'amount' => $item->count ])` and `$summary->get($item->date)->put('amount', $amount + $item->count)`; — thetispro, Aug 13 '20 at 15:55
@thetispro I have updated my answer. If it is working for you please accept it :) — Kurt Friars, Aug 13 '20 at 16:06

score 1 · Accepted Answer · answered Aug 12 '20 at 23:35

The data shape is not exactly the same you wanted, but I added some meaningful keys that I think are helpful.

return DB::table('types')->get(['created_at', 'type'])
    ->groupBy(fn ($item) => Carbon::parse($item->created_at)->format('Y-m-d'))
    ->map(function ($item, $key) {
        $details = $item->map(fn ($type) => $type->type)
            ->unique()->map(fn ($i) => ['type'.$i => $item->sum(fn ($type) => $i === $type->type ? 1 : 0)]);

        return ['date' => $key, 'amount' => $item->count(), 'details' => $details];
    });

Laravel count multilevel groupBy

2 Answers2