CODEWITHSUNDEEP
phpmysqllaravellaravel-5.5
Christian Burgos
Christian Burgos

Reputation: 1591

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

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

Upvotes: 3

Views: 3866

Answers (1)

Maraboc
Maraboc

Reputation: 11083

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.

Upvotes: 8

Related Questions