Doctrine Querybuilder ORDER BY子句不在SELECT列表中

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:

这一直很好:)但是自从升级到Mysql 5.7后我开始在任何地方都遇到这个错误：

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

SQLSTATE [HY000]：常规错误：3065 ORDER BY子句的表达式＃1不在SELECT列表中，引用列'dctrn_result.date_sent_5'不在SELECT列表中;这与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.

我已经在大多数使用DBAL层的地方解决了这个问题，只需将项添加到选择列表中，但我无法弄清楚如何使用这个特定的queryBuilder。

5 个解决方案

#1

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

您必须通过添加以下行来编辑/etc/mysql/mysql.cnf：

[mysqld]
sql-mode=""

Don't forget to restart the service mysql:

别忘了重启服务mysql：

sudo service mysql restart

For info, I am using Ubuntu 16.04 LTS.

有关信息，我使用的是Ubuntu 16.04 LTS。

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

到/etc/mysql/my.cnf为我解决了这个问题（重启服务后）。虽然官方对学说问题的回应当然会更好。

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

更新：比我更了解此建议的人只建议禁用导致问题的模式。

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

在＃4846中报告了一个错误，它似乎与#sqlmode_only_full_group_by有关，并且有一些例子，这是什么意思。在找到正确的解决方案之前，解决方案是在查询中添加 - > addSelect（'message'）（我不知道它是否修复了问题或者说教条还是重写了查询），但是这样的教义会将按摩水合作为好吧可能不希望或禁用ONLY_FULL_GROUP_BY sql模式，但是，mysql可能会返回无效数据。

#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

实际上mysql 5.7在sql模式下包含'ONLY_FULL_GROUP_BY'。所以我们不能在不在select list中的元素中执行orderby。我们必须从

'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

素里亚

#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时，连接的表不会自动添加到选择列表中。您可以调用addSelect（TABLE_ALIAS）来消除错误。

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

#1