mirror of
https://github.com/doctrine/orm.git
synced 2026-03-23 22:42:18 +01:00
DDC-1927: Pagination of a SELECT of specific fields results in a RuntimeException #2431
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @doctrinebot on GitHub (Jul 16, 2012).
Originally assigned to: @Ocramius on GitHub.
Jira issue originally created by user netiul:
When paginating a DQL string which selects specific fields it results in the following error: PHP Fatal error: Uncaught exception 'RuntimeException' with message 'Not all identifier properties can be found in the ResultSetMapping: id'
NOT working: 'SELECT c.id, c.number FROM Application\Entity\Course c'
WORKING: 'SELECT c FROM Application\Entity\Course c'
WORKING: 'SELECT c, c.id, c.number FROM Application\Entity\Course c'
Setting hydration mode to scalar results makes no difference.
Gist to example code and stack trace: https://gist.github.com/d5cd6d0b0ac28e722dd7
@doctrinebot commented on GitHub (Jul 29, 2012):
Comment created by @beberlei:
First results: This is very complicated to support, the pagination was designed for entity results. I have to check this when I have more time.
@doctrinebot commented on GitHub (Aug 8, 2012):
Comment created by dquintard:
Hi Benjamin,
I can change my code to select entity results (even for hundreds/thousands results).
But for now Pagination is not usable with ResultSetMapping::addScalarResult
@doctrinebot commented on GitHub (Nov 13, 2012):
Comment created by mpinkston:
The reason this error occurs is because the Paginator creates its own ResultSetMapping and relies on the SqlWalker to configure it (see Doctrine\ORM\Query\SqlWalker::walkSelectExpression).
Doctrine will interpret each field in the first example (SELECT c.id, c.number...) as a PathExpression and add it to the result set mapping as a scalar result. This makes it impossible for the paginator to reliably know which field can be considered an identifier.
A quick fix might be to re-write the query to use PartialObjectExpression: SELECT partial c.{id, number} ...
(edited after a re-read and realization that a custom ResultSetMapping wouldn't cut it)
@doctrinebot commented on GitHub (Nov 25, 2012):
Comment created by @beberlei:
Allowing generic+complex pagination on scalar results is impossible for us, closing as can't fix.
Just use LIMITs yourself here or as suggested partial objects.
@doctrinebot commented on GitHub (Nov 25, 2012):
Issue was closed with resolution "Can't Fix"
@doctrinebot commented on GitHub (Apr 18, 2013):
Comment created by vecchia:
imho this pagination feature is quite useless if we are forced to fetch the complete Entity. Take for example a big table with a lot of data: extracting all the infos will take a lot of time... There should be a way to support the first query type
@doctrinebot commented on GitHub (Sep 10, 2013):
Comment created by phpcoder:
Did i miss something about Doctrine 2 ? What is the point if we can't use pagination? Selecting only several/needed fields is as standard practice as brushing your teeth every day. And pagination doesn't work when i select only specific fields (it throws 'Not all identifier properties can be found in the ResultSetMapping: id' error). I also thought that
$query->setFirstResult(20);and$query->setMaxResults(100);works fine with joins but it turns out it doesn't work. It looks too complicated to integrate such common tool as pagination (I'm switching from CakePHP based ORM which works just fine with any kind of joins etc, no problems with pagination so far, but we are switching to Symfony 2 which goes with Doctrine 2 and actually i was recommended to use Doctrine 2, but i clearly see solid problems atm :/)@doctrinebot commented on GitHub (Sep 10, 2013):
Comment created by netiul:
@Busta Rhymes - Use partial entities (only the data of the fields you specify is fetched and set in the entity). As pagination is most of the time used just for displaying data, one is fine with that.
@doctrinebot commented on GitHub (Oct 23, 2013):
Comment created by ficuscr:
I was pretty disappointed with this resolution. I fail to see how the tasks of a paginator fail to work with Query object foo yet work with Query object bar. They are the same objects. I would think a query is a query for the intent and purpose of the paginator, get a count, set limit and offset, give me back the page of results. All the more so if that Query has HydrationMode set to an array.
What does "generic+complex pagination" even mean?
@doctrinebot commented on GitHub (Oct 23, 2013):
Comment created by @ocramius:
As discussed in private, the problem is that doctrine needs a selected root entity in a DQL query in order to paginate the results.
That's a current limitation that cannot be worked around.
What could be done is giving a "meaning" to scalars being selected, so that they are upgraded to (for example) identifiers of a particular root entity.
That is still not worth it given the amount of bugs and increase in LOC that spawns from it.
@doctrinebot commented on GitHub (Oct 12, 2014):
Comment created by s.todorov:
For anybody who might be experiencing this issue, a possible workaround might be:
{quote}
$paginator = new Paginator($query);
$paginator->setUseOutputWalkers(false);
{quote}
@pablomoreno61 commented on GitHub (Feb 17, 2016):
is this issue fixed?
@Ocramius commented on GitHub (Feb 17, 2016):
As I noted above, this limitation cannot currently be worked atound. The exception is thrown voluntarily.
@pablomoreno61 commented on GitHub (Feb 17, 2016):
@Ocramius do you have any idea if this issue will be attacked soon?
@Ocramius commented on GitHub (Feb 17, 2016):
It won't be attacked at all. The limitation is reasonable/understandable.
@asifaglodiya commented on GitHub (Jun 22, 2016):
You can use the DoctrineDbalAdapter to select specific columns from the table
<?php use Pagerfanta\Adapter\DoctrineDbalAdapter; use Doctrine\DBAL\Query\QueryBuilder; $queryBuilder = new QueryBuilder($conn); $queryBuilder->select('p.id, p.name,p.date_created')->from('posts', 'p'); $countQueryBuilderModifier = function ($queryBuilder) { $queryBuilder->select('COUNT(DISTINCT p.id) AS total_results') ->setMaxResults(1); }; $adapter = new DoctrineDbalAdapter($queryBuilder, $countQueryBuilderModifier); $pagerfanta = new Pagerfanta($adapter); $pagerfanta->setMaxPerPage(15); // 10 by default $page = $request->query->get('page', 1); $pagerfanta->setCurrentPage($page); // 1 by default if ($pagerfanta->hasPreviousPage()) { $pagerfanta->getPreviousPage(); } if ($pagerfanta->hasNextPage()) { $pagerfanta->getNextPage(); } return $this->render(TestManagerBundle:Post:index.html.twig', array('pagination' => $pagerfanta));@Sohorev commented on GitHub (Feb 7, 2017):
Some problem, when i am use $paginator->setUseOutputWalkers(false); arrays instead objects returns
Please think about how to fix it
@Sohorev commented on GitHub (Feb 7, 2017):
I have solve my problem with "partial" syntax for query
partial user.{id,name}@ghost commented on GitHub (Feb 23, 2018):
For me solution was:
$paginator->setUseOutputWalkers(false);