HABTM selection seemingly ignores joinTable

Question

UPDATE #2 -- SOLUTION FOUND: Turns out my use of this lookup:

$this->User->Group->find(....)

was not what I needed. To pull out a user's groups I needed to use:

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

< /UPDATE #2>< PROBLEM>

I'm attempting to do a HABTM relationship between a Users table and Groups table. The problem is, that I when I issue this call:

$this->User->Group->find('list');

The query that is issued is:

SELECT [Group].[id] AS [Group__id], [Group].[name] AS [Group__name] FROM [groups] AS [Group] WHERE 1 = 1

I can only assume at this point that I have defined my relationship wrong as I would expect behavior to use the groups_users table that is defined on the database as per convention. My relationships:

class User extends AppModel {
        var $name = 'User';
        //...snip...
    var $hasAndBelongsToMany = array(
        'Group' => array(
            'className'             => 'Group',
            'foreignKey'            => 'user_id',
            'associationForeignKey' => 'group_id',
            'joinTable'             => 'groups_users',
            'unique'                => true,
        )
    );
        //...snip...
}

class Group extends AppModel {
    var $name = 'Group';
    var $hasAndBelongsToMany = array ( 'User' => array(
        'className'             => 'User',
        'foreignKey'            => 'group_id',
        'associationForeignKey' => 'user_id',
        'joinTable'             => 'groups_users',
        'unique'                => true,
    ));
}

Is my understanding of HABTM wrong? How would I implement this Many to Many relationship where I can use CakePHP to query the groups_users table such that a list of groups the currently authenticated user is associated with is returned?

UPDATE

After applying the change suggested by ndm I still receive a large array return (Too big to post) which returns all groups and then a 'User' element if the user has membership to that group. I looked at the query CakePHP uses again:

SELECT 
    [User].[id] AS [User__id], 
    [User].[username] AS [User__username], 
    [User].[password] AS [User__password], 
    [User].[email] AS [User__email], CONVERT(VARCHAR(20), 
    [User].[created], 20) AS [User__created], CONVERT(VARCHAR(20), 
    [User].[modified], 20) AS [User__modified], 
    [User].[full_name] AS [User__full_name], 
    [User].[site] AS [User__site], 
    [GroupsUser].[user_id] AS [GroupsUser__user_id], 
    [GroupsUser].[group_id] AS  [GroupsUser__group_id], 
    [GroupsUser].[id] AS [GroupsUser__id] 
FROM 
    [users] AS [User] JOIN 
        [groups_users] AS [GroupsUser] ON (
        [GroupsUser].[group_id] IN (1, 2, 3, 4, 5) AND 
        [GroupsUser].[user_id] = [User].[id]
    )

Is there an easy way to refine that such that I only receive the group ids & names for the entries I have membership to? I was thinking of using:

array('conditions'=>array('GroupsUser.user_id'=>$user_id))

...but I receive an sql error on the groups table:

SELECT TOP 1 [Group].[name] AS [Group__name], CONVERT(VARCHAR(20), [Group].[created], 20) AS [Group__created], CONVERT(VARCHAR(20), [Group].[modified], 20) AS [Group__modified], [Group].[id] AS [Group__id] FROM [groups] AS [Group] WHERE [GroupsUser].[user_id] = 36 ORDER BY (SELECT NULL)

Just FYI, the only 2 lines of code you need (one in each model) are: `public $hasAndBelongsToMany = array('User');` and `public $hasAndBelongsToMany = array('Group');` All others are redundant, as they are already the default values. — Dave, Nov 06 '12 at 03:21

score 1 · Accepted Answer · answered Nov 05 '12 at 22:56

1

I think you just misunderstood what the list find type is ment to do.

The query is totally fine, the list find type is used for retreiving a list of records of a single model only, where the models primary key is used as index, and the display field as value.

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#find-list

answered Nov 05 '12 at 22:56

ndm

59,784
9
71
110

Ah sorry. `'list'` was only the latest version as I was begining to attempt a brute-force solution before asking this question. Using the `'all'` param I get a massive list that again contains all the available groups but only nests a `'User'` portion to the ones my user has membership for. Is this normal? Can I refine the search somehow? Using `fields` or `conditions` seems to break the query giving errors on my usage of `array(conditions=>array('User.username'=>$user_id))` – Grambot Nov 06 '12 at 13:56
Correction. I think I've fixed it. I changed my `find` to: `$this->User->find('all',array('conditions'=>array('User.id'=>$user_id)));` The additional $this->user->`group` caused that lookup issue. Thank you! – Grambot Nov 06 '12 at 14:23

HABTM selection seemingly ignores joinTable

1 Answers1