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

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.

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.

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