Limit many-to-many relation result in Symfony2

Question

I have two entities Question and Tag. There is many to many relation between them, and there is an generated 'tag_question' table (with question_id and tag_id columns).

$questions = $tag->getQuestion();

When this is executed, it returns all the result, all the questions with the tag. The method is automatically generated by Doctrine2 in the Tag entity. How to limit the result (to add pagination, etc)?

Answer 1

If performance is not a huge problem for you, then it shouldn't be too much of an issue to use Doctrine to obtain references to all the Question entities, and let PHP do the filtering (instead of letting MySQL do it). Remember that, by default, Doctrine doesn't return complete entities; rather, it returns a proxy object (which can later be more completely hydrated via another query to the database):

class Question
{
    public function getQuestions($offset = 0; $length = null)
    {
        return array_slice( $this->questions, $offset, $length );
    }
}

However, if performance is an issue, you may want to look into creating a custom repository class with methods that execute custom DQL queries or Doctrine's QueryBuilder class:

class TagRepository
{
    public function findSomeByQuestion($question, $offset, $length)
    {
        $qb = $this->createQueryBuilder("tag")
            ->select("tag")
            ->join("tag.questions", "questions")
            ->where("question.id = :questionId")
            ->setParameter("questionId", $question->getId())
            ->setFirstResult($offset)
            ->setMaxResults($length);
        return $qb->getQuery()->getResult();
    }
}