Symfony - Using Outer Joins with Doctrine ORM

Question

I have three entities: User, Answer, and Question.

There is a OneToMany relationship between User and Answer and a ManyToOne relationship between Answer and Question. Basically, a particular user optionally gives answers to a bunch of questions.

What I'm trying to accomplish in the world of ORM is retrieving ALL questions and their associated answers for a particular user. The key part is that a user may not have answered a particular question but I still want to get that question (with a null answer).

My Answer entity has a 'user' field which maps to the User entity which is inverted by an 'answers' field within the User entity. If I use this 'answers' field within the User entity, I only get the question/answer pairs for which the user has actually answered. I do not get questions for which the user has not answered.

Typically, using raw SQL, this would involve a simple "left outer join" between my question and answer table, but I want this to be done using Doctrine's ORM. Any suggestions? I'm pretty new to the world of ORM.

Answer 1

I did it! Here's how:

I created a field in my Question entity that contains all answers, from all users, for that particular question; its mapped with a OneToMany relationship to the Answer entity. And then to make sure we restrict that list to the answers for a particular user I created a custom repository for my Question entity and created the following function:

public function findAllJoinedToAnswer($user)
{
    $query = $this->getEntityManager()
        ->createQuery('
            SELECT q, a
            FROM Bundle:Question q
            LEFT JOIN q.answers a
            WITH a.user = :user'
         )->setParameter('user', $user);

    try{
        return $query->getResult();
    }catch (\Doctrine\ORM\NoResultException $e) {
        return null;
    }
}

Just pass in an instance of the User entity, and voila!