[FIXED] How to disable only_full_group_by option in Laravel

Issue

I am new to laravel and I am having an issue with DB problem.

I have disabled 'only_full_group_by' sql_mode by editing /etc/mysql/my.cnf file. And I checked sql_mode for both global and session using SELECT @@GLOBAL.sql_mode; and SELECT @@SESSION.sql_mode; and confirmed that sql_mode no longer has only_full_group_by.

However, when I make a request through postman, it gives me the error saying this is incompatible with sql_mode=only_full_group_by.

I am so confused. Why do I get this error even after I changed sql_mode? Am I doing something wrong?

Any suggestion or advice would be appreciated.

Thank you.

SQL using toSql()

select A.* 
from `A` 
inner join `B` on `A`.`id` = `B`.`a_id` 
inner join `C` on `C`.`id` = `B`.`c_id` 
group by `A`.`id` having COUNT(A.id) > 0;

Solution

This is the wrong way of going about this. Rather than turning off only_full_group_by mode, you should be fixing your query so that it doesn't break MySQL:

SELECT a1.*
FROM A a1
INNER JOIN
(
    SELECT A.id
    FROM A
    INNER JOIN B
        ON A.id = B.a_id 
    INNER JOIN C
        ON C.id = B.c_id
    GROUP BY A.id
    HAVING COUNT(A.id) > 0
) a2
    ON a1.id = a2.id;

I don't know why your attempts to turn off strict mode failed, but you should not be relying on it in any case.

Answered By - Tim Biegeleisen