Doctrine, how to add all objects of the relationship to the result of the QueryBuilder and actually show the content of the added objects?

Question

I have the following structure in my database:

Each House has multiple Bedrooms and multiple Kitchens. Each Kitchen has multiple Cabinets.

Right now, I obtain all the cabinets based on a given Bedroom (I know it's weird). So I enter a Bedroom, It looks the House up, gets all the Kitchens associated with that House, then all Cabinets of those Kitchens. This is the code for it:

public function findCabinetsByBedroom(Bedroom $bedroom)
    {
        return $this->createQueryBuilder('cabinet')
            ->join('cabinet.kitchen', 'kitchen')
            ->join('kitchen.house', 'house')
            ->join('house.bedroom', 'bedroom')
            ->select('cabinet')
            ->andWhere('bedroom = : bedroom')
            ->setParameter('bedroom', $bedroom)
            ->getQuery()
            ->getResult(Query::HYDRATE_OBJECT);
    }

I would like to extend my code to contain the Kitchen of each Cabinet and even the House. I managed to get the Kitchen by simply adding ->addSelect('kitchen') to my code. As so:

public function findCabinetsAndTheirKitchenByBedroom(Bedroom $bedroom)
    {
        return $this->createQueryBuilder('cabinet')
            ->join('cabinet.kitchen', 'kitchen')
            ->join('kitchen.house', 'house')
            ->join('house.bedroom', 'bedroom')
            ->select('cabinet')
            ->addSelect('kitchen')
            ->andWhere('bedroom = : bedroom')
            ->setParameter('bedroom', $bedroom)
            ->getQuery()
            ->getResult(Query::HYDRATE_OBJECT);
    }

But trying to add the House information of the Kitchen doesn't work the same way and I guess it has to do with the fact that the Cabinets have no direct relationship with the House. Xdebug shows the following if I use the latest method (aka findCabinetsAndTheirKitchenByBedroom):


▼$cabinet = {array} [2]
  ▼0 = {App\Entity\Cabinet} [4]
    id = 1
    ►time = {DateTime} [3]
    productCategory = "someCat"
    ▼kitchen = {App\Entity\Kitchen} [3]
      ▼house = {Proxies\__CG__\App\Entity\House} [7]
        lazyPropertiesDefaults = {array} [0]
        ►__initializer__ = {Closure} [3]
        ►__cloner__ = {Closure} [3]
        __isInitialized__ = false
        *App\Entity\House*bedroom = null
        *App\Entity\House*id = 555
        *App\Entity\House*name = null
      id = 55
      country = "US"

Opposes to this when I use the first one (aka findCabinetsByBedroom):

▼$cabinet = {array} [2]
  ▼0 = {App\Entity\Cabinet} [4]
    id = 1
    ►time = {DateTime} [3]
    productCategory = "someCat"
    ▼kitchen = {Proxies\__CG__\App\Entity\Kitchen} [7]
      lazyPropertiesDefaults = {array} [0]
      ►__initializer__ = {Closure} [3]
      ►__cloner__ = {Closure} [3]
      __isInitialized__ = false
      *App\Entity\Kitchen*house = null
      *App\Entity\Kitchen*id = 55
      *App\Entity\Kitchen*country = null

So based on these result I concluded that addSelect indeed returned the Kitchen. And yes I checked the data in the database, it's the correct results. But how would I add the House information to the Cabinet?

One more issue, even though Xdebug shows the correct Kitchen info for each Cabinet, they're for some reason not returned when testing with POSTMAN or the browser. I get the following result:

{ "id": 1, "time": { "date": "2019-06-12 11:51:22.000000", "timezone_type": 3, "timezone": "UTC" }, "productCategory": "someCat", "kitchen": {} }

So it's empty for some reason. Any ideas how to display the information inside the Object Kitchen? I thought it had to do with it being a different object than Cabinet, but following this logic the content of time should have been empty as well since it's a DateTime object. So that can't be it but I have no clue why it's returned empty.

On the Annotation of the relations (e.g. `@ORM\OneToMany(..)`), add `fetch="EAGER"` as an option. Have a look here: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/annotations-reference.html#manytoone (and search that page for "fetch" for more examples). Extra info: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/advanced-configuration.html#association-proxies, https://stackoverflow.com/questions/26891658/what-is-the-difference-between-fetch-eager-and-fetch-lazy-in-doctrine/26895601 — rkeet, Jul 30 '19 at 11:25
@rkeet This solved it for me! Could you add your comment as answer so that I can accept it? — , Jul 30 '19 at 11:33

score 0 · Accepted Answer · answered Jul 31 '19 at 06:55

When using Doctrine, associations with other Entity objects are by default loaded "LAZY", see the docs about Extra lazy associations:

With Doctrine 2.1 a feature called Extra Lazy is introduced for associations. Associations are marked as Lazy by default, which means the whole collection object for an association is populated the first time its accessed. [..]

(I will say, documentation on the default fetching settings is very lacking, as this is the only spot (upgrade docs) which I could find where this is stated)

What this means: if you have something like the following Annotation, it means the relations will not be included until they're called, e.g. via a getObject() or getCollection()

/**
 * @var Collection|Bathroom[]
 * @ORM\OneToMany(targetEntity="Entity\Bathroom", mappedBy="house")
 */
private $bathrooms;

In this case, when you get your House object, an inspection of the object during execution (ie. with Xdebug) will show that the $house does have $bathrooms, however, each instance will show along the lines of:

{
    id: 123,
    location: null,
    house: 2,
    faucets: null,
    shower: null,
    __initialized__:false
}

This object's presence shows that Doctrine is aware of a Bathroom being associated with the House (hence the back 'n' forth ID's being set on the bi-directional relation, but no other properties set), however, the __initialized__:false indicated that Doctrine did not fetch the object. Hence: fetch="LAZY".

To get the associations with your get*() action, they must be set to fetch="EAGER", as shown here in the docs.

Whenever you query for an entity that has persistent associations and these associations are mapped as EAGER, they will automatically be loaded together with the entity being queried and is thus immediately available to your application.

To fix your issue:

Mark the associations you wish to return immediately as fetch="EAGER"

Extra:

Have a look at the Annotations Reference, specifically for OneToOne, OneToMany, ManyToOne and ManyToMany. Those references also show the available fetch options available for each association type.

Doctrine, how to add all objects of the relationship to the result of the QueryBuilder and actually show the content of the added objects?

1 Answers1