How to mimic groupBy on a collection returned from an Eloquent query?

B.D Liroy 0
Viewed 15

I have a few queries that are almost identical but are different in the groupBy clauses.

These are some of the similar queries:

MyModel::select(DB::raw('SUM('col1') as col1, SUM('col2') as col2), SUM('col3') as col3'))
    ->groupBy('col1')
    ->get()

and

MyModel::select(DB::raw('SUM('col1') as col1, SUM('col2') as col2), SUM('col3') as col3'))->groupBy('col1')
    ->groupBy('col2')
    ->get();

and

MyModel::select(DB::raw('SUM('col1') as col1, SUM('col2') as col2), SUM('col3') as col3'))
    ->groupBy('col1')->groupBy('col2')->groupBy('col3')
    ->get();

Now I know that collections have groupBy and sortBy, but it doesn't work as expected here, since the collection returned from my queries is nested:

Illuminate\Database\Eloquent\Collection
    #items: array: 1 [
        0 => App\Models\MyModel
           #attributes: array // <-- the actual data is here

so if I try to use it like in the docs, it won't work:

https://laravel.com/docs/9.x/collections#method-groupby

What I'm trying to achieve is this:

I want to have a single "base" query, and on top of this query, do the different groupBy's on the collection so that I only do 1 database query instead of multiple:

MyModel::select(DB::raw('SUM('col1') as col1, SUM('col2') as col2), SUM('col3') as col3'))->get()

And on top of it do the groupBy's

Is it possible?

laravel
1 Answers

You may pass a string to ->groupBy($value) or a closure

the string needs to be an attribute on the models in the collection which under the hood laravel converts this to a closure fn ($item) => data_get($item, $value)

using a closure:

/**
 * @param Model $item
 */
$collection->groupBy(function($item) {
    return $item->{$value}
});

you make also pass an array to group by multiple

$collection->groupBy(['col_1', 'col_2'])

what this will do is create a collection of collections. first collection being grouped by col_1 and in each col_1 collection would be a collection grouped by col_2 which would be a collection of records

// with the following structure
collect([
    ['col_1' => '1a', 'col_2' => '1b', 'col_3' => '1'],
    ['col_1' => '2a', 'col_2' => '1b', 'col_3' => '2'],
    ['col_1' => '1a', 'col_2' => '2b', 'col_3' => '3'],
    ['col_1' => '2a', 'col_2' => '2b', 'col_3' => '4'],
    ['col_1' => '3a', 'col_2' => '3b', 'col_3' => '5'],
])->groupBy(['col_1', 'col_2']);

// you will get:
Illuminate\Support\Collection {
  all: [
    "1a" => Illuminate\Support\Collection {#col_1
      all: [
        "1b" => Illuminate\Support\Collection {#col_2
          all: [
            [
              "col_1" => "1a",
              "col_2" => "1b",
              "col_3" => "1",
            ],
          ],
        },
        "2b" => Illuminate\Support\Collection {#col_2
          all: [
            [
              "col_1" => "1a",
              "col_2" => "2b",
              "col_3" => "3",
            ],
          ],
        },
      ],
    },
    "2a" => Illuminate\Support\Collection {#col_1
      all: [
        "1b" => Illuminate\Support\Collection {#col_2
          all: [
            [
              "col_1" => "2a",
              "col_2" => "1b",
              "col_3" => "2",
            ],
          ],
        },
        "2b" => Illuminate\Support\Collection {#col_2
          all: [
            [
              "col_1" => "2a",
              "col_2" => "2b",
              "col_3" => "4",
            ],
          ],
        },
      ],
    },
    "3a" => Illuminate\Support\Collection {#col_1
      all: [
        "3b" => Illuminate\Support\Collection {#col_2
          all: [
            [
              "col_1" => "3a",
              "col_2" => "3b",
              "col_3" => "5",
            ],
          ],
        },
      ],
    },
  ],
}

even though col_2 has the same value same we first grouped by col_1 then inside of each collection in col_1 we grouped by col_2

I am not 100% what your question is but i hope this helps you understand more of how it works

RawSlugs0
Related