Sonata's list doesn't work with PostgreSQL

[Birmania]

Hello,

I install Sonata AdminBundle on Symfony 2 with a PostgreSQL database and when I try to sort a list on a column, I obtain this error :

An exception has been thrown during the rendering of a template ("SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ....idsystem AS idsystem0 FROM systems s0 ORDER BY s0_.codesy... ^") in "SonataAdminBundle:CRUD:base_list.html.twig" at line 28.

It look like PostgreSQL need to have the "order by" columns in the SELECT statement to work contrary to MySQL.

Have you got a solution to solve this problem please ?

[rande]

Can you give me the full query and the stack trace ?

[Birmania]

Sure, there is it.

Full Query :

SELECT DISTINCT s0.idsymbol AS idsymbol0 FROM symbols s0 ORDER BY s0_.namesymbol ASC LIMIT 25 OFFSET 0

First stack trace :

[2/2] Twig_ErrorRuntime: An exception has been thrown during the rendering of a template ("SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ....idsymbol AS idsymbol0 FROM symbols s0 ORDER BY s0.namesy...
^") in "SonataAdminBundle:CRUD:base_list.html.twig" at line 28. -

in /tmp/Symfony/app/cache/dev/classes.php at line 7403 -+

1. } catch (Twig_Error $e) {
2. throw $e;
3. } catch (Exception $e) {
4. throw new Twig_Error_Runtime(sprintf('An exception has been thrown during the rendering of a template ("%s").', $e->getMessage()), -1, null, $e);
5. }
6. }
7. /**

at Twig_Template ->display (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_table' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters')))

in /tmp/Symfony/app/cache/dev/twig/37/a0/f7529f7d22400ddbc9948cce03b7.php at line 39 -+

1. {
2. $context = array_merge($this->env->getGlobals(), $context); 38.
3. $this->getParent($context)->display($context, array_merge($this->blocks, $blocks));
4. } 41.
5. // line 14

at __TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7 ->doDisplay (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array())

in /tmp/Symfony/app/cache/dev/classes.php at line 7399 -+

1. public function display(array $context, array $blocks = array())
2. {
3. try {
4. $this->doDisplay($context, $blocks);
5. } catch (Twig_Error $e) {
6. throw $e;
7. } catch (Exception $e) {

at Twig_Template ->display (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array())

in /tmp/Symfony/app/cache/dev/twig/61/46/0679a1df2b78e79550c29673e015.php at line 21 -+

1. {
2. $context = array_merge($this->env->getGlobals(), $context); 20.
3. $this->getParent($context)->display($context, array_merge($this->blocks, $blocks));
4. } 23.
5. public function getTemplateName()

at __TwigTemplate_61460679a1df2b78e79550c29673e015 ->doDisplay (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array())

in /tmp/Symfony/app/cache/dev/classes.php at line 7399 -+

1. public function display(array $context, array $blocks = array())
2. {
3. try {
4. $this->doDisplay($context, $blocks);
5. } catch (Twig_Error $e) {
6. throw $e;
7. } catch (Exception $e) {

at Twig_Template ->display (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'))

in /tmp/Symfony/app/cache/dev/classes.php at line 7418 -+

1. $level = ob_get_level();
2. ob_start();
3. try {
4. $this->display($context);
5. } catch (Exception $e) {
6. while (ob_get_level() > $level) {
7. ob_end_clean();

at Twig_Template ->render (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'))

in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/TwigBundle/TwigEngine.php at line 60 -+

1. */
2. public function render($name, array $parameters = array())
3. {
4. return $this->load($name)->render($parameters);
5. } 62.
6. /**

at TwigEngine ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'))

in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/TwigBundle/TwigEngine.php at line 114 -+

1. $response = new Response();
2. } 113.
3. $response->setContent($this->render($view, $parameters)); 115.
4. return $response;
5. }

at TwigEngine ->renderResponse ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), null)

in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/FrameworkBundle/Controller/Controller.php at line 98 -+

1. */
2. public function render($view, array $parameters = array(), Response $response = null)
3. {
4. return $this->container->get('templating')->renderResponse($view, $parameters, $response);
5. } 100.
   1. /**

at Controller ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'))

in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Controller/CRUDController.php at line 128 -+

1. $parameters['admin'] = isset($parameters['admin']) ? $parameters['admin'] : $this->admin;
2. $parameters['base_template'] = isset($parameters['base_template']) ? $parameters['base_template'] : $this->getBaseTemplate(); 127.
3. return parent::render($view, $parameters);
4. } 130.
5. /**

at CRUDController ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list'))

in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Controller/CRUDController.php at line 144 -+

141.

1. return $this->render($this->admin->getListTemplate(), array(
2. 'action' => 'list',
3. ));
4. } 146.
5. /**

at CRUDController ->listAction ()

in at line -+

at call_user_func_array (array(object(SymbolsAdminController), 'listAction'), array())

in /tmp/Symfony/app/cache/dev/classes.php at line 3880 -+

3877.

3878. $arguments = $this->resolver->getArguments($request, $controller);
3880. $response = call_user_func_array($controller, $arguments);
3882. if (!$response instanceof Response) {
3883. $event = new GetResponseForControllerResultEvent($this, $request, $type, $response);

at HttpKernel ->handleRaw (object(Request), '1')

in /tmp/Symfony/app/cache/dev/classes.php at line 3850 -+

1. public function handle(Request $request, $type = HttpKernelInterface::MASTER_REQUEST, $catch = true)
2. {
3. try {
4. return $this->handleRaw($request, $type);
5. } catch (\Exception $e) {
6. if (false === $catch) {
7. throw $e;

at HttpKernel ->handle (object(Request), '1', true)

in /tmp/Symfony/app/cache/dev/classes.php at line 4785 -+

1. $this->container->set('request', $request, 'request'); 4783.
2. try {
3. $response = parent::handle($request, $type, $catch);
4. } catch (\Exception $e) {
5. $this->container->leaveScope('request'); 4788.

at HttpKernel ->handle (object(Request), '1', true)

in kernel.root_dir/bootstrap.php.cache at line 547 -+

1. $this->boot();
2. } 546.
3. return $this->getHttpKernel()->handle($request, $type, $catch);
4. } 549.

at Kernel ->handle (object(Request))

in /home/abrultet/interface-admin/Symfony/web/app_dev.php at line 24 -

Second stack trace :

[1/2] PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ....idsymbol AS idsymbol0 FROM symbols s0 ORDER BY s0_.namesy...
^ -+

1. in /home/abrultet/interface-admin/Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php at line 618 -+
   1. $stmt->execute($params);
   2. }
   3. } else {
   4. $stmt = $this->_conn->query($query);
   5. } 620.
   6. if ($hasLogger) {
2. at PDO ->query ('SELECT DISTINCT s0.idsymbol AS idsymbol0 FROM symbols s0 ORDER BY s0_.namesymbol ASC LIMIT 25 OFFSET 0') in /home/abrultet/interface-admin/Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php at line 618 -+
   1. $stmt->execute($params);
   2. }
   3. } else {
   4. $stmt = $this->_conn->query($query);
   5. } 620.
   6. if ($hasLogger) {
3. at Connection ->executeQuery ('SELECT DISTINCT s0.idsymbol AS idsymbol0 FROM symbols s0 ORDER BY s0_.namesymbol ASC LIMIT 25 OFFSET 0', array(), array()) in /home/abrultet/interface-admin/Symfony/vendor/doctrine/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php at line 46 -+ 43.
   44. public function execute(Connection $conn, array $params, array $types)
   45. {
   46. return $conn->executeQuery($this->_sqlStatements, $params, $types);
   47. }
   48. } 49.
4. at SingleSelectExecutor ->execute (object(Connection), array(), array()) in /home/abrultet/interface-admin/Symfony/vendor/doctrine/lib/Doctrine/ORM/Query.php at line 274 -+
   1. $this->_resultSetMapping = $this->_parserResult->getResultSetMapping();
   2. } 273.
   3. return $executor->execute($this->_em->getConnection(), $sqlParams, $types);
   4. } 276.
   5. /**
5. at Query ->_doExecute () in /home/abrultet/interface-admin/Symfony/vendor/doctrine/lib/Doctrine/ORM/AbstractQuery.php at line 586 -+
   1. }
   2. } 585.
   3. $stmt = $this->_doExecute(); 587.
   4. if (is_numeric($stmt)) {
   5. return $stmt;
6. at AbstractQuery ->execute (array(), '2') in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Datagrid/ORM/ProxyQuery.php at line 73 -+
   70. $queryBuilderId->resetDQLPart('select');
   71. $queryBuilderId->add('select', 'DISTINCT '.$select);
   73. $results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
   74. $idx = array();
   75. $connection = $queryBuilder->getEntityManager()->getConnection();
   76. foreach($results as $id) {
7. at ProxyQuery ->getFixedQueryBuilder (object(QueryBuilder)) in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Datagrid/ORM/ProxyQuery.php at line 47 -+
   44. $queryBuilder->orderBy($sortBy, $this->getSortOrder());
   45. }
   47. return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
   48. }
   50. /**
8. at ProxyQuery ->execute (array(), '1') in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Datagrid/ORM/Pager.php at line 53 -+
   50. _/
   51. public function getResults($hydrationMode = Query::HYDRATE_OBJECT)
   52. {
   53. return $this->getQuery()->execute(array(), $hydrationMode);
   54. }
   56. /_*
9. at Pager ->getResults () in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Datagrid/Datagrid.php at line 66 -+
   63. $this->buildPager();
   65. if (!$this->results) {
   66. $this->results = $this->pager->getResults();
   67. }
   69. return $this->results;
       1. at Datagrid ->getResults () in at line -+
       2. at call_user_func_array (array(object(Datagrid), 'getresults'), array()) in /tmp/Symfony/app/cache/dev/classes.php at line 7550 -+
       3. if ($this->env->hasExtension('sandbox')) {
       4. $this->env->getExtension('sandbox')->checkMethodAllowed($object, $method);
       5. }
       6. $ret = call_user_func_array(array($object, $method), $arguments);
       7. if ($object instanceof Twig_TemplateInterface) {
       8. return new Twig_Markup($ret);
       9. }
       10. at Twig_Template ->getAttribute (object(Datagrid), 'results', array(), 'any', false) in /tmp/Symfony/app/cache/dev/twig/37/a0/f7529f7d22400ddbc9948cce03b7.php at line 300 -+
       11. $context['batchactions'] = $this->getAttribute($this->getContext($context, 'admin'), "batchactions", array(), "any", false);
       12. // line 28
       13. echo " ";
       14. if ((twig_length_filter($this->env, $this->getAttribute($this->getAttribute($this->getContext($context, 'admin'), "datagrid", array(), "any", false), "results", array(), "any", false)) > 0)) {
       15. // line 29
       16. echo " <form action=\"";
       17. echo twig_escape_filter($this->env, $this->getAttribute($this->getContext($context, 'admin'), "generateUrl", array("batch", $this->getAttribute($this->getContext($context, 'admin'), "filterParameters", array(), "any", false), ), "method", false), "html");
       18. at TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7 ->block_list_table (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig', '_preview' => '', '_form' => '', '_show' => ''), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in at line -+
       19. at call_user_func (array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig', '_preview' => '', '_form' => '', '_show' => ''), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in /tmp/Symfony/app/cache/dev/classes.php at line 7324 -+
       20. if (isset($blocks[$name])) {
       21. $b = $blocks;
       22. unset($b[$name]);
       23. call_user_func($blocks[$name], $context, $b);
       24. } elseif (isset($this->blocks[$name])) {
       25. call_user_func($this->blocks[$name], $context, $blocks);
       26. } elseif (false !== $parent = $this->getParent($context)) {
       27. at Twig_Template ->displayBlock ('list_table', array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig', '_preview' => '', '_form' => '', '_show' => ''), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_table' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in /tmp/Symfony/app/cache/dev/classes.php at line 7358 -+
       28. public function renderBlock($name, array $context, array $blocks = array())
       29. {
       30. ob_start();
       31. $this->displayBlock($name, $context, $blocks);
       32. return ob_get_clean();
       33. }
       34. /**
       35. at Twig_Template ->renderBlock ('list_table', array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig', '_preview' => '', '_form' => '', '_show' => ''), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_table' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in /tmp/Symfony/app/cache/dev/twig/64/be/1d596c610f7300963d900cfeb5df.php at line 62 -+
       36. $context['_show'] = $this->renderBlock("show", $context, $blocks);
       37. // line 59
       38. echo " ";
       39. $context['_list_table'] = $this->renderBlock("list_table", $context, $blocks);
       40. // line 60
       41. echo " ";
       42. $context['_list_filters'] = $this->renderBlock("list_filters", $context, $blocks);
       43. at TwigTemplate_64be1d596c610f7300963d900cfeb5df ->doDisplay (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array('actions' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_table' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in /tmp/Symfony/app/cache/dev/classes.php at line 7399 -+
       44. public function display(array $context, array $blocks = array())
       45. {
       46. try {
       47. $this->doDisplay($context, $blocks);
       48. } catch (Twig_Error $e) {
       49. throw $e;
       50. } catch (Exception $e) {
       51. at Twig_Template ->display (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array('actions' => array(object(__TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_actions'), 'side_menu' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_side_menu'), 'table_header' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_header'), 'table_body' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_body'), 'table_footer' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_table_footer'), 'list_table' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_table'), 'list_filters' => array(object(TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7), 'block_list_filters'))) in /tmp/Symfony/app/cache/dev/twig/37/a0/f7529f7d22400ddbc9948cce03b7.php at line 39 -+
       52. {
       53. $context = array_merge($this->env->getGlobals(), $context); 38.
       54. $this->getParent($context)->display($context, array_merge($this->blocks, $blocks));
       55. } 41.
       56. // line 14
       57. at __TwigTemplate_37a0f7529f7d22400ddbc9948cce03b7 ->doDisplay (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array()) in /tmp/Symfony/app/cache/dev/classes.php at line 7399 -+
   70. public function display(array $context, array $blocks = array())
   71. {
   72. try {
   73. $this->doDisplay($context, $blocks);
   74. } catch (Twig_Error $e) {
   75. throw $e;
   76. } catch (Exception $e) {
       11. at Twig_Template ->display (array('assetic' => array('debug' => true, 'use_controller' => true), 'app' => object(GlobalVariables), 'action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array()) in /tmp/Symfony/app/cache/dev/twig/61/46/0679a1df2b78e79550c29673e015.php at line 21 -+
   77. {
   78. $context = array_merge($this->env->getGlobals(), $context); 20.
   79. $this->getParent($context)->display($context, array_merge($this->blocks, $blocks));
   80. } 23.
   81. public function getTemplateName()
       12. at __TwigTemplate_61460679a1df2b78e79550c29673e015 ->doDisplay (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), array()) in /tmp/Symfony/app/cache/dev/classes.php at line 7399 -+
   82. public function display(array $context, array $blocks = array())
   83. {
   84. try {
   85. $this->doDisplay($context, $blocks);
   86. } catch (Twig_Error $e) {
   87. throw $e;
   88. } catch (Exception $e) {
       13. at Twig_Template ->display (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig')) in /tmp/Symfony/app/cache/dev/classes.php at line 7418 -+
   89. $level = ob_get_level();
   90. ob_start();
   91. try {
   92. $this->display($context);
   93. } catch (Exception $e) {
   94. while (ob_get_level() > $level) {
   95. ob_end_clean();
       14. at Twig_Template ->render (array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig')) in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/TwigBundle/TwigEngine.php at line 60 -+
   96. */
   97. public function render($name, array $parameters = array())
   98. {
   99. return $this->load($name)->render($parameters);
   100. } 62.
   101. /**
        15. at TwigEngine ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig')) in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/TwigBundle/TwigEngine.php at line 114 -+
   102. $response = new Response();
   103. } 113.
   104. $response->setContent($this->render($view, $parameters)); 115.
   105. return $response;
   106. }
        16. at TwigEngine ->renderResponse ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig'), null) in /home/abrultet/interface-admin/Symfony/vendor/symfony/src/Symfony/Bundle/FrameworkBundle/Controller/Controller.php at line 98 -+
   107. */
   108. public function render($view, array $parameters = array(), Response $response = null)
   109. {
   110. return $this->container->get('templating')->renderResponse($view, $parameters, $response);
   111. } 100.
        1. /**
           17. at Controller ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list', 'admin' => object(SymbolsAdmin), 'base_template' => 'SonataAdminBundle::standard_layout.html.twig')) in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Controller/CRUDController.php at line 128 -+
   112. $parameters['admin'] = isset($parameters['admin']) ? $parameters['admin'] : $this->admin;
   113. $parameters['base_template'] = isset($parameters['base_template']) ? $parameters['base_template'] : $this->getBaseTemplate(); 127.
   114. return parent::render($view, $parameters);
   115. } 130.
   116. /**
        18. at CRUDController ->render ('SonataAdminBundle:CRUD:list.html.twig', array('action' => 'list')) in /home/abrultet/interface-admin/Symfony/vendor/bundles/Sonata/AdminBundle/Controller/CRUDController.php at line 144 -+
   117. return $this->render($this->admin->getListTemplate(), array(
   118. 'action' => 'list',
   119. ));
   120. } 146.
   121. /**
        19. at CRUDController ->listAction () in at line -+
        20. at call_user_func_array (array(object(SymbolsAdminController), 'listAction'), array()) in /tmp/Symfony/app/cache/dev/classes.php at line 3880 -+
   123. $arguments = $this->resolver->getArguments($request, $controller); 3879.
   124. $response = call_user_func_array($controller, $arguments); 3881.
   125. if (!$response instanceof Response) {
   126. $event = new GetResponseForControllerResultEvent($this, $request, $type, $response);
        21. at HttpKernel ->handleRaw (object(Request), '1') in /tmp/Symfony/app/cache/dev/classes.php at line 3850 -+
   127. public function handle(Request $request, $type = HttpKernelInterface::MASTER_REQUEST, $catch = true)
   128. {
   129. try {
   130. return $this->handleRaw($request, $type);
   131. } catch (\Exception $e) {
   132. if (false === $catch) {
   133. throw $e;
        22. at HttpKernel ->handle (object(Request), '1', true) in /tmp/Symfony/app/cache/dev/classes.php at line 4785 -+
   134. $this->container->set('request', $request, 'request'); 4783.
   135. try {
   136. $response = parent::handle($request, $type, $catch);
   137. } catch (\Exception $e) {
   138. $this->container->leaveScope('request'); 4788.
        23. at HttpKernel ->handle (object(Request), '1', true) in kernel.root_dir/bootstrap.php.cache at line 547 -+
   139. $this->boot();
   140. } 546.
   141. return $this->getHttpKernel()->handle($request, $type, $catch);
   142. } 549.
   143. 24. at Kernel ->handle (object(Request)) in /home/abrultet/interface-admin/Symfony/web/app_dev.php at line 24 -+

[lsmith77]

s0_.namesymbol .. looks like that should be s0.namesymbol

[Birmania]

Yeah and s0.namesymbol must be in the SELECT part I think. Like that : SELECT DISTINCT s0.idsymbol AS idsymbol0,s0.namesymbol FROM symbols s0 ORDER BY s0.namesymbol ASC LIMIT 25 OFFSET 0

[Birmania]

In this code (Sonata\AdminBundle\Datagrid\ORM\ProxyQuery.php) :

public function execute(array $params = array(), $hydrationMode = null)
{
    // always clone the original queryBuilder
    $queryBuilder = clone $this->queryBuilder;

    // todo : check how doctrine behave, potential SQL injection here ...
    if ($this->getSortBy()) {
        $sortBy = $this->getSortBy();
        if (strpos($sortBy, '.') === false) { // add the current alias
            $sortBy = $queryBuilder->getRootAlias().'.'.$sortBy;
        }
        $queryBuilder->orderBy($sortBy, $this->getSortOrder());   
        $queryBuilder->addSelect($sortBy); //Line added
    }

    return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
}

I try to add the line : $queryBuilder->addSelect($sortBy);

But it cause this error : ErrorException: Warning: spl_object_hash() expects parameter 1 to be object, array given in /home/abrultet/interface-admin/Symfony/vendor/doctrine/lib/Doctrine/ORM/UnitOfWork.php line 1180

I understand the error (the select part became an array) but I think it's the good thing to do. How can I do to solve the PostgreSQL usage problem so ?

[ishiharay]

Hi, Birmania.

I modified 2 lines and added 3 lines. It works for me.

public function execute(array $params = array(), $hydrationMode = null)
{
    // always clone the original queryBuilder
    $queryBuilder = clone $this->queryBuilder;

    // todo : check how doctrine behave, potential SQL injection here ...
    if ($this->getSortBy()) {
        $sortBy = $this->getSortBy();
        if (strpos($sortBy, '.') === false) { // add the current alias
            $sortBy = $queryBuilder->getRootAlias().'.'.$sortBy;
        }
        $queryBuilder->orderBy($sortBy, $this->getSortOrder());
    }

    return $this->getFixedQueryBuilder($queryBuilder, $sortBy)->getQuery()->execute($params, $hydrationMode); // modified
}

private function getFixedQueryBuilder(QueryBuilder $queryBuilder, $sortBy = null) // modified
{
    $queryBuilderId = clone $queryBuilder;

    // step 1 : retrieve the targeted class
    $from = $queryBuilderId->getDQLPart('from');
    $class = $from[0]->getFrom();

    // step 2 : retrieve the column id
    $idName = current($queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class)->getIdentifierFieldNames());

    // step 3 : retrieve the different subjects id
    $select = sprintf('%s.%s', $queryBuilderId->getRootAlias(), $idName);
    $queryBuilderId->resetDQLPart('select');
    $queryBuilderId->add('select', 'DISTINCT '.$select);

    if ($sortBy) { // added
        $queryBuilderId->addSelect($sortBy); // added
    } // added

    $results  = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
    $idx      = array();
    $connection = $queryBuilder->getEntityManager()->getConnection();
    foreach($results as $id) {
         $idx[] = $connection->quote($id[$idName]);
    }

    // step 4 : alter the query to match the targeted ids
    if (count($idx) > 0) {
        $queryBuilder->andWhere(sprintf('%s IN (%s)', $select, implode(',', $idx)));
        $queryBuilder->setMaxResults(null);
        $queryBuilder->setFirstResult(null);
    }

    return $queryBuilder;
}

[mvrhov]

I haven't seen the issue before, so I made an individual PR which was just merged so this should now work by default for anyone using master. @Birmania can you confirm, that the master also works for you and close the ticket afterwards if it does.

[helmer]

Hmm, if you have a DISTINCT query plus an ORDER BY clause, you would also need to add a `GROUP BY, in which case the result set will be different (unless you use some random throw-away aggregator to group the ordered elements).

But other than that, all of this seems kind of fishy and is just a workaround, perhaps someone can offer a clean solution? I can look into it tomorrow, but perhaps we can get some input from Doctrine fellows meanwhile?

EDIT: And why is the query always modified with a DISTINCT Id, shouldn't this kind of logic reside in the endpoints, not in the direct wrapper?

EDIT2: Postgre support for NewsBundle is poor as well, there are some direct LIKE expressions against date fields, which are not supported by PG since 8.3 and a helper function parsing dates into strings seem to be lacking from ORM at this point as well :(

EDIT3: Issued a PR in NewsBundle to fix EDIT2 comment.

[soullivaneuh]

Seems to be fixed by @helmer in #14.

BTW, this old issue seems to be not relevant anymore.

Going to close it.

@rande Please reopen it if i'm wrong.