Is there a way to have Doctrine only hydrate a subset of associated entities in a collection through a DQL query condition?

Question

Consider a one-to-many entity relationship between Store and Location entities (a Store has many Locations). Next consider a Store entity with 3 Locations:

Store (id = 1)
   locations
      (id = 1, opened = true)
      (id = 2, opened = true)
      (id = 3, opened = false)

Is there a way in DQL to fetch the Store by id but fetch it in such a way that the locations collection only contains Location entities that have the condition opened = true?

Currently I'm able to fetch the Store entity by id and then filter out the locations I don't want but I'm wondering if there is a way to never fetch the unwanted locations from the DB using a Doctrine DQL query and fetch the Store entity with a partially hydrated list of locations. Thanks.

Answer 1

An easy way would be to fetch all Stores that have an open Location. I didn't check if this has syntax errors:

$query = $em->createQuery("SELECT s FROM Store s JOIN s.location l WHERE l.open = '1');
$stores = $query->getResult();

Then you could invoke getLocations() for each store to get collection of the open Location-s. I assume you have the method getLocations() in your Store class.

You could also define relation as one-to-many bidirectional, and then you could run DQL query, something like this:

$query = $em->createQuery("SELECT l FROM Location l JOIN l.stores s WHERE s.id=[your specific Store ID] AND l.open='1');

That should yield the collection of Location objects.

More examples: https://www.doctrine-project.org/projects/doctrine-orm/en/2.16/reference/dql-doctrine-query-language.html#joins