Exclude rows with Doctrine ORM DQL (NOT IN)
- by Sheriffen
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();
