Symfony query with ONLY_FULL_GROUP_BY ON

Question

I want to retrive a collection of object as result of cities grouped by name, my query is

public function getDistinctCitiesName()
{
    $qb = $this->createQueryBuilder("cc");
            ->add('groupBy', 'cc.name');

    return $qb;
}

When run i have an exception

request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT c0_.id AS id_0, c0_.name AS name_1, c0_.postal_code AS postal_code_2, c0_.country AS country_3, c0_.state AS state_4, c0_.visible AS visible_5 FROM cities c0_ GROUP BY c0_.name': SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'www.c0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

I can't disable the only_full_group_by mode, so is there a solution ?

Your query doesn't contain any aggregated columns, it is not allowed into [only_full_group_by](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) mode. Consider rewriting your query to include aggregates — Flying, Jan 03 '19 at 14:24
thanks, but rewriting query does not return a collection on object — Hansen mattias, Jan 03 '19 at 14:47
Query with an aggregation doesn't mean to return complete rows of the table at all, it is about SQL, not Doctrine. Try to think about your goal, what do you actually want to achieve by this `groupBy` clause? — Flying, Jan 03 '19 at 14:53
Your method name is `getDistinctCitiesName` so I can assume that your actual query should look like `select distinct(name) from cities`, but your actual query is different. — Flying, Jan 03 '19 at 15:07
Thanks, but as said i want retrive a collection of object, select distinct return only array of values — Hansen mattias, Jan 03 '19 at 16:20
In this case you need to either retrieve collection of objects and extract distinct names from them manually or write query that will load distinct names (but not objects!) from database — Flying, Jan 03 '19 at 16:33
Thanks @Flying, so I do not really have the choice, it's an annoying limitation. — Hansen mattias, Jan 03 '19 at 16:59
Actually it is not a limitation, but a way that relational databases are working. I would recommend you to update your query to use `select distinct` and fetch single column of values to avoid loading unnecessary data in application — Flying, Jan 03 '19 at 17:03
https://stackoverflow.com/questions/23921117/disable-only-full-group-by — hous, Jan 03 '19 at 20:47
@hous full-group-by is the correct way to create a SQL query, disable it does not exist in other DBMS except mysql as i know... — Hansen mattias, Jan 04 '19 at 09:43

score 7 · Answer 1 · answered Nov 14 '19 at 11:22

You need to disable ONLY_FULL_GROUP_BY from mySQL to get rid of this exception. There are two options for you:-

1:- Disable it in the doctrine configuration

options:
    1002: 'SET sql_mode=(SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY", ""))'

If you add this to the doctrine configuration, you won't get the exception anymore.

2:- Disable it in the mySQL Either Run the following command inside mysql console

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Or delete it from phpmyadmin disable ONLY_FULL_GROUP_BY from phpmyadmin

This is my blog on this topic. You could check it and share your review.

Thanks!

Adding the 1002 option in doctrine configuration works fine ! Thanks. My questions are "Why this option ? Will this modification cause some other issue ?" — D.Picard, May 25 '21 at 10:06

Symfony query with ONLY_FULL_GROUP_BY ON

1 Answers1