doctrine2 loads one-to-many associations with fetch mode eager using too many SQL queries

Question

I am loading a list of many entities.
These entities have a one-to-many association to other entities.
I want to load all these other entities in one single SQL query (instead of one query for every entity in the first list).

As discribed in the doctrine2 documentation: http://www.doctrine-project.org/docs/orm/2.1/en/reference/dql-doctrine-query-language.html#temporarily-change-fetch-mode-in-dql this should be possible with "EAGER" loading.

but it does not work as described.

my code:

class User{
    /**
     * @ORM\OneToMany(targetEntity="Address", mappedBy="user", indexBy="id", fetch="EAGER")
     */
    protected $addresses;
    public function __construct(){
        $this->addresses = new ArrayCollection();
    }
}

class Address{
    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="addresses")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="UserId", referencedColumnName="id")
     * })
     */
    private $user;
}

class UserRepository{
    public function findUsersWithAddresses(){
        return $this->getEntityManager()
            ->createQuery('SELECT u FROM MyBundle:User u ORDER BY u.name ASC')
            ->setFetchMode('MyBundle\Entity\User', 'addresses', \Doctrine\ORM\Mapping\ClassMetadata::FETCH_EAGER)
            ->setMaxResults(10)
            ->getResult();
    }
}

The method UserRepository::findUsersWithAddresses() executes 11 SQL Queries.

How can I tell Doctrine to use only one SQL Query to load the address entities?

I am using:

• symfony v2.0.9
• doctrine-common 2.1.4
• doctrine-dbal 2.1.5
• doctrine 2.1.5

Impudent

Answer 1

The current version of doctrine doesn't support this.

There is a feature request about this in the doctrine2 issue tracker.

So I hope it will be implemented soon.

Impudent

What was 'current version' at time of writing this answer...? I think it should be possible to do this right now right? With a LEFT JOIN on addresses in your DQL. – Commence

This has now been implemented in Doctrine 2.5 – Buckboard

Answer 2

According to your link:

You can mark a many-to-one or one-to-one association as fetched temporarily to batch fetch these entities using a WHERE .. IN query

It looks like the current version of Doctrine does not support eager loading on a one-to-many collection, unfortunately.

This page seems to confirm this supposition:

@OneToMany

Required attributes:

targetEntity: FQCN of the referenced target entity. Can be the unqualified class name if both classes are in the same namespace. IMPORTANT: No leading backslash!

Optional attributes:

• cascade: Cascade Option
• orphanRemoval: Boolean that specifies if orphans, inverse OneToOne entities that are not connected to any owning instance, should be removed by Doctrine. Defaults to false.
• mappedBy: This option specifies the property name on the targetEntity that is the owning side of this relation. Its a required attribute for the inverse side of a relationship.

The @OneToMany annotation does not feature a fetch attribute, as opposed to @OneToOne and @ManyToOne.

---

Update

I just noticed that you can actually eager fetch the related entities, using an explicit LEFT JOIN in DQL:

SELECT u, a FROM User u
LEFT JOIN u.addresses a

Do use a LEFT JOIN, and not an inner JOIN, or entities with an empty collection (User without any Address) would be omitted from the result set.

Update (2017)

As pointed by GusDeCool and webDEVILopers in the comments, the fetch attribute is now supported on @OneToMany. The above answer is now obsolete.

Coat

I think Doctrine does support eager loading on a one-to-many collection, otherwise my method UserRepository::findUsersWithAddresses() would only execute 1 SQL Query instead of 11. When i remove the fetch="EAGER" from the User entity and the ->setFetchMode() from the UserRepository, my method UserRepository::findUsersWithAddresses() only executes 1 SQL Query – Impudent

I can not use a LEFT JOIN for eager loading, because ->setMaxResults(10) does not work correctly using LEFT JOINs for one-to-many collections – Impudent

on the latest one we can do it. doctrine 2.5. updated docs: doctrine-orm.readthedocs.org/en/latest/reference/… – Mushy

Indeed it works with Doctrine 2.5 and now executes one query less: doctrine-orm.readthedocs.org/en/latest/changelog/… – Quitt

Answer 3

I had exactly the same issues and updated my doctrine module in Zend Framework 2 to version 2.5 and now all is working fine. You can check my question here

Commence

Hi Wilt, can you advise on which version of doctrine ORM you are using, and which queries doctrine generates for you in that particular case? I am trying to do the same but cannot manage so far... – Rhoades

The TS asked about OneToMany not ManyToOne as mentioned in your linked question. Anyway eager loading seems to be supported even for OneToMany relations now. – Umbrageous