CODEWITHSUNDEEP
mysqlsymfonydoctrine-orm
Desi Cochrane
Desi Cochrane

Reputation: 657

Doctrine Querybuilder ORDER BY clause is not in SELECT list

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.

Upvotes: 12

Views: 14544

Answers (6)

akronymn
akronymn

Reputation: 2446

Updating Doctrine to 2.8 should resolve this.

Upvotes: 0

Fracsi
Fracsi

Reputation: 2314

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');

Upvotes: 1

Suriya Kumar
Suriya Kumar

Reputation: 647

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

Upvotes: 3

mickadoo
mickadoo

Reputation: 3483

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.

Upvotes: 16

Amine Jallouli
Amine Jallouli

Reputation: 3959

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.

Upvotes: 22

1ed
1ed

Reputation: 3668

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.

Upvotes: 3

Related Questions