SonataAdminBundle: Cannot export data with entities that have composite keys

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.

About this issue

  • Original URL
  • State: closed
  • Created 7 years ago
  • Comments: 29 (11 by maintainers)

Commits related to this issue

Most upvoted comments

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
+2
greg0ire on Feb 19, 2017

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.

+1
eugenekurasov on Jun 26, 2017

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

Not working: http://xxxx/admin/app/stockbatiment/export?filter[_sort_order]=ASC&filter[_sort_by]=produit%2Cbatiment&filter[_page]=0&filter[_per_page]=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.

+1
Ousret on Feb 19, 2017

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
+1
Ousret on Jun 26, 2017
Read more comments on GitHub
← SonataAdminBundle: block type not found
SonataAdminBundle: Checkbox input not showing (for users : enabled and roles) →