How does Doctrine2 One-To-Many fetch=EAGER work?

Question

I'm using Symfony 2.8 / Doctrine ORM 2.5.2.

I have 2 entities, Gallery OneToMany File

class Gallery
{
    /**
     * @var File[]
     *
     * @ORM\OneToMany(targetEntity="File", mappedBy="gallery", fetch="EAGER")
     */
    private $files;
}

I see 2 things in the documentation.

First, now the OneToMany relationship does have the fetch=EAGER option (specified here). It was not there in previous versions.

Second, the manual setting for this fetch method per query seems not available for OneToMany but I don't know if the documentation is up-to-date as it states:

Changing the fetch mode during a query is only possible for one-to-one and many-to-one relations.

I have anyway tried both, here is my query:

public function findWithEager()
{
    $qb = $this->createQueryBuilder('g');

    $query = $qb->getQuery();
    $query->setFetchMode("CommonBundle\\Entity\\Gallery", "files", ClassMetadata::FETCH_EAGER);

    return $query->getResult();
}

But when I do:

foreach ($galleryRepository->findWithEager() as $gallery) {
    foreach ($gallery->getFiles() as $file) {
        $file->getId();
    }
}

Then I got 1+n queries. The first is SELECT * FROM Gallery and the n following ones are SELECT * FROM File WHERE id = :galleryId

I would like Doctrine to do 1+1 queries, the second one being SELECT * FROM File WHERE id IN (:galleryListIds)

Did I miss something? Is this behavior implemented in Doctrine?

The latest doctrine changelog states:

When marking a one-to-many association with fetch="EAGER" it will now execute one query less than before and work correctly in combination with indexBy.

It is not clear at all what is the expected behavior.

Any insight is welcome, thanks!

Answer 1

After much searching and some testing (using Doctrine ORM 2.5.6 on PHP 5.6) I have some results.

At this time it is not possible to get your Gallery entities with one query and all related File entities with a second query.

You have two options

Get Gallery and File entities in one query using Left Join.

• This is what the ->find* methods do when you set fetch="EAGER" on your annotation.
• You can do this manually with DQL: SELECT g, f FROM Gallery g LEFT JOIN g.files f

As noted in the docs, you cannot call ->setFetchMode('Gallery', 'files', ClassMetadata::FETCH_EAGER) on a DQL query to achieve the same result

... For one-to-many relations, changing the fetch mode to eager will cause to execute one query for every root entity loaded. This gives no improvement over the lazy fetch mode which will also initialize the associations on a one-by-one basis once they are accessed.

This will result in n additional queries being run immediately after your first query to fetch your Gallery entities.

Get Gallery entities with one query and lazy-load the File entities.

• This is Doctrine's default behaviour (fetch="LAZY") and will result in 1 query plus an additional query for each set of Gallery#$files you access (1+n queries if you access them all).

Possible Future Option

There is a PR to add an EAGER_BATCHED fetch option which would do exactly what you want (fetch Gallery entities with one query then fetch all File entities with a second query) but there doesn't seem to be much happening with it, unfortunately.

Conclusion

If you're using the ->find* methods, fetch="EAGER" annotations will be respected. Doctrine will do this with a LEFT JOIN. Depending on your data set this could be ok or very expensive.

If you're writing DQL manually or using the query builder you must LEFT JOIN one-to-many relations AND remember to add any entities you want fetched to the select clause.

My preference is to write DQL whenever you want a one-to-many relation fetched eagerly because it makes your intention clear.