Symfony2 / Doctrine multiple joins returns error

Question

I'm building a system with messages that can be assigned to (one or more) users.

Table: Messages

• id
• text

Table: Assignments

• id
• remark
• status

Table: Assignment_User

• id
• assignment_id
• user_id

Table: Users

• id
• name

Now I want to retrieve all assigned messages for a specific user

    $qb->select('DISTINCT m')
    ->from('MessageBundle:Assignment', 'a')
    ->join('MessageBundle:Message', 'm')
    ->join('MessageBundle:AssignmentUser', 'au')
    ->where('a.message = m')
    ->andWhere('au.assignment = a')
    ->andWhere('a.status = (:assigned)')
    ->setParameter('assigned', 'assigned')
    ->orderBy("mr.createdAt", "desc");

As soon as I add that second JOIN it throws an error... Error: Expected Literal, got 'JOIN'

What would be the correct way to get all assigned messages for user X?

Answer 1

Doctrine should already understand how your tables relate through your entity mappings (if not, you might need to set those up first). Rather than trying to join a whole table and then specifying what the table is joined on (SQL-style), just join, for example, au.assignment as a.

At that point, you can also specify any additional conditions on your join, such as you'd want on a.status.

You might prefer to reorder the joins below depending on what associations you have set up in which directions (I don't know whether your Message has an assignments property, for example).

$qb->select('DISTINCT m')
->from('MessageBundle:AssignmentUser', 'au')
->innerJoin('au.assignment', 'a', 'WITH', 'a.status = (:assigned)')
->innerJoin('au.user', 'u')
->innerJoin('a.message', 'm')
->where('u.id = (:user_id)')
->setParameter('assigned', 'assigned')
->setParameter('user_id', $yourSpecificUserId)
->orderBy("m.createdAt", "desc");