laravel 5.5 group by on a single column doesn't work

Question

laravel 5.5 group by on a single column doesn't work even if i set the settings "strict" to false.. i have tried the raw query in the mysql workbench and it's working fine but i am having this error on laravel:

SQLSTATE[42000]: Syntax error or access violation: 1055 'hammer.vehicle.consignor_id' isn't in GROUP BY (SQL: select `vehicle`.*, `vehicle_brand`.`vehicle_brand_name`, `vehicle_model`.`vehicle_model_name`, `vehicle_variant`.`vehicle_variant_name`, `vehicle_model`.`year`, `vehicle`.`vehicle_id` from `vehicle` left join `vehicle_brand` on `vehicle_brand`.`vehicle_brand_id` = `vehicle`.`vehicle_brand_id` left join `vehicle_model` on `vehicle_model`.`vehicle_brand_id` = `vehicle_brand`.`vehicle_brand_id` left join `vehicle_variant` on `vehicle_variant`.`vehicle_model_id` = `vehicle_model`.`vehicle_model_id` group by `vehicle`.`vehicle_id` limit 10 offset 0)

here is the laravel code that's causing the error

\DB::table('vehicle')
                    ->select('vehicle.*', 'vehicle_brand.vehicle_brand_name', 'vehicle_model.vehicle_model_name', 'vehicle_variant.vehicle_variant_name', 'vehicle_model.year', 'vehicle.vehicle_id')
                    ->leftJoin('vehicle_brand', 'vehicle_brand.vehicle_brand_id', '=', 'vehicle.vehicle_brand_id')
                    ->leftJoin('vehicle_model', 'vehicle_model.vehicle_brand_id', '=', 'vehicle_brand.vehicle_brand_id')
                    ->leftJoin('vehicle_variant', 'vehicle_variant.vehicle_model_id', '=', 'vehicle_model.vehicle_model_id')
                    ->where($where)
                    ->groupBy('vehicle.vehicle_id')
                    ->paginate(10);

The mysql settings in the config "strict" is already set to false

@LakiGeri $where variable can be empty or has where statements. for now it is set to an empty array — Christian Burgos, Sep 20 '17 at 07:44
`vehicle.vehicle_id` i think there is a problem here no ? what is the id of the vehicle table ? — Maraboc, Sep 20 '17 at 07:45
that is the primary key of the vehicle table.. that's our naming convention — Christian Burgos, Sep 20 '17 at 07:46
Ok so in the select you have 'vehicle.*' and vehicle.vehicle_id may be it's not the problem source but just for cleaning :p — Maraboc, Sep 20 '17 at 07:48
tried removing vehicle.vehicle_id from the select. still it persist — Christian Burgos, Sep 20 '17 at 07:50
maybe the problem with the DB, have you tried to run the SQL select what is in the error msg? — LakiGeri, Sep 20 '17 at 07:51
@LakiGeri i've run the raw query in the workbench. it worked. i've already said that on the question — Christian Burgos, Sep 20 '17 at 07:52
you're right, im sorry. Maybe it misses the `->get();` from the end of the db query. — LakiGeri, Sep 20 '17 at 07:54
@LakiGeri i don't think ->get() is needed, i already have the ->paginate() — Christian Burgos, Sep 20 '17 at 07:55
`for now it is set to an empty array` - can you try removing the `->where($where)` all together, as a test? Excluding the where, your query is really just a plain select, so there doesn't seem much that could go wrong. Maybe having `->where([])` is causing problems? — Don't Panic, Sep 20 '17 at 07:56

Maraboc · Accepted Answer · 2017-09-20T08:12:57.380

8

I think it's the Strict mode that causes the problem, check the config/database.php and check inside the mysql, and change

'strict' => true,

to

'strict' => false,

Because as mentioned in this thread

If this is set to true then it'll add the ONLY_FULL_GROUP_BY when querying.

Or you can try to disable the only_full_group_by setting by executing the following:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

As suggested here.

edited Sep 20 '17 at 08:12

answered Sep 20 '17 at 07:56

Maraboc

10,550
3
37
48

i've already said in my question that i've set that to false. that's why it's weird because on laravel 5.4 doing that solves this problem. upgrading to laravel 5.5 it doesn't work. – Christian Burgos Sep 20 '17 at 07:57
Try to add `vehicle.consignor_id` to the groupBy just like mentioned [here](https://stackoverflow.com/a/34115425/4881811) – Maraboc Sep 20 '17 at 08:09

laravel 5.5 group by on a single column doesn't work

1 Answers1