[BUG] Mysql 5.7, General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list

[hshahdoost]

Package version, Laravel version

1.3.x, Lumen 5.4.x, Mysql 5.7

Expected behaviour

Return an aggregation on an entity (count)

Actual behaviour

Library no longer works for Aggregation operations after upgrading to Mysql 5.7

Steps to reproduce the behaviour

This issue has already been solved on the main Doctrine project here, The issue is also here.
After upgrading mysql to 5.7 due to a new flag on sql-mode (ONLY_FULL_GROUP_BY) we can no longer executing such queries. I suggest that you take the latest changes from Doctrine project.

Quick hack

For now it can be solved by updating sql-mode in your mysql configuration to the following line

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

[eigan]

This class (LimitSubqueryOutputWalker) Doesnt exist in laravel-doctrine. You would have to override it.

You can do that by creating a custom Paginator by extending Doctrine Paginator and use the fixed version of LimitSubqueryOutputWalker in getIterator.

Looks like Doctrine have some issues left before next release.

[hshahdoost]

@eigan I didn't quite catch what you mean by overriding this class, do you mean that this issue is invalid and should be solved by the developer?

[eigan]

Yes. There is probably not much this project can do other than having a patched Paginator.

I mean create your own by extending it. I have been doing it for the last year.

[patrickbrouwers]

Closing this as it's not really a issue with the laravel-doctrine package. For a proper fix you'll have to wait till they release 2.6. In the meantime setting sql-mode="" should "fix" the problem. Overwriting the Paginator is also an option, but we won't over a patched one, as it's doctrine/orm logic.

[hshahdoost]

@eigan sorry, I didn't know this is using Doctrine project, I thought it's a fork from it. Then you're right.

[minorsolis]

Hello!! Instead of changing the database, I fixed this issue following this (in the config/database.php) laravel/framework#14997 (comment)