Search code examples
phpmysqllaravellumen

SQL mode only_full_group_by causing error in Lumen 5.7

I have updated my lumen version from 5.6 to 5.7. In this illuminate/database package v5.7.15 enable MySQL mode only_full_group_by by default. This causes some of my Group By queries to fail. Most people suggests to disable strict mode in lumen. I think disable the strict mode is not a right solution. Is there any alternate query available in MySQL? Or I need to build a logic in PHP? I'm Using MySQL 5.6

I'm using quires like 

SELECT MONTHNAME(date_created) MONTH, YEAR(date_created) YEAR 
FROM test_table 
GROUP BY MONTH, YEAR

I'm not using aggregate functions. data_created column has datetime datatype

Solution

  • For that kind of query, where you don't use agregate functions that need GROUP BY, you can use distinct like this:

    SELECT DISTINCT MONTHNAME(date_created) MONTH, YEAR(date_created) YEAR 
FROM test_table

    So this way you don't need to disable the full_group_by restriction

    Also you could do it this way:

    SELECT MONTHNAME(date_created) MONTH, YEAR(date_created) YEAR 
FROM test_table 
GROUP BY MONTHNAME(date_created), YEAR(date_created)