I have this data

My query like this

SELECT name, year, count(*) as count FROM table GROUPBY name, year

Output will be

How to make output like this in php or laravel

{
    name => test1,{
        {
            year => 2020,
            count =>  1
        },
        {
            year => 2021,
            count => 1
        }
    },
    name => test2,{
        {
            year => 2020,
            count =>  2
        },
        {
            year => 2021,
            count => 1
        }
    },
    name => test3,{
        {
            year => 2020,
            count =>  1
        }
    },
}

CodePudding user response：

This Might not be perfect, but something like this should suffice:

Data::select("year",DB::raw("count(id) as count"))
                        ->groupBy('Name')
                        ->get();

CodePudding user response：

After getting the grouped results from Database you can apply groupBy in Collection and apply the format

$query = DB::table('yourtable')
    ->select([
      'name',
      'year',
      DB::raw('count(*) as count'),
    ])
    ->groupBy(['name','year'])
    ->get()
    ->groupBy('name')
    ->map(function($yearlyCollection){
      return $yearlyCollection
            ->map(function($eachData){
              return Arr::except((array)$eachData,'name');
            });
    })
  ->map(function($value,$key){
    return [
      'name' => $key,
      'yearData' => $value->toArray(),
    ];
  })
  ->values()
  ->toArray();

will Produde the output like this

[
     [
       "name" => "Test1",
       "yearData" => [
         [
           "year" => 2020,
           "count" => 1,
         ],
         [
           "year" => 2021,
           "count" => 1,
         ],
       ],
     ],
     [
       "name" => "Test2",
       "yearData" => [
         [
           "year" => 2020,
           "count" => 2,
         ],
         [
           "year" => 2021,
           "count" => 1,
         ],
       ],
     ],
     [
       "name" => "Test3",
       "yearData" => [
         [
           "year" => 2020,
           "count" => 1,
         ],
       ],
     ],
   ]