Sonataadminbundle: Cannot export data with entities that have composite keys

[Ousret]

Environment

Sonata packages

$ composer show sonata-project/*
sonata-project/admin-bundle              3.13.0 The missing Symfony Admin Generator
sonata-project/block-bundle              3.3.0  Symfony SonataBlockBundle
sonata-project/cache                     1.0.7  Cache library
sonata-project/core-bundle               3.2.0  Symfony SonataCoreBundle
sonata-project/doctrine-orm-admin-bundle 3.1.3  Symfony Sonata / Integrate Doctrine ORM into the SonataAdminBundle
sonata-project/exporter                  1.7.1  Lightweight Exporter library

Symfony packages

$ composer show symfony/*
symfony/monolog-bundle     v2.12.1 Symfony MonologBundle
symfony/phpunit-bridge     v3.2.4  Symfony PHPUnit Bridge
symfony/polyfill-apcu      v1.3.0  Symfony polyfill backporting apcu_* functions to lower PHP versions
symfony/polyfill-intl-icu  v1.3.0  Symfony polyfill for intl's ICU-related data and classes
symfony/polyfill-mbstring  v1.3.0  Symfony polyfill for the Mbstring extension
symfony/polyfill-php56     v1.3.0  Symfony polyfill backporting some PHP 5.6+ features to lower PHP versions
symfony/polyfill-php70     v1.3.0  Symfony polyfill backporting some PHP 7.0+ features to lower PHP versions
symfony/polyfill-util      v1.3.0  Symfony utilities for portability of PHP codes
symfony/security-acl       v3.0.0  Symfony Security Component - ACL (Access Control List)
symfony/swiftmailer-bundle v2.4.2  Symfony SwiftmailerBundle
symfony/symfony            v3.2.4  The Symfony PHP framework

PHP version

$ php -v
PHP 7.0.15 (cli) (built: Jan 22 2017 08:51:45) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.15, Copyright (c) 1999-2017, by Zend Technologies

Subject

Entities that have composite key also known as multiple 'primary key' cannot be exported.

Exception: [Semantical Error] line 0, col -1 near 'SELECT DISTINCT': Error: '' is not defined.
Exporter tried to execute this DQL request:
'SELECT DISTINCT o FROM AppBundle\Entity\StockBatiment o LEFT JOIN o.batiment s_batiment LEFT JOIN o.produit s_produit ORDER BY'

ORDER BY clause has no argument.

Steps to reproduce

1) Create any entity with at least two Ids
2) Create the associated sonata admin class
3) Fill the table
4) Try to export data to csv for example.

Expected results

Not HTML exception ^^

Actual results

HTML with normal symfony 500 page.

INFO - Matched route "admin_app_stockbatiment_export". 
DEBUG - Read existing security token from the session. 
DEBUG - SELECT t0.username AS username_1, t0.username_canonical AS username_canonical_2, t0.email AS email_3, t0.email_canonical AS email_canonical_4, t0.enabled AS enabled_5, t0.salt AS salt_6, t0.password AS password_7, t0.last_login AS last_login_8, t0.confirmation_token AS confirmation_token_9, t0.password_requested_at AS password_requested_at_10, t0.roles AS roles_11, t0.id AS id_12, t0.rfid AS rfid_13, t0.rfid_code AS rfid_code_14, t0.date_naissance AS date_naissance_15, t0.nom AS nom_16, t0.prenom AS prenom_17, t0.telephone AS telephone_18 FROM utilisateur t0 WHERE t0.id = ? LIMIT 1 
DEBUG - SELECT t0.id AS id_1, t0.designation AS designation_2, t0.adresse AS adresse_3, t0.ipv4_strict AS ipv4_strict_4, t0.telephone AS telephone_5, t0.api_etiquettes AS api_etiquettes_6, t0.api_eeg AS api_eeg_7, t0.slug AS slug_8, t0.ville_id AS ville_id_9 FROM batiment t0 INNER JOIN utilisateur_batiment ON t0.id = utilisateur_batiment.batiment_id WHERE utilisateur_batiment.utilisateur_id = ? 
DEBUG - User was reloaded from a user provider. 
INFO - User Deprecated: The Sonata\CoreBundle\Exporter\Exporter class is deprecated since version 3.1 and will be removed in 4.0. Use Exporter\Exporter instead 
DEBUG - SELECT count(DISTINCT s0_.produit) AS sclr_0 FROM stock_batiment s0_ LEFT JOIN batiment b1_ ON s0_.batiment = b1_.id LEFT JOIN produit p2_ ON s0_.produit = p2_.id 
INFO - User Deprecated: The Sonata\AdminBundle\Admin\AbstractAdmin::trans method is deprecated since version 3.9 and will be removed in 4.0. 
WARNING - Translation not found. 
DEBUG - Stored the security token in the session. 
CRITICAL - Uncaught PHP Exception Doctrine\ORM\Query\QueryException: "[Semantical Error] line 0, col -1 near 'SELECT DISTINCT': Error: '' is not defined." at /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 63 

Others

I'm wondering if this issue is caused by sonata-admin or exporter.
I really need this to work, if someone could provide hotfix, I would really appreciate it.

Thank!

How to fix it for now

Sort at least one column before trying to export and you should be fine.

[greg0ire]

Please provide a stack trace

[Ousret]

Here you go.

[1] Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col -1 near 'SELECT DISTINCT': Error: '' is not defined.
at n/a
in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 63

at Doctrine\ORM\Query\QueryException::semanticalError('line 0, col -1 near \'SELECT DISTINCT\': Error: \'\' is not defined.', object(QueryException))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 483

at Doctrine\ORM\Query\Parser->semanticalError('line 0, col -1 near \'SELECT DISTINCT\': Error: \'\' is not defined.', null)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 707

at Doctrine\ORM\Query\Parser->processDeferredResultVariables()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 283

at Doctrine\ORM\Query\Parser->getAST()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 351

at Doctrine\ORM\Query\Parser->parse()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 281

at Doctrine\ORM\Query->_parse()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 229

at Doctrine\ORM\Query->getResultSetMapping()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 904

at Doctrine\ORM\AbstractQuery->iterate(null, 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 630

at Doctrine\ORM\Query->iterate()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/exporter/src/Source/DoctrineORMQuerySourceIterator.php line 129

at Exporter\Source\DoctrineORMQuerySourceIterator->rewind()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/exporter/src/Handler.php line 43

at Exporter\Handler->export()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/core-bundle/Exporter/Exporter.php line 66

at Sonata\CoreBundle\Exporter\Exporter->Sonata\CoreBundle\Exporter\{closure}()
    in  line 

at call_user_func(object(Closure))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/StreamedResponse.php line 108

at Symfony\Component\HttpFoundation\StreamedResponse->sendContent()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/Response.php line 419

at Symfony\Component\HttpFoundation\Response->send()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/EventListener/StreamedResponseListener.php line 41

at Symfony\Component\HttpKernel\EventListener\StreamedResponseListener->onKernelResponse(object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
    in  line 

at call_user_func(array(object(StreamedResponseListener), 'onKernelResponse'), object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/WrappedListener.php line 106

at Symfony\Component\EventDispatcher\Debug\WrappedListener->__invoke(object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
    in  line 

at call_user_func(object(WrappedListener), object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 3272

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))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 3187

at Symfony\Component\EventDispatcher\EventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/TraceableEventDispatcher.php line 136

at Symfony\Component\EventDispatcher\Debug\TraceableEventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4236

at Symfony\Component\HttpKernel\HttpKernel->filterResponse(object(StreamedResponse), object(Request), 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4231

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4171

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 168

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/web/app_dev.php line 30

at require('/Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/web/app_dev.php')
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Resources/config/router_dev.php line 40

[2] Doctrine\ORM\Query\QueryException: SELECT DISTINCT o FROM AppBundle\Entity\StockBatiment o LEFT JOIN o.batiment s_batiment LEFT JOIN o.produit s_produit ORDER BY
at n/a
in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 41

at Doctrine\ORM\Query\QueryException::dqlError('SELECT DISTINCT o FROM AppBundle\\Entity\\StockBatiment o LEFT JOIN o.batiment s_batiment LEFT JOIN o.produit s_produit ORDER BY ')
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 483

at Doctrine\ORM\Query\Parser->semanticalError('line 0, col -1 near \'SELECT DISTINCT\': Error: \'\' is not defined.', null)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 707

at Doctrine\ORM\Query\Parser->processDeferredResultVariables()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 283

at Doctrine\ORM\Query\Parser->getAST()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 351

at Doctrine\ORM\Query\Parser->parse()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 281

at Doctrine\ORM\Query->_parse()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 229

at Doctrine\ORM\Query->getResultSetMapping()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 904

at Doctrine\ORM\AbstractQuery->iterate(null, 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 630

at Doctrine\ORM\Query->iterate()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/exporter/src/Source/DoctrineORMQuerySourceIterator.php line 129

at Exporter\Source\DoctrineORMQuerySourceIterator->rewind()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/exporter/src/Handler.php line 43

at Exporter\Handler->export()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/core-bundle/Exporter/Exporter.php line 66

at Sonata\CoreBundle\Exporter\Exporter->Sonata\CoreBundle\Exporter\{closure}()
    in  line 

at call_user_func(object(Closure))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/StreamedResponse.php line 108

at Symfony\Component\HttpFoundation\StreamedResponse->sendContent()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpFoundation/Response.php line 419

at Symfony\Component\HttpFoundation\Response->send()
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/EventListener/StreamedResponseListener.php line 41

at Symfony\Component\HttpKernel\EventListener\StreamedResponseListener->onKernelResponse(object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
    in  line 

at call_user_func(array(object(StreamedResponseListener), 'onKernelResponse'), object(FilterResponseEvent), 'kernel.response', object(TraceableEventDispatcher))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/WrappedListener.php line 106

at Symfony\Component\EventDispatcher\Debug\WrappedListener->__invoke(object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
    in  line 

at call_user_func(object(WrappedListener), object(FilterResponseEvent), 'kernel.response', object(ContainerAwareEventDispatcher))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 3272

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))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 3187

at Symfony\Component\EventDispatcher\EventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/EventDispatcher/Debug/TraceableEventDispatcher.php line 136

at Symfony\Component\EventDispatcher\Debug\TraceableEventDispatcher->dispatch('kernel.response', object(FilterResponseEvent))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4236

at Symfony\Component\HttpKernel\HttpKernel->filterResponse(object(StreamedResponse), object(Request), 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4231

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/var/cache/dev/classes.php line 4171

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 168

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/web/app_dev.php line 30

at require('/Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/web/app_dev.php')
    in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Resources/config/router_dev.php line 40

[greg0ire]

SELECT DISTINCT o 
                    FROM AppBundle\\Entity\\StockBatiment o 
             LEFT JOIN o.batiment s_batiment 
             LEFT JOIN o.produit s_produit ORDER BY 

There's your problem.

[Ousret]

I figured this.. by myself ^^
DQL request wasn't generated correctly, but by whom ? sonata-admin or exporter ?

[greg0ire]

Maybe debug this file : in /Users/Ousret/Documents/SARL Distrigel/wwwdistrigel/vendor/sonata-project/core-bundle/Exporter/Exporter.php line 66 ?

[Ousret]

Maybe. I'll look into it.

[Ousret]

Exporter from Sonata-admin\core is deprecated, so it seems.

[greg0ire]

Can you link to the file you are talking about on github?

[Ousret]

https://github.com/sonata-project/SonataAdminBundle/blob/3.x/Export/Exporter.php
On my file, class is not empty.

[greg0ire]

Then you are using an outdated version. Please upgrade first. EDIT: it says you are using 3.13.0 in the PR body… what's the deal?

[Ousret]

3.13.0 is not the latest ?

[greg0ire]

Yes it is.

[greg0ire]

On my file, class is not empty.

How can it be not empty if you are using 3.13.0?

[Ousret]

But I've this one.. I've doubled checked composer update.
It tells me that there no update missing..

[greg0ire]

How can it be not empty if you are using 3.13.0?

[Ousret]

I don't know.

[greg0ire]

Maybe try reinstalling? rm vendor composer.lock -fr && composer update -v

[Ousret]

Just did. Same exception. But class is now empty. This is getting weirder and weirder..

[greg0ire]

Ok so that's actually better. Now you'll have to use your debugger and have a deeper look at this.

[Ousret]

This issue is going to be much harder to fix than I expected..
What fn does generate DQL req for export ? DataSourceIterator ?

[Ousret]

Working:
http://xxxx/admin/app/stockbatiment/export?filter%5B_sort_order%5D=ASC&filter%5B_sort_by%5D=quantite&filter%5B_page%5D=0&filter%5B_per_page%5D=32&format=csv

Not working:
http://xxxx/admin/app/stockbatiment/export?filter%5B_sort_order%5D=ASC&filter%5B_sort_by%5D=produit%2Cbatiment&filter%5B_page%5D=0&filter%5B_per_page%5D=32&format=csv

With composite key, I need to sort at least one column to proceed export. Now it's about filtering for sure.

Bye.

[Ousret]

Should be easy to provide hotfix.

[greg0ire]

Here is an urldecoded diff if anyone wants to understand.

- http://xxxx/admin/app/stockbatiment/export?filter[_sort_order]=ASC&filter[_sort_by]=produit,batiment&filter[_page]=0&filter[_per_page]=32&format=csv
+ http://xxxx/admin/app/stockbatiment/export?filter[_sort_order]=ASC&filter[_sort_by]=quantite&filter[_page]=0&filter[_per_page]=32&format=csv

[Ousret]

produit & batiment are Ids + ManyToOne
quantite is not Id and is simple Integer

Voila!

[Kaijiro]

Was it fixed ?

[Ousret]

@Kaijiro Nope. For now, just force ORDER BY clause to have at least one column assigned.

[eugenekurasov]

Hi,
It is issue for sonata-project/doctrine-orm-admin-bundle

https://github.com/sonata-project/SonataDoctrineORMAdminBundle/blob/3.x/Model/ModelManager.php#L512

$query->addOrderBy($query->getSortBy(), $query->getSortOrder());

getSortOrder - return empty string and generate wrong SQL.

[eugenekurasov]

One more.
I think you miss primary key or disabled sorting by primary key.
https://github.com/sonata-project/SonataAdminBundle/blob/3.x/Datagrid/Datagrid.php#L161 - and he don't pass by condition.
By the commits I can assume - This condition need for denied sort by invisible fields. So if you miss primary key or disabled sort by primary key you will be have exception on export.
I hope this is information was helpful.

[eugenekurasov]

I added example code with exception in pull request (https://github.com/sonata-project/SonataDoctrineORMAdminBundle/issues/696)