CODEWITHSUNDEEP
sqlsymfonydoctrineeager-loading
pierallard
pierallard

Reputation: 3371

Symfony/doctrine: Optimize n+1 queries

Context

Imagine a set of proposals, where every user can vote up or down for a proposal.

So I have 2 models:

Now I want to display all my proposals, including extra information for each proposal:

 Issue

This is really easy to implement, but this implies 4 request per proposal display (called the n+1 query issue). I come from Rails world, where this can easily be resolved using eager loading or some tricky queries. But I can not resolve it with doctrine and need some help!

What I tried

 Use magical fields

The first solution is to make a custom query: select all the proposal, including the number of upvotes, downvotes, has the current user upvoted or downvoted.

The query now returns 4 more columns than the model describe: count_upvotes, count_downvotes, has_user_upvoted and has_user_downvoted.

To be useable with doctrine, I have to add these field to my Proposal Entity.

For me, this is not a good solution, because it implies to always use this query to avoid bastard model, where these fields can be null.

Use a summary

The second solution is to make another object to be used by the view. This object is created with an argument: the current user. This object is created by optimized queries, and contains these methods:

For me, it's really overkilled to be forced to create an object just for a view optimization.

Use extended Entity

This third solution use an unique query to get proposals, their upvotes, downvotes, and check if user has up or downvoted.

It creates a new "extended" entity,

public function __construct(
    Proposal $badgeProposal,
    $upvotesCount,
    $downvotesCount,
    $hasUserUpvoted,
    $hasUserDownvoted
) {

For me, this is the better solution, but for the moment this can not work because I don't know how to simply hydrate a Proposal from a SQL row (but I'm searching hardly).

Your turn

So, what can be other solutions? Is has to be a well-known issue for doctrine developers.

Upvotes: 4

Views: 4270

Answers (1)

kero
kero

Reputation: 10638

Have you already experimented on this or just thought about it?

For me, a simple fetch="EAGER" solved this, resulting in exactly one query. With the following setup

class Proposal {
    /**
     * @ORM\OneToMany(targetEntity="Vote", mappedBy="proposals", fetch="EAGER")
     */
    protected $votes;
}

class Vote {
    /**
     * @ORM\ManyToOne(targetEntity="Proposal", inversedBy="votes")
     * @ORM\JoinColumn(..)
     */
    protected $proposal;
    /**
     * @ORM\ManyToOne(targetEntity="Opinion", inversedBy="votes")
     * @ORM\JoinColumn(..)
     */
    protected $opinion;
    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="votes")
     * @ORM\JoinColumn(..)
     */
    protected $user;
}

class Opinion/User {
    /**
     * @ORM\OneToMany(targetEntity="Vote", mappedBy="proposals")
     */
    protected $votes;
}

Actually, I edited the Proposal entity even more, adding the following methods

class Proposal {
    public function getCountOpinion($id) {
        $count = 0;
        foreach ($this->getVotes() as $vote) {
            if ($vote->getOpinion()->getId() === $id) {
                $count++;
            }
        }
        return $count;
    }

    public function getUserVote($user) {
        foreach ($this->getVotes() as $vote) {
            if ($vote->getUser() == $user) {
                return $vote;
            }
        }
        return null;
    }
}

As I mentioned in the beginning, this still resulted in only one query. However, you see alot of for here. If you have lots of votes for each proposal, you might want to cache the results (eg. iterating over votes only once, getting all the countOpinion and userVote)

PS: Don't forget to add constructors for the classes that have ArrayCollections (OneToMany)

Upvotes: 7

Related Questions