mirror of
https://github.com/doctrine/orm.git
synced 2026-03-24 06:52:09 +01:00
DDC-3818: Paginator fails to retrieve entities linked with One-To-Many #4677
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 10, 2015).
Originally assigned to: @Ocramius on GitHub.
Jira issue originally created by user odoucet:
This happens when you deal with entities with children as One-To-Many, and you perform a search on one of the child.
Let's use a real example :
a Company has 1,N Employees.
Data is the following :
Company
||ID || COMPANY NAME||
|1 | ACME LTD|
|2 | PEACH SOFTWARE|
Employee
||ID || Name || Employee company ID||
| 1 | Basil | 1|
| 2 | Robert | 1|
| 3 | Donald | 1|
| 4 | Daniel | 2|
| 5 | Tom | 2|
Following DQL query will work and return two companies with property 'employees' hidrated :
Now let's add a WHERE clause :
We will get Company #1, but only 1 employee on it (Robert) instead of three.
Explanation
This is how Paginator works :
Problem is in query 3 : WHERE clause is copied and IN() is added. To work properly, we should reset the WHERE clause and only use IN().
This bug went unseen because Doctrine is using massive cache on Entities based on @id. Tests did not show this bug because the full object is retrieved from the cache version, where all descendants are present.
Solution
in ORM\Tools\Pagination\WhereInWalker.php, reset $AST->whereClause.
I realize just now that this would break applications using WhereInWalker class directly. I wondered if the proper fix would be to duplicate this class as PaginationWhereInWalker and use this last class in Paginator.
Another problem happens with prepared statements (that we should all use with Doctrine) : as we remove some WHERE clauses, we have more parameters than what's bind. Latest pull request contains a fix to not copy parameters in WhereInQuery, and only add what's needed.
This is when I work on such a bug that I understand how complex Doctrine is ... I hope I did not open the pandora's box. If my fix is not implemented properly, I'll will be happy to discuss it.
Full pull Request, containg test and fixes : http://www.doctrine-project.org/jira/browse/DDC-3819
@ostrolucky commented on GitHub (Aug 4, 2018):
Fix available in https://github.com/doctrine/doctrine2/pull/1454
@ostrolucky commented on GitHub (Aug 4, 2018):
I don't think this is concern of paginator. Problem is in original query. If user did not use Paginator and ran your query
SELECT c, e FROM Company c LEFT JOIN c.employees e WHERE e.name = 'Robert', Doctrine wouldn't fetch employees without specified name too. Therefore I think this falls outside the scope of paginator and I suggest to close. What do you think @Ocramius ?@Ocramius commented on GitHub (Aug 5, 2018):
The DQL query is indeed broken due to the incorrect filtering over a fetch join