开发者

Exclude rows with Doctrine ORM DQL (NOT IN)

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 adm开发者_如何学运维in 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();


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


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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜