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

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 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

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.