CODEWITHSUNDEEP
laravellaravel-5eloquent
GrameJunior
GrameJunior

Reputation: 101

Laravel: Expression #3 of SELECT list is not in GROUP BY (Querying a Many-to-Many relationship)

I got a project with the Models Menu and User, which are in a Many-to-Many relationship.

On one site I show the past menus per calender week, on the other I want to show the order history of a specific user per calender week.

I got the menu history working with the following query:

public static function archives()
    {
        return  Menu::selectRaw('week(date, 1) as kw, year(date) as year')
                ->orderByRaw('min(date) asc')
                ->groupBy('kw', 'year')
                ->get();
    }

Now i thought about the same query for the User, but I get errors that are not even in my statement.

public static function archives(int $id)
    {
        return User::find($id)->menus()
                    ->selectRaw('week(date, 1) as kw, year(date) as year')
                    ->orderByRaw('min(date) asc')
                    ->groupBy('kw', 'year')
                    ->get();

    }

Expression #3 of SELECT list is not in GROUP BY

Upvotes: 0

Views: 180

Answers (1)

rkj
rkj

Reputation: 8287

Your mysql is set to ONLY_FULL_GROUP_BY option.

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

However in laravel you can overwrite it in config/database.php connections array mysql strict mode

'mysql' => [
            ...
            'strict' => false,
            'engine' => null,
        ],

Upvotes: 1

Related Questions