What is the correct way to get an entity's children's children in Symfony?

Question

I'm using Symfony4. I have a database structure with three tables, with two one-to-many relations relating them. For instance:

House -> Person -> Dog

Each house can contain many persons, each person can own many dogs. (My database isn't really about houses and dogs, the actual table names have been changed to protect the innocent.) Using Doctrine entities, I of course have functions like $house->getPersons() and $person->getDogs(). But what is the correct way to implement a function like $house->getDogs()? There are several solutions I can come up with, but none of them seem like a "good" solution to me:

• I could simply add a one-to-many relation relating House to Dog. However, this goes against one of the fundamental rules of normal database structure: no redundant data. The relation house->person->dog is already expressed in the database, so adding another relation directly from house->dog is not only redundant, but could potentially lead to a situation where a dog's person lives in one house, but the dog lives in another (which is not something I want).

• I could just do something like

  foreach($house->getPersons() as $person){
      $person->getDogs();
      // Do something with each dog
      ...
  }
  

  This isn't well optimized, as I'd be running a separate query for each person - potentially a lot of queries - when I could easily be running one query with a couple joins.

• I could use the HouseRepository or a query builder within the House entity to run a custom query. This, as I understand it, is frowned upon in Symfony. We generally don't want any DQL or use of repositories in the entity classes, correct?

• I could use the HouseRepository from within services/controllers to run a custom query. This would be a simple way, but feels a little inelegant. Nevertheless, I have a feeling this might be the "right" way.

To sum up: I have a feeling I should be able to somehow put this fairly simple double-join in my entity, and not have to go to the repository level to get this data. To be clear, I'm not asking how to write a DQL join, I'm asking where the right place to put it is, or if there's some clever Symfony way to do this using Criteria or something similar.

Answer 1

Iterating through each person in the way you've done it is perfectly valid. If you are looking to minimize the hits to the database, you can use some join statements to "pre select" everything you need all in one trip to the db. Using a technique like the one shown here, try the code below.

Assuming one to many relationship dog->persons and a second one to many relationship person->dogs, then in your HouseRepository use a method that pulls all relevant data in a single query, including all related Person(s) and Dog(s).

class HouseRepository extends ServiceEntityRepository
{
    $qb = $this->createQueryBuilder('house')
        ->leftJoin('house.persons', 'persons')
        ->addSelect('persons')
        ->leftJoin('persons.dogs', 'dogs')
        ->addSelect('dogs')
        ->getQuery()->getResult();
}

This is untested of course, and I suspect the returned data structure will be so messy that even if it works your code might be ugly to handle it.

Answer 2

If you want call $house->getDogs() you could add a method in your House entity.

public function getDogs()
{
    /** @var Doctrine\Common\Collections\ArrayCollection */
    $dogs = new ArrayCollection();

    if ($persons = $this->getPersons()) {
        foreach($persons as $person) {
            if ($d = $person->getDogs()) {
                $dogs->add($d);
            }
        }
    }

    return $dogs;
}