{"action":"opened","issue":{"url":"https://api.github.com/repos/sonata-project/SonataAdminBundle/issues/3147","labels_url":"https://api.github.com/repos/sonata-project/SonataAdminBundle/issues/3147/labels{/name}","comments_url":"https://api.github.com/repos/sonata-project/SonataAdminBundle/issues/3147/comments","events_url":"https://api.github.com/repos/sonata-project/SonataAdminBundle/issues/3147/events","html_url":"https://github.com/sonata-project/SonataAdminBundle/issues/3147","id":98509583,"number":3147,"title":"Pager: The 'DISTINCT' issue.","user":{"login":"anvaya","id":2928423,"avatar_url":"https://avatars.githubusercontent.com/u/2928423?v=3","gravatar_id":"","url":"https://api.github.com/users/anvaya","html_url":"https://github.com/anvaya","followers_url":"https://api.github.com/users/anvaya/followers","following_url":"https://api.github.com/users/anvaya/following{/other_user}","gists_url":"https://api.github.com/users/anvaya/gists{/gist_id}","starred_url":"https://api.github.com/users/anvaya/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anvaya/subscriptions","organizations_url":"https://api.github.com/users/anvaya/orgs","repos_url":"https://api.github.com/users/anvaya/repos","events_url":"https://api.github.com/users/anvaya/events{/privacy}","received_events_url":"https://api.github.com/users/anvaya/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-08-01T05:24:47Z","updated_at":"2015-08-01T05:24:47Z","closed_at":null,"body":"Hey,\r\n\r\n=> We have a MySQL database with over 40 million rows.\r\n\r\n=> Did a simple \"select id, field1, field2,.... from table limit 32\", returns in 1 sec.\r\n\r\n=> Built Admin over it using Sonata admin, takes forever to load practically the same thing.\r\n\r\n=> When the query was analyzed, it turns out sonata pager adds a \"DISTINCT\" keyword to the select statement.\r\n\r\n=> While I can understand its requirement in join queries, but when you have a query which includes the primary key, and no one-to-many joins, this practically kills performance. Pager needs to be smarter.  I had to comment this part of code to get things working.\r\n\r\n=> Is there a better option, perhaps I override this method in extended class and use that as the pager ? \r\n\r\nCode: \r\n========\r\nsonata-project\\doctrine-orm-admin-bundle\\Datagrid\\ProxyQuery.php\r\n\r\n/**\r\n     * This method alters the query to return a clean set of object with a working\r\n     * set of Object\r\n     *\r\n     * @param \\Doctrine\\ORM\\QueryBuilder $queryBuilder\r\n     *\r\n     * @return \\Doctrine\\ORM\\QueryBuilder\r\n     */\r\n    private function getFixedQueryBuilder(QueryBuilder $queryBuilder)\r\n    {\r\n        $queryBuilderId = clone $queryBuilder;\r\n\r\n        // step 1 : retrieve the targeted class\r\n        $from  = $queryBuilderId->getDQLPart('from');\r\n        $class = $from[0]->getFrom();\r\n        $metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);\r\n\r\n        // step 2 : retrieve identifier columns\r\n        $idNames = $metadata->getIdentifierFieldNames();\r\n\r\n        // step 3 : retrieve the different subjects ids\r\n        $selects = array();\r\n        $idxSelect = '';\r\n        foreach ($idNames as $idName) {\r\n            $select = sprintf('%s.%s', $queryBuilderId->getRootAlias(), $idName);\r\n            // Put the ID select on this array to use it on results QB\r\n            $selects[$idName] = $select;\r\n            // Use IDENTITY if id is a relation too. See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html\r\n            // Should work only with doctrine/orm: ~2.2\r\n            $idSelect = $select;\r\n            if ($metadata->hasAssociation($idName)) {\r\n                $idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);\r\n            }\r\n            $idxSelect .= ($idxSelect !== '' ? ', ' : '') . $idSelect;\r\n        }        \r\n\r\n        $queryBuilderId->resetDQLPart('select');\r\n        //$queryBuilderId->add('select', 'DISTINCT '.$idxSelect); //Commented by Mrugendra.\r\n        $queryBuilderId->add('select', $idxSelect);\r\n       \r\n//        .....\r\n\r\n        return $queryBuilder;\r\n    }"}}