Group by multiple columns in Laravel

Question

How to GROUP BY multiple column in Laravel? I tried this code:

$routes = DB::table('route')
    ->groupBy('rte_origin')
    ->groupBy('rte_destination')
    ->get();

But this is not working.

Tunicate

it is working for me, did you find your problem? – Corium

Answer 1

Did you try:

$routes = DB::table('route')
->groupBy('rte_origin', 'rte_destination')
->get();

Can't test here right now, but the API says groupBy() accepts an array.

For reference, please visit:

• Laravel 5.0: https://github.com/laravel/framework/blob/5.0/src/Illuminate/Database/Query/Builder.php#L1037
• Laravel 4.2: https://github.com/laravel/framework/blob/4.2/src/Illuminate/Database/Query/Builder.php#L1017

Pilloff

That's also not working. Showing error as following. ErrorException strtolower() expects parameter 1 to be string, array given – Tunicate

Ouch. I'm sorry satjan. Let me get back home and I'll give this a test for you. – Pilloff

FYI, Originally groupBy() did not accept an array, as explained in https://mcmap.net/q/404737/-group-by-multiple-columns-in-laravel. That functionality was added in L4.2 (or possibly earlier) See github.com/laravel/framework/blob/4.2/src/Illuminate/Database/… for reference. – Maintop

@GustavoRamos Your link to the laravel docs is dead. Please see github.com/laravel/framework/blob/5.0/src/Illuminate/Database/… for L5.0, or github.com/laravel/framework/blob/4.2/src/Illuminate/Database/… for L4.2 – Maintop

Thanks for pointing that out, @SeantheBean. I've edited the main answer. Cheers! – Pilloff

Wish this little tidbit was in the official docs. Given the way Laravel methods go, it could have either been n arguments, an array, or just calling groupBy multiple times. – Youngyoungblood

its worked for me. groupby('booking_branch','booking_date') -----Laravel Framework 8.17.2------ – Bedstead

Answer 2

It is incorrect to believe that the Database\Query\Builder::groupBy() method accepts arrays as arguments. Currently, it will only accept N number of string arguments.

As of this writing, the current version of the Laravel Framework is: v4.2.4, and the code for the Database\Query\Builder::groupBy() method is as follows:

/**
 * Add a "group by" clause to the query.
 *
 * @param  dynamic  $columns
 * @return \Illuminate\Database\Query\Builder|static
 */
public function groupBy()
{
    $this->groups = array_merge((array) $this->groups, func_get_args());

    return $this;
}

If you think about it, func_get_args() returns an array of all columns that might have been fed in as strings. Therefore, expected input of this function is:

$builder->groupBy('column1', 'column2', ...);

The resulting $this->groups property on the builder object should be an array of strings, like this:

['column1','column2']

However, if we fed an array into the method above, like this:

$builder->groupBy(['column1','column2']);

the $this->groups property would end up with a nested array that looks like this:

[['column1','column2']]

The rest of the query builder framework expects the $builder->groups property to be a non-nested array of strings. Thus, when the framework tries to columnize and wrap table names in their proper escaped format (each database engine has a different table name escape operator), it tries to wrap an array instead of a string and you get your error.

The offending error line is line 49 in Database\Grammar::wrap().

If we were to modify the Database\Query\Builder::groupBy() method to make it accept arrays, we would rewrite it something like the following:

public function groupBy()
{
    $args = func_get_args();
    foreach($args AS $arg)
    {
        $arg = (is_array($arg)) ? $arg:[$arg];
        $this->groups = array_merge((array) $this->groups, $arg);
    }

    return $this;
}

This method accepts an array at any parameter index.

Unsure

A pull-request has been submitted to the Laravel team to resolve this issue. See: github.com/laravel/framework/pull/4843 – Unsure

The ability to pass an array to groupBy() was added in L4.2. See github.com/laravel/framework/blob/4.2/src/Illuminate/Database/… for reference. – Maintop

Laravel Framework v9.38 vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php ``` /** * Add a "group by" clause to the query. * * @param array|string ...$groups * @return $this */ public function groupBy(...$groups) { foreach ($groups as $group) { $this->groups = array_merge( (array) $this->groups, Arr::wrap($group) ); } return $this; } ``` – Inconsistency

Answer 3

Put the select before the groupBy and the arguments in brackets.

$routes = DB::table('route')
    ->select(['column_1', 'column_2'])
    ->groupBy(['column_1', 'column_2'])
    ->get();

Pontefract

Answer 4

Edit your applications's database config file config/database.php

In mysql array, set strict => false to disable MySQL's strict mode to make this work.

Baresark

Answer 5

you can run this for only 1 query

    config()->set('database.connections.mysql.strict', false);
    DB::reconnect();

    Model::query();

    config()->set('database.connections.mysql.strict', true);
    DB::reconnect();

Idaline

Answer 6

In Laravel - go to database.php and change to:

'strict' => false,

Figured

...so you found https://mcmap.net/q/404737/-group-by-multiple-columns-in-laravel and decided to write the same advice again? – Internode