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: A Proposal, A Vote, related to a proposal, a user, and an opinion (up

Solution 1:

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

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

classVote{
    /**
     * @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;
}

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

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

classProposal{
    publicfunctiongetCountOpinion($id) {
        $count = 0;
        foreach ($this->getVotes() as$vote) {
            if ($vote->getOpinion()->getId() === $id) {
                $count++;
            }
        }
        return$count;
    }

    publicfunctiongetUserVote($user) {
        foreach ($this->getVotes() as$vote) {
            if ($vote->getUser() == $user) {
                return$vote;
            }
        }
        returnnull;
    }
}

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)