Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mp3man's avatar

Laravel query but works with phpmyadmin

Hi, I've got the next query that works fine with phpmyadmin:

select COUNT(networks.hardware_id) AS numAddresses, hardware.id,name,workgroup,userid,ipaddr,ip_ranges.centre,osname,oscomments,processort,processorn,processors,memory,lastcome,deviceid,useragent,bios.smanufacturer as biosmanufacturer,smodel as biosmodel,ssn as biossn,bios.type as biostype from `hardware` inner join `ip_ranges` on `hardware`.`ip_range_id` = `ip_ranges`.`id` inner join `bios` on `bios`.`hardware_id` = `hardware`.`id` inner join `networks` on `networks`.`hardware_id` = `hardware`.`id` group by `hardware`.`id` 

This query is generated using laravel query builder as follows:

 $data = Hardware::selectRaw('COUNT(networks.hardware_id) AS numAddresses, hardware.id,name,workgroup,userid,ipaddr,ip_ranges.centre,osname,oscomments,processort,processorn,processors,memory,lastcome,deviceid,useragent,bios.smanufacturer as biosmanufacturer,smodel as biosmodel,ssn as biossn,bios.type as biostype')
                ->join('ip_ranges', 'hardware.ip_range_id', 'ip_ranges.id')
                ->join('bios', 'bios.hardware_id', 'hardware.id')
                ->join('networks', function($join) {
                    $join->on('networks.hardware_id', 'hardware.id')
                        ->on('networks.ipaddress', 'hardware.ipaddr');
                })
                //->where('deviceid', '<>', '_SYSTEMGROUP_')
                ->when($limitDates, function ($query, $limitDates) {
                    return $query->whereDate('lastcome', '<=', Carbon::now()->addDays(OCS_DAYS_CONTACT[0]))
                        ->whereDate('lastcome', '>=', Carbon::now()->addDays(-OCS_DAYS_CONTACT[1]));
                })
                ->groupBy('hardware.id');

The problem is that, laravel returns next error:

message: SQLSTATE[42000]: Syntax error or access violation: 1055 'ocsinv.hardware.NAME' isn't in GROUP BY (SQL: select COUNT(networks.hardware_id) AS numAddresses, hardware.id,name,workgroup,userid,ipaddr,ip_ranges.centre,osname,oscomments,processort,processorn,processors,memory,lastcome,deviceid,useragent,bios.smanufacturer as biosmanufacturer,smodel as biosmodel,ssn as biossn,bios.type as biostype from `hardware` inner join `ip_ranges` on `hardware`.`ip_range_id` = `ip_ranges`.`id` inner join `bios` on `bios`.`hardware_id` = `hardware`.`id` inner join `networks` on `networks`.`hardware_id` = `hardware`.`id` and `networks`.`ipaddress` = `hardware`.`ipaddr` group by `hardware`.`id`)

So this means that grup by clouse must include all the select fields, and I don't want to do that, there's any way to make it work?

0 likes
3 replies
tykus's avatar
tykus
Best Answer
Level 104

Modify your database config to turn off strict mode for your application's connection:

// database.php

return [

	// ...

	'connections' => [

		// ...

		'mysql' => [
            'driver' => 'mysql',
            // ...
            'strict' => false,
			// ...

		]


]
mp3man's avatar

It didn't worked for me, I'm sorry, it continues giving me the same error, I'm using Laravel 8, probably for that?

Please or to participate in this conversation.