Disable ONLY_FULL_GROUP_BY in doctrine

Anxious from getting an exception of ONLY_FULL_GROUP_BY with the Symfony project .?

There is a best and simple solution if you want to get rid of this exception from a particular Symfony project, all you need to do is, you have to update the configuration of the doctrine.

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8

You need to add 1002 key inside the options key with the value ‘SET sql_mode=(SELECT REPLACE(@@sql_mode, “ONLY_FULL_GROUP_BY”, “”))’ under the dbal configuration.
Like this:-

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        options:
            1002: 'SET sql_mode=(SELECT REPLACE(@@sql_mode, "ONLY_FULL_GROUP_BY", ""))'

That’s it. Now clear the cache if you’re having any trouble otherwise you’re ready to go.

moreover, if you want to change this configuration globally (for all of the projects), you could either do it from MySQL console or from PHPMyAdmin.

1:- From MySQL console

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

2:- From phpmyadmin

• Open phpmyadmin & select localhost
• Click on menu Variables & scroll down for sql mode
• Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.

I’ve explored this while contributing to Symfony based project UVdesk, there are a lot more things to learn and you could also contribute to an enterprise-level open source helpdesk.

That’s how you could disable ONLY_FULL_GROUP_BY in doctrine.
Thanks for reading me. I hope you’ll solve the issue with the help of this blog, please share your reviews on this, that will support me to write more.