Exclude rows with Doctrine ORM DQL (NOT IN)

Posted by Sheriffen on Stack Overflow See other posts from Stack Overflow or by Sheriffen
Published on 2010-02-16T17:00:12Z Indexed on 2010/04/19 11:03 UTC
Read the original article Hit count: 344

Filed under:
|
|
|
|

I'm building a chat application with codeigniter and doctrine.

Tables:
- User
- User_roles
- User_available

Relations:
ONE user have MANY roles. ONE user_available have ONE user.

Users available for chatting will be in the user_available table.

Problem:
I need to get all users in in user_available that hasn't got role_id 7.

So I need to express in DQL something like (this is not even SQL, just in words):

SELECT * from user_available WHERE NOT user_available.User.Role.role_id = 7

Really stuck on this one

EDIT: Guess I was unclear. The tables are already mapped and Doctrine does the INNER JOIN job for me. I'm using this code to get the admin that waited the longest but now I need the user:

$admin = Doctrine_Query::create()
        ->select('c.id')
        ->from('Chat_available c')
        ->where('c.User.Roles.role_id = ?', 7)
        ->groupBy('c.id')
        ->orderBy('c.created_at ASC')
        ->fetchOne();

Now I need to get the user that waited the longest but this does NOT work

$admin = Doctrine_Query::create()
        ->select('c.id')
        ->from('Chat_available c')
        ->where('c.User.Roles.role_id != ?', 7)
        ->groupBy('c.id')
        ->orderBy('c.created_at ASC')
        ->fetchOne();

© Stack Overflow or respective owner

Related posts about doctrine

Related posts about php