CODEWITHSUNDEEP
mysqlsymfonydoctrine-ormgroup-by
Hansen mattias
Hansen mattias

Reputation: 23

Symfony query with ONLY_FULL_GROUP_BY ON

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 ?

Upvotes: 0

Views: 6272

Answers (1)

Shubham Mehrotra
Shubham Mehrotra

Reputation: 91

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!

Upvotes: 8

Related Questions