laravel 5.5 group by doesn't work

Question

I'm trying to get some data but I just get this error

SQLSTATE[42000]: Syntax error or access violation: 1055 'ms_mascotas.ms_razas.id' isn't in GROUP BY (SQL: select ms_razas.id, ms_razas.nombre, ms_mascotas.raza_id, ms_mascotas.id from ms_mascotas inner join ms_razas on ms_razas.id = ms_mascotas.raza_id where ms_razas.tipo_animal_id = 1 group by ms_mascotas.raza_id)"

my query is this

$data=Mascota::select("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
       ->join("ms_razas","ms_razas.id","=","ms_mascotas.raza_id")
       ->where("ms_razas.tipo_animal_id",$id)
       ->groupBy("ms_mascotas.raza_id")
       ->get();

I've been reading about this error and it is related with the strict mode in the database file, strict is set to false by default, what should I do?

hi luis, now the error is gone, but the query isn't grouping the data — Felipe Castillo, Apr 12 '18 at 20:18
That one groups the collection. If you want to group the query results use the second one — Luis felipe De jesus Munoz, Apr 12 '18 at 20:22
`groupBy("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")` — Luis felipe De jesus Munoz, Apr 12 '18 at 20:23
There can be multiple rows for each `ms_mascotas.raza_id` value. Which one do you want to get? — Jonas Staudenmeir, Apr 12 '18 at 22:06

Jems · Accepted Answer · 2020-04-06T04:00:36.197

6

The problem isn't caused by laravel but this is because MySQL doesn't permit the illegal query, because in standard SQL-92, the nonaggregated name column in the select list does not appear in the GROUP BY. MySQL Handling of GROUP BY

Actually, you better solve the solution with changing the query, but if you still want to use the same query, just go to your config\database.php and change the setting the database that you use

'strict' => false

edited Apr 06 '20 at 04:00

answered Apr 13 '18 at 04:49

Jems

1,666
4
20
50

1

strict is set to false by default in laravel 5.5 – Felipe Castillo Apr 13 '18 at 07:42
try your query in Database tool like mysql or postgresql.. Maybe your query is the problem – Jems Apr 13 '18 at 07:51
I don't know what happened but I compressed and decompress the project in my personal computer and when I entered again to the database file ,stric was set to true. – Felipe Castillo Apr 13 '18 at 08:18

score 2 · Answer 2 · edited Apr 12 '18 at 20:57

Everything you select you must add in the group by, here is an example

 Mascota::select("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
       ->join("ms_razas","ms_razas.id","=","ms_mascotas.raza_id")
       ->where("ms_razas.tipo_animal_id",$id)
       ->groupBy("ms_razas.id","ms_razas.nombre","ms_mascotas.raza_id","ms_mascotas.id")
       ->get();

You need to have a full group by:

SELECT `name`, `type`, `language`, `code` 
FROM `users` 
WHERE `verified` = '1' 
GROUP BY `name`, `type`, `language`, `code` 
ORDER BY `count` DESC LIMIT 0, 25

SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause. SQL99 loosens this restriction a bit and states that all columns in the select clause must be functionally dependent of the group by clause. MySQL by default allows for partial group by and this may produce non-deterministic answers, example:

create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;
+------+------+
| x    | y    |
+------+------+
|    1 |    1 |
+------+------+

I.e. A random y is select for the group x. One can prevent this behavior by setting @@sql_mode:

set @@sql_mode='ONLY_FULL_GROUP_BY';
select x,y from t group by x; 
ERROR 1055 (42000): 'test.t.y' isn't in GROUP BY

even this query doesn't work, what happen with this version of laravel? `$data=Mascota::groupBy("raza_id") ->get();` — Felipe Castillo, Apr 12 '18 at 20:48

laravel 5.5 group by doesn't work

2 Answers2