title: How to join multiple entities on a foreign ID in Symfony 4 using a query builder?
body: <p>I'm trying to learn Symfony. Today I was following <a href="https://symfony.com/doc/current/doctrine/associations.html" rel="nofollow noreferrer">The associations tutorial.</a> I decided to make a small application that a House, Kitchens, Bedrooms, and cabinets. I (tried to ;-) ) make a small Class diagram using draw.io to give you a better idea.</p>
  
  <p><a href="https://i.stack.imgur.com/XaLFy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XaLFy.jpg" alt="This is it"></a>  </p>
  
  <p>So basically a House can have multiple Bedrooms and multiple Kitchens. Each kitchen can have multiple cabinets. The House has an id and a name. The Bedroom and Kitchen as well. The cabinet has id, shopUrl and is also linked via a foreign key (<code>account_id</code>) to its parent Kitchen. 
  I also link the Kitchen and the Bedroom to the House using a foreign key (<code>house_id</code>). So I followed the tutorial and created the <code>House</code> entity:</p>
  
  <pre class="lang-php prettyprint-override"><code>    &lt;?php
  
      namespace App\Entity;
  
      use Doctrine\ORM\Mapping as ORM;
      use Doctrine\Common\Collections\ArrayCollection;
      use Doctrine\Common\Collections\Collection;
  
      /**
       * @ORM\Entity(repositoryClass="App\Repository\HouseRepository")
       */
      class House implements \JsonSerializable
      {
  
          /**
           * @ORM\OneToMany(targetEntity="App\Entity\Kitchen", mappedBy="house")
           */
          private $kitchen;
  
          /**
           * @ORM\OneToMany(targetEntity="App\Entity\Bedroom", mappedBy="house")
           */
          private $bedroom;    
  
          /**
           * House constructor
           */
          public function __construct()
          {
              $this-&gt;kitchen = new ArrayCollection();
              $this-&gt;bedroom = new ArrayCollection();
          }
  
          /**
           * @return Collection|Kitchen[]
           */
          public function getKitchen(): Collection
          {
              return $this-&gt;kitchen;
          }
  
          /**
           * @return Collection|Bedroom[]
           */
          public function getBedroom(): Collection
          {
              return $this-&gt;bedroom;
          }
  
  
          /**
           * @ORM\Id()
           * @ORM\GeneratedValue()
           * @ORM\Column(type="integer")
           */
          private $id;
  
          /**
           * @ORM\Column(type="string", length=255)
           */
          private $name;
  
          public function getId(): ?int
          {
              return $this-&gt;id;
          }
  
          public function getName(): ?string
          {
              return $this-&gt;name;
          }
  
          public function setName(string $name): self
          {
              $this-&gt;name = $name;
  
              return $this;
          }
  
          public function jsonSerialize()
          {
              return get_object_vars($this);
          }
      }
  </code></pre>
  
  <p>The House repository is empty (a.k.a: only containts the automatically generated code from Symfony): </p>
  
  <pre class="lang-php prettyprint-override"><code>    &lt;?php
  
      namespace App\Repository;
  
      use App\Entity\House;
      use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
      use Symfony\Bridge\Doctrine\RegistryInterface;
  
      /**
       * @method House|null find($id, $lockMode = null, $lockVersion = null)
       * @method House|null findOneBy(array $criteria, array $orderBy = null)
       * @method House[]    findAll()
       * @method House[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
       */
      class HouseRepository extends ServiceEntityRepository
      {
          public function __construct(RegistryInterface $registry)
          {
              parent::__construct($registry, House::class);
          }
  
      }
  </code></pre>
  
  <p>The <code>Bedroom</code> entity is this: </p>
  
  <pre class="lang-php prettyprint-override"><code>    &lt;?php
  
      namespace App\Entity;
  
      use Doctrine\ORM\Mapping as ORM;
  
      /**
       * @ORM\Entity(repositoryClass="App\Repository\BedroomRepository")
       */
      class Bedroom implements \JsonSerializable
      {
  
          /**
           * @ORM\ManyToOne(targetEntity="App\Entity\House", inversedBy="bedroom")
           */
          private $house;
  
          public function getHouse(): House
          {
              return $this-&gt;house;
          }
  
          public function setHouse(House $house): self
          {
              $this-&gt;house = $house;
  
              return $this;
          }
  
  
          /**
           * @ORM\Id()
           * @ORM\GeneratedValue()
           * @ORM\Column(type="integer")
           */
          private $id;
  
          /**
           * @ORM\Column(type="string", length=255)
           */
          private $name;
  
          public function getId(): ?int
          {
              return $this-&gt;id;
          }
  
          public function getName(): ?string
          {
              return $this-&gt;name;
          }
  
          public function setName(string $name): self
          {
              $this-&gt;name = $name;
  
              return $this;
          }
  
          public function jsonSerialize()
          {
              return get_object_vars($this);
          }
      }
  </code></pre>
  
  <p>and the Bedroom repository is also empty.</p>
  
  <p>The <code>Kitchen</code> entity has the following code:</p>
  
  <pre class="lang-php prettyprint-override"><code>    &lt;?php
  
      namespace App\Entity;
  
      use Doctrine\ORM\Mapping as ORM;
  
      /**
       * @ORM\Entity(repositoryClass="App\Repository\KitchenRepository")
       */
      class Kitchen implements \JsonSerializable
      {
  
  
          /**
           * @ORM\OneToMany(targetEntity="App\Entity\Cabinet", mappedBy="kitchen")
           */
          private $cabinet;
  
          /**
           * Kitchen constructor
           */
           public function __construct()
          {
              $this-&gt;cabinet= new ArrayCollection();
          }
  
          /**
           * @return Collection|Cabinet[]
           */
          public function getCabinet(): Collection
          {
              return $this-&gt;cabinet;
          }
  
  
          /**
           * @ORM\ManyToOne(targetEntity="App\Entity\House", inversedBy="kitchen")
           */
          private $house;
  
          public function getHouse(): House
          {
              return $this-&gt;house;
          }
  
          public function setHouse(House $house): self
          {
              $this-&gt;house = $house;
  
              return $this;
          }
  
          /**
           * @ORM\Id()
           * @ORM\GeneratedValue(strategy="UUID")
           * @ORM\Column(type="integer")
           */
          private $id;
  
          /**
           * @ORM\Column(type="string", length=255)
           */
          private $name;
  
          public function getId(): int
          {
              return $this-&gt;id;
          }
  
          public function getName(): string
          {
              return $this-&gt;name;
          }
  
          public function setName(string $name): self
          {
              $this-&gt;name = $name;
  
              return $this;
          }
  
          public function jsonSerialize()
          {
              return get_object_vars($this);
          }
      }
  </code></pre>
  
  <p>and the Kitchen repository is also empty.
  Finally, the <code>cabinet</code> consists of the following:</p>
  
  <pre class="lang-php prettyprint-override"><code>    &lt;?php
  
      namespace App\Entity;
  
      use DateTime;
      use Doctrine\ORM\Mapping as ORM;
  
      /**
       * @ORM\Entity(repositoryClass="App\Repository\CabinetRepository")
       */
      class Cabinet
      {
          /**
           * @ORM\Id()
           * @ORM\GeneratedValue()
           * @ORM\Column(type="integer")
           */
          private $id;
  
          /**
           * @ORM\Column(type="string", length=255)
           */
          private $shopUrl;
  
          private $account_id;
  
          /**
           * @ORM\ManyToOne(targetEntity="Kitchen", inversedBy="cabinet")
           */
          private $kitchen;
  
          /**
           * Cabinet constructor.
           * @param string $shopUrl
           * @param Kitchen $kitchen
           * @param int $id
           */
          public function __construct(string $shopUrl, Kitchen $kitchen = null, int $id = null)
          {
              $this-&gt;shopUrl = $shopUrl;
              $this-&gt;kitchen = $kitchen;
              $this-&gt;id = $id;
          }
  
          public function setId(int $id): self
          {
              $this-&gt;id = $id;
  
              return $this;
          }
  
          public function getId(): int
          {
              return $this-&gt;id;
          }
  
  
          public function getShopUrl(): string
          {
              return $this-&gt;shopUrl;
          }
  
          public function getKitchen(): Kitchen
          {
              return $this-&gt;kitchen;
          }
  
          public function setKitchen(Kitchen $kitchen): self
          {
              $this-&gt;kitchen = $kitchen;
              $this-&gt;account_id = $kitchen-&gt;getId();
              return $this;
          }
  
          public function setAccount_id(int $account_id): self
          {
              $this-&gt;account_id = $account_id;
  
              return $this;
          }
  
          public function getAccount_id(): int
          {
              return $this-&gt;account_id;
          }
  
      }
  </code></pre>
  
  <p>In contrast to the other entities, the cabinet has some logic (this is where I actually need help). Since Bedroom and Kitchen are associated with a House, I would like to give a Bedroom, then look up all the kitchens associated with the same house as the Bedroom and then return all cabinets that these kitchens have. I know it may seem illogical but I discovered this too late to come up with another concept. My current code doesn't work because I'm not sure whether this is possible and because it's a bit too complex for me to grasp at this moment. But I have this as the content of the cabinet repo:</p>
  
  <pre class="lang-php prettyprint-override"><code>
      &lt;?php
  
      namespace App\Repository;
  
      use App\Entity\Bedroom;
      use App\Entity\House;
      use App\Entity\Cabinet;
      use App\Entity\Kitchen;
      use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
      use Symfony\Bridge\Doctrine\RegistryInterface;
  
      /**
       * @method Cabinet|null find($id, $lockMode = null, $lockVersion = null)
       * @method Cabinet|null findOneBy(array $criteria, array $orderBy = null)
       * @method Cabinet[]    findAll()
       * @method Cabinet[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
       */
      class CabinetRepository extends ServiceEntityRepository
      {
          public function __construct(RegistryInterface $registry)
          {
              parent::__construct($registry, Cabinet::class);
          }
  
          public function findByBedroom(Bedroom $bedroom) //use joins??
          {
              return $this-&gt;createQueryBuilder('cabinet')
                  -&gt;join('cabinet.bedroom', 'bedroom')
                  -&gt;join('cabinet.kitchen', 'kitchen')
                  -&gt;addSelect('cabinet')
                  -&gt;andWhere('cabinet.bedroom = :idBedroom')
                  -&gt;setParameter('idBedroom', $bedroom-&gt;getId())
                  -&gt;orderBy('time', 'ASC')
                  -&gt;getQuery()
                  -&gt;getResult();
          }
      }
  
  </code></pre>
  
  <p>I'm using PHPStorm and no error are showing anywhere but of course, the querybuilder doesn't return anything. How can I fix it? I couldn't find any questions that try to achieve what I'm trying to do.</p>
  
  <p>I added data manually to the database, so there's data in there. And using Sequel Pro I can see the relations. The data (as far as I'm concerned) is fine. The queryBuilder is where the mistakes are.</p>
  
  <p>A demonstrative example with some data:</p>
  
  <p>This is the Bedroom data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>id     name              house_id  
  325    bigBedroomOne     1666   
  815    smallBedroomOne   555   
  902    bigBedroomTwo     1666
  </code></pre>
  
  <p>This is the House data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>id     name          
  1666   bigHouse      
  555    smallHouse      
  </code></pre>
  
  <p>This is the Kitchen data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>id   name              house_id
  1    bigKitchen        1666
  2    smallKitchen      555
  55   mediumKitchen     555
  </code></pre>
  
  <p>And finally, this is the cabinets data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>id  shopUrl    account_id
  1   ur.l       55
  88  co.m       2
  33  ne.t       1
  </code></pre>
  
  <p>So in this example I would like to plug in the Bedroom id <code>815</code> which is associated with the house_id <code>555</code>. Then from there all the Kitchen associated with that house_id, should be selected, so <code>2</code> and <code>55</code>. Finally, the cabinets with id <code>1</code> and <code>88</code> should be returned.</p>
  
  <p>Edit: When running <code>bin/console doc:sch:val</code> I get this back:</p>
  
  <h2>`Mapping</h2>
  
  <p>[OK] The mapping files are correct.</p>
  
  <h2>Database</h2>
  
  <p>[OK] The database schema is in sync with the mapping files.`</p>