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?