CODEWITHSUNDEEP
phpcakephpcakephp-1.3php-5.3
user765368
user765368

Reputation: 20346

cakephp find query conditions on associated model

I have a model User and a model Role in a CakePHP application. The association between the two models is the following:

User $belongsTo Role
Role $hasMany User

I want to make a query on the User model to find all users with a specific role (let's say the role Supervisor). I did my query like this:

$supervisors = $this->User->find('all', array(
    'contain' => array(
        'Role' => array(
            'conditions' => array(
                'Role.name' => 'Supervisor'
            )
        )
    )
));

But the above query returns me all the users in my users table. It does not return only the users with role Supervisor. I know that if I do two queries, one on the Role model to find the id of the role type 'Supervisor' and then do another query on the User model and pass the id of the supervisor role record in the conditions on my User model like this:

$supervisor_role_id = $this->Role->field('id', array('Role.name' => 'Supervisor'));

$supervisors = $this->User->find('all', array(
    'conditions' => array(
        'User.role_id' => $supervisor_role_id
    )
));

The above queries will give me the desired result. But I don't wanna do 2 queries to do this. Why doesn't the first approach work. Any idea please?

Thank you

Upvotes: 3

Views: 8913

Answers (1)

Dave
Dave

Reputation: 29121

The reason your attempt didn't work

CakePHP's Containble Behavior creates separate queries for each model. So - what you did was basically described like this: "Find all Users. Also find any Roles with the name of 'Supervisor". As you can see, there is no condition that crosses between the two.

So, you can do one of the following:

1) [easy way] Query the other way around

Query from the Role model and contain it's user(s). This pulls the role you want (based on your provided conditions) then contains any/all of it's users.

Note - if you've already loaded the 'User' model (or it's been loaded by default because you're in the UsersController), you can run your find like this: $this->User->Role->find(..... - so you don't have to load the Role model separately.

2) Use JOINs (see CakePHP Book on Joining Tables)

This allows you to limit the result of a parent model based on it's associated data.

Upvotes: 6

Related Questions