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?

Rob Hogan · Accepted Answer · 2013-08-01 09:54:56Z

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");

Collectives™ on Stack Overflow

Symfony2 / Doctrine multiple joins returns error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related