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.

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

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.

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

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.