CODEWITHSUNDEEP
phpcakephphas-and-belongs-to-many
carlmango11
carlmango11

Reputation: 525

Query on Multiple Associations with CakePHP Models

I'm very new to CakePHP (started last night) and I'm having an issue with queries.

The way my model works is this:

Classgroups << hasAndBelongsTo >> Users << hasAndBelongsTo >> PermissionsGroups

Basically a Classgroup is made up of a load of users and each user belongs to a permission group. The group defines what type of user they are as well as their permissions.

Now I need to make a query where I get all Users that belong to a certain PermissionGroup but I'm doing it from the ClassgroupsController.

However whenever I try to do this:

$this->Classgroup->User->find('list', array(
            'fields' => array('User.surname_firstname'),
            'order' => array(
                    'User.surname_firstname'
                ),
            'conditions' => array(
                    'PermissionGroup.permissions' => '10'
                )
        )

I get this error:

Column not found: 1054 Unknown column 'PermissionGroup.permissions' in 'where clause'

Because I'm in the ClassgroupController it's as if the Group fields can't be seen but I would have assumed because User is associated with PermissionGroup it should be ok. I have set up the associations between the 3 models but the problem seems to be that the query isn't looking "deep" enough into the model.

I've also tried setting the recursive value on the find to 2,3 and 4. From what I've read I think this probably isn't a great idea and Containable is better but surely it should at least work?

I should also point out that these associations are one way. I've only added them to the Models I needed. So Classgroups has an association to User and User has an association to PermissionGroup

Am I doing something obviously wrong? Or have I misunderstood a CakePHP concept? Thanks

UPDATE: For the second time I've seen someone say I should use joins, like so:

'joins'=>array(
    array(
         'table'=>'rooms',
         'alias'=>'Room',
         'type'=>'inner',
         'conditions'=>array(
              'Room.hotel_id'=>'Hotel.id'
         )
    )

But I have the feeling that's an outdated way to do it... I've also tried using contain like so:

'contain' => array(
                'Group' => array(
                    'conditions' => array(
                        'Group.name' => '10'
                    )
                )
            )

But it didn't work, I still got all of the Users.

Upvotes: 0

Views: 335

Answers (1)

Barry Chapman
Barry Chapman

Reputation: 6780

You were on the right track with joins:

$joins = array(
    array(
     'table'=>'permission_groups',
     'alias'=>'PemissionGroup',
     'type'=>'inner',
     'conditions'=>array(
          'User.id = PermissionGroup.user_id',
          'PermissionGroup.permissions = 10'
     )
 )


    $this->Classgroup->User->find('list', array(
        'fields' => array('User.surname_firstname'),
        'joins' => $joins,
        'order' => array(
                'User.surname_firstname'
            )
    )

Try to work with that!

Upvotes: 1

Related Questions