CODEWITHSUNDEEP
mysqlcakephp
Chris
Chris

Reputation: 545

CakePHP: Filter search on one table based on values from another table

I have a table containing user information, model name is User

User
----
id    full_name (for demo purposes)
1     john doe
2     jane doe
3     john smith
4     jane smith

The User model is linked to another model called Record

Record
------
userid(FK)
1
1
3
3
3

I also have a routine in my Controller that looks up users based on some criteria.

$leads = $this->User->find('list',array(
'fields' => array('User.id','User.full_name'),
'order' => array('User.full_name ASC'),
'conditions'=>array('AND'=>array(
'NOT'=>array('User.deleted_record'=>1),
 array('NOT'=>array('User.username'=>'root',
  array('User.username'=>'testuser')))
        ))
));

This works just fine for returning a dropdown list of users. What I need to do now is further filter the user list based on whether they have one or more records in the Record model. If they don't, they should not appear. 

User
----
id
1
3

I'm guessing this will require an IN clause and a join, but I don't know the "Cake" way to set it up in my code.

Upvotes: 1

Views: 613

Answers (1)

Disorder
Disorder

Reputation: 430

If you have already set up a relationship between the user and record table in the respective models, then you should try this in the UsersController :

$userList = $this->User->Record->find('list', array('group' => array('Record.userid')));

Upvotes: 2

Related Questions