Jira issue originally created by user muqker:
Hi! Let me start by saying you guys did a great job with Doctrine 1 and that I can't wait to start using Doctrine2 :)
I will explain this feature request with an example. I have a User entity wich relates one to many to a Picture entity. Picture has a " is main picture" boolean field. Not all users have a main picture. I would like to be able to select all Users, each with their main picutre, if that exists, or some Null value, if it does not exists, in one query, using join. I would also like for the result collection to contain Picture entities on the first level, with the User beinng accessible as an aggregate of Picture.
The way I can think doing this is by using a RIGHT or LEFT join (not INNER) as to also select Users that don't have a main picture. I can do this by selecting
SELECT Picture p, p.User u FROM p RIGHT JOIN u WITH p.main=1
but right joins afik are not available atm in either version of Doctrine, or by selecting
SELECT User u, u.Picture p FROM u LEFT JOIN p WITH p.main=1
and somehow instructing the hydrator to consider Picture as the root object for the generated object tree and User as a "child" of Picture.
For users without a picture, the Picture object would somehow indicate it is NULL, while still holding a refference to the User.
Makes sense? :) If there is an alternate way to achieve this, please enlighten me, tough I think it would still add felxibility if we could hint the hydrator for the root object in a tree.
Comment created by @beberlei:
Why don't you model that as ManyToOne for the Main Picture and OneToMany for all pictures? Makes much more sense from an ORM perspsective, you would have your own property "User::$mainPicture"
Comment created by muqker:
Thanks for the suggestion. However, this was just an example to demonstrate some lack of flexibility, I am not strictly looking for a solution to this example, but to the concept behind it.
Also, how would I get the result with Picture on the top level and User aggregated to Picture with the model you suggested? Unless I am missing something, wouldn't I end up in the same situation?
I can post-process the results myself and create a new collection easily, ofc, but it would be better (and more optimal) if I could tell the hydrator to do this, similar to how INDEXBY is passed as an option to the hydrator.