Syntax error: COLUMN isn't in GROUP BY (SQL, Laravel)

Question

I get an error in Laravel 5.4 trying to run the following query:

    return ReferLinkVisit::where('refer_link_id', $this->id)
        ->groupBy('ipaddr')
        ->get()

Error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'database.refer_link_visits.id' isn't in GROUP BY (SQL: select * from `refer_link_visits` where `refer_link_id` = 1 group by `ipaddr`) (View: /resources/views/dashboard/refer/home.blade.php)

Yet I can run the command in phpmyadmin and it will work just fine. I don't get it because I've wrote similar queries a hundred times but for whatever reason this time it just doesn't want to work. I can't figure out what I've done wrong.

Table structure:

That's an improper `GROUP BY` - all selected fields should either be named in the group, or grouping functions like `COUNT()`. What are you trying to achieve here? — Niet the Dark Absol, Jul 31 '17 at 16:08
You have to specify individual columns in a `GROUP BY`. Put all non-aggregated columns in `GROUP BY` statement. — Eric, Jul 31 '17 at 16:09

score 2 · Answer 1 · answered Aug 05 '19 at 07:26

You need to disable MySQL strict mode. Open config/database.php then find mysql array within the connections array. change the value of strict mode to false.

'connections' => [
     ...
     'mysql' => [
         ...
         'strict' => false, // ensure strict mode is set to false
         ...
      ],
     ...
]

I hope this solves your problem.

Syntax error: COLUMN isn't in GROUP BY (SQL, Laravel)

1 Answers1