CakePHP Model Query Construction

0
votes

Hi I just started with CakePHP, and am wondering how to do a certain find query.

Basically I have a model called User that has a hasMany relationship to another model called roles. I want to essentially execute an SQL call along the lines of:

SELECT * FROM users WHERE EXISTS ( SELECT * FROM roles WHERE user_id = users.id )

But also get the hasMany related data as well.

But am having trouble figuring out what is the best way to do this in CakePHP, outside of writing the MySQL out directly. Thanks!

2
cakephp

2 Answers

0
votes

in users controller

  $this->User->find('all', array('conditions' => array('User.id' => 'Role.user_id'), 'recursive' => 1));

Specifying recursive 1 will pull all related data as well

0
votes

@Ehtesham: it's better to use Containable To find a user and related role records: 

$this->User->find('first',array(
    'conditions'=>array('User.id'=>$some_id),
    'contain'=>array('Role')
));

"SELECT * FROM users WHERE EXISTS ( SELECT * FROM roles WHERE user_id = users.id )": If I understand you correctly, you want to find all users that have at least 1 role? If so, you should set up counterCache and add one more condition to the find 'User.role_count >'=>0