Sonataadminbundle: ORDER BY clause is not in SELECT list when exporting

[kunicmarko20]

Subject

In my Admin class inside of configureListFields method I added a field that is a child entity property.:
$listMapper->add('user.email')

If I order list by that field and try to export I get:

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

So we need to have the field we use for order in our select list but we do not have that.
After some searching, I found out that ONLY_FULL_GROUP_BY is making this problem and while something like this can fix it, it is still a hack and not a solution.

After some debugging:

Adding $query->addSelect($sortBy); here adds that field to select but it makes problem with exporter:

here instead of getting an array with Object that is being iterated over we get array in array with Object as first value and value of that field we added to select as second and then that creates a problem. Few lines down we ask for $current[0] expecting it to be an Object but now it should be $current[0][0]. We could probably add check to see if $current[0] is array and take $current[0][0].

I could provide PRs but the "solution" I proposed feels a bit hackish, so I am open to suggestion.

Steps to reproduce

Install MySQL 5.7.5+ (I tested on 5.7.20)
Create Admin Class that has a property of its child class in list mapper.
Open list, order by that field and try to export.

Expected results

Exported file.

Actual results

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

   (vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php:50)
  at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array(), array())
     (vendor/doctrine/orm/lib/Doctrine/ORM/Query.php:321)
  at Doctrine\ORM\Query->_doExecute()
     (vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php:905)
  at Doctrine\ORM\AbstractQuery->iterate(null, 1)
     (vendor/doctrine/orm/lib/Doctrine/ORM/Query.php:630)
  at Doctrine\ORM\Query->iterate()
     (vendor/sonata-project/exporter/src/Source/DoctrineORMQuerySourceIterator.php:135)
  at Exporter\Source\DoctrineORMQuerySourceIterator->rewind()
     (vendor/sonata-project/exporter/src/Handler.php:43)
  at Exporter\Handler->export()
     (vendor/sonata-project/core-bundle/Exporter/Exporter.php:66)
  at Sonata\CoreBundle\Exporter\Exporter->Sonata\CoreBundle\Exporter\{closure}()
  at call_user_func(object(Closure))
     (vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/StreamedResponse.php:108)
  at Symfony\Component\HttpFoundation\StreamedResponse->sendContent()
     (vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/Response.php:384)
  at Symfony\Component\HttpFoundation\Response->send()
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/EventListener/StreamedResponseListener.php:41)
  at Symfony\Component\HttpKernel\EventListener\StreamedResponseListener->onKernelResponse(object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
  at call_user_func(array(object(StreamedResponseListener), 'onKernelResponse'), object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
     (vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/WrappedListener.php:104)
  at Symfony\Component\EventDispatcher\Debug\WrappedListener->__invoke(object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
  at call_user_func(object(WrappedListener), object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
     (vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/EventDispatcher.php:212)
  at Symfony\Component\EventDispatcher\EventDispatcher->doDispatch(array(object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener), object(WrappedListener)), 'kernel.response', object(FilterResponseEvent))
     (vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/EventDispatcher.php:44)
  at Symfony\Component\EventDispatcher\EventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
     (vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/TraceableEventDispatcher.php:146)
  at Symfony\Component\EventDispatcher\Debug\TraceableEventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:193)
  at Symfony\Component\HttpKernel\HttpKernel->filterResponse(object(StreamedResponse), object(Request), 1)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:175)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:68)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php:171)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/app_vagrant.php:11)

[jordisala1991]

Could this be related to this PRs?

https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/728
https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/785

[kunicmarko20]

Yes it is related.
adding $query->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [OrderByToSelectWalker::class]);
instead of $query->addSelect($sortBy); works, but there is still the same problem with exporter, we get:

array:2 [▼
  0 => array:1 [▼
    0 => Delivery {#3037 ▶}
  ]
  1 => array:1 [▼
    "email" => "[email protected]"
  ]
]

Instead of:

array:1 [▼
  0 => Delivery {#3023 ▶}
]

[jordisala1991]

What version of the doctrine-orm are you using?

[kunicmarko20]

tested on newest 3.4.1 (if we are talking about sonata-doctrine-orm?)

[jordisala1991]

No, I am talking about this one: https://packagist.org/packages/doctrine/orm (wrong name by me :P)

[kunicmarko20]

that is on 2.6.0, also tested on 2.5.10 same issue.

[jordisala1991]

Hmm.. Then:

1. We can maybe add $query->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [OrderByToSelectWalker::class]); to doctrineOrm to fix the first issue

2. We need to know what bundle or package is causing the second one. It is SonataoctrineOrmAdminBundle, exporter or core?

[kunicmarko20]

1. ModelManager returns the DoctrineORMQuerySourceIterator from exporter and array in array is visible here not sure if there is something in between.

[dmarkowicz]

@kunicmarko20 please check if https://github.com/sonata-project/SonataDoctrineORMAdminBundle/pull/805 solves your problem with OrderByToSelectWalker.

[kunicmarko20]

@dmarkowicz yes, this fixes the problem, thank you, we now only need to add setHint in ModelManager