Exclude rows with Doctrine ORM DQL (NOT IN)

Question

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

Answer 1

To do this you need to JOIN the tables first

Use an inner join to join user_available.id to user.id

However, I think you may be using the user_available table incorrectly. As it is matched 1 to 1 with the user table, can't you just have an is_available field in your user table?

Answer 2

I realise the problem has probably gone away by now, but this is how I do it for future readers:

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