CODEWITHSUNDEEP
cakephp
Divine Propotion
Divine Propotion

Reputation: 172

CakePHP Find with Associated Model Condition

I have 3 tables

Users (id, name, email)

Teams (id, name)

TeamUsers (id, team_id, user_id)

How do I find all the users who are not in team_id = 1?

Upvotes: 0

Views: 46

Answers (1)

AD7six
AD7six

Reputation: 66169

There are many ways. Here's one:

$model = ClassRegistry::init('User');

$query = 'SELECT * FROM team_users where user_id = User.id AND team_id = 1';

$users = $model->find('all', array(
    'conditions' => array(
        "NOT EXISTS ($query)"
    )
));

Which will result in:

SELECT
    *
FROM
    users as User
WHERE
    NOT EXISTS (
        SELECT * FROM team_users WHERE user_id = User.id AND team_id = 1
    )

Upvotes: 3

Related Questions