CakePHP Retrieving data from habtm

Question

I haven't been able to find a solution that solves this. I am trying to query my database for a list of groups to which a user belongs. Users can be in multiple groups, and groups can have multiple users.

I have "users", "groups", and "groups_users" tables. Each has an "id" field, and the "groups_users" table has two other fields, "group_id" and "user_id".

I think all the tables are properly formed.

My User.php, and Group.php model files look like this

//app/Model/User.php
class User extends AppModel {
    public $recursive = 1;
    public @hasAndBelongsToMany = array(
        'Group' => array(
            'className' => 'Group',
            'joinTable' => 'groups_users',
            'foreignKey' => 'user_id',
            'associationForeignKey' => 'group_id'
        )
    );
}

//app/Model/Group.php
class Group extends AppModel {
    public $recursive = 1;
    public @hasAndBelongsToMany = array(
        'User' => array(
            'className' => 'User',
            'joinTable' => 'groups_users',
            'foreignKey' => 'group_id',
            'associationForeignKey' => 'user_id'
        )
    );
}

Then, in my Controller file I have this

//app/Controller/ProjectController.php
class ProjectController extends AppController {
    public $uses = array('Groups', 'Users');
    ...
    $this->set('groupsForUser', $this->Groups->find('all', array(
        'conditions' => array('Users.UserName' => 'testuser1')
    ));
}

Every time I try to display the data, I get the error message Error: ..... Unknown column 'Users.UserName' in 'where clause'

And it shows me the SQL it's trying to run: SELECT Groups.id, ... FROM accounts.groups AS Groups WHERE Users.UserName = 'testuser1'

Obviously the association between the tables isn't happening properly and the SQL query it is sending in isn't joined properly, but I can't figure out what's going wrong here. I've tried varieties like

.....$this->Groups->Users->find(....

And stuff like that, but nothing seems to work.

Help!

Answer 1

I think You should use "User.UserName" instead of "Users.UserName" in your ProjectController.php because you have "User" in your associations So model name should be same.

//app/Controller/ProjectController.php
class ProjectController extends AppController {
    public $uses = array('Groups', 'User');
    ...
    $this->set('groupsForUser', $this->Groups->find('all', array(
        'conditions' => array('User.UserName' => 'testuser1')
    ));
}

Answer 2

I had to take a completely different approach to the problem. It's mostly "Cake-y".

I simplified the models a bit by taking out the $recursive bit and trimmed down the HABTM part, removing the joinTable, foreignKey, and associationForeignKey bits)

The ProjectController got the code:

public $uses = array('Group', 'User');
$arrayGroupsForUser = array();
....
$results = $this->User->Find('first', array(
    'conditions' => array('User.UserName' => 'testuser1')
));

foreach($results['Group'] as $result) {
    array_push($arrayGroupsForUser, $result['name']);
}
$this->set('groupsForUser', $arrayGroupsForUser);

(Thank you ned stark for noting the need for a singular "User". I can't upvote your answer with my limited reputation yet.)

The "$this->set(.....);" line is to pass just the array of group names to the View to use.

This link http://rottmann.net/2012/07/cakephp-hasandbelongstomany-relationship-queries-demystified/ was a big help in helping me solve this.