Doctrine Querybuilder ORDER BY clause is not in SELECT list

Question

I have the following query builder:

$queryBuilder = $this
    ->createQueryBuilder('recipient')
    ->leftJoin('recipient.message', 'message')
    ->orderBy('message.dateSent', 'DESC');

This has been working fine :) - but since upgrading to Mysql 5.7 I have started getting this error everywhere:

SQLSTATE[HY000]:
General error: 3065
Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.date_sent_5' which is not in SELECT list;
this is incompatible with DISTINCT

I have solved this in most places where I am using the DBAL layer by just add the item to the select list, but I can't figure out how to do that with this particular queryBuilder.

Answer 1

You have to edit the /etc/mysql/mysql.cnf by adding these lines:

[mysqld]
sql-mode=""

Don't forget to restart the service mysql:

sudo service mysql restart

For info, I am using Ubuntu 16.04 LTS.

Answer 2

Adding:

[mysqld]
sql-mode=""

to /etc/mysql/my.cnf fixed the problem for me (after restarting service). Although of course an official response to the doctrine issue would be nicer.

Update: Someone who knows more than me about this recommended only disabling the mode that's causing the problem.

Answer 3

There is a bug reported in #4846 and it seems to be related to #sqlmode_only_full_group_by and there are some examples abaut what does it mean here. Until a proper fix comes out a solution would be to add ->addSelect('message') to the query (I don't know if it fixes the issue or doctrine rewrites the query anyway), but that way doctrine will hydrate massages as well which maybe not desired or disable ONLY_FULL_GROUP_BY sql mode, but then, mysql maybe can return invalid data.

Answer 4

Actually mysql 5.7 contains 'ONLY_FULL_GROUP_BY' in sql mode.So we can't perform orderby in the element that is not in select list.we have to change it from

'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

into

'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

We can done this by executing the following queries

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Thanks,

Suriya

Answer 5

When using QueryBuilder, joined tables are not added to the select list automatically. You can call addSelect(TABLE_ALIAS) to get rid of the error.

$queryBuilder = $this
    ->createQueryBuilder('recipient')
    ->leftJoin('recipient.message', 'message')
    ->addSelect('message') //THIS LINE
    ->orderBy('message.dateSent', 'DESC');

Answer 6

Updating Doctrine to 2.8 should resolve this.