CakePHP 2.x HABTM find data which have relationship by condition

Question

I have a HABTM association between the Models Sdirectory and User like this:

class Sdirectory extends AppModel {
    public $actsAs = array('Containable');
    public $hasAndBelongsToMany = array(
        'User' =>
            array(
                'className' => 'User',
                'joinTable' => 'sdirectories_users',
                'foreignKey' => 'sdirectory_id',
                'associationForeignKey' => 'user_id',
                'unique' => true,
                'with' => 'SdirectoriesUser'
            )
    );
}

and

class User extends AppModel {
    public $actsAs = array(
        'Acl' => array('type' => 'requester', 'enabled' => false),
        'Containable'
    );
    public $hasAndBelongsToMany = array(
        'Sdirectory' =>
            array(
                'className' => 'Sdirectory',
                'joinTable' => 'sdirectories_users',
                'foreignKey' => 'user_id',
                'associationForeignKey' => 'sdirectory_id',
                'unique' => true,
                'with' => 'SdirectoriesUser'
            )
    );
}

and (just to see, there should be Containable anywhere)

class AppModel extends Model {
    public $recursive = -1;
    public $actsAs = array('Containable');
}

So i have a relational table between both with the database name sdirectories_users. My goal is to get just the Sdirectories, which have a relationship to a User. In SQL it would be:

SELECT * FROM 
    sdirectories, sdirectories_users, users
WHERE
    sdirectories.id = sdirectories_users.sdirectory_id
  AND
    sdirectories_users.user_id = users.id

When i now call $this->Sdirectory->find('all', array('contain' => array('User'))), i get all Sdirectories - and if there is a relationship between both, i get next to the Sdirectories the User data.

Thats not what i want, but i know, that the associated Models can "talk" with each others and should work.

Now i try to set the conditions in CakePHP find method like the SQL statement above:

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

As result i get the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'SdirectoriesUser.sdirectory_id' in 'where clause'

SQL Query: SELECT `Sdirectory`.`id`, `Sdirectory`.`parent_directory_id`, `Sdirectory`.`name`,
`Sdirectory`.`is_root_of_share`, `Sdirectory`.`created`, `Sdirectory`.`modified` FROM
`secufile`.`sdirectories` AS `Sdirectory` WHERE `Sdirectory`.`id` =
`SdirectoriesUser`.`sdirectory_id` AND `SdirectoriesUser`.`user_id` = `User`.`id`

In the FROM clause in the SQL statement are the two Models in "contain" missing. As shown above, without the "conditions", the "contain" works fine. I also tried to start the call from $this->Sdirectory->User(...) - but then i have the problem, that i need to get connected to other Models which are associated to Sdirectory and i need to get the fields from Sdirectory and not from User.

Does anyone know, how i get just the Sdirectories, which are associated to one or more User(s)?

Edit / Update: When i try the following:

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

i get as "last query" the following (working) sql statement:

SELECT 
    [selected_fields]
FROM
    `secufile`.`users` AS `User` 
JOIN 
    `secufile`.`sdirectories_users` AS `SdirectoriesUser` 
ON 
    (`SdirectoriesUser`.`sdirectory_id` IN (1, 2, [...], 29, 31)        
        AND 
    `SdirectoriesUser`.`user_id` = `User`.`id`) 
WHERE
    `SdirectoriesUser`.`user_id` = `User`.`id`

All fine. Now i just also need the second condition:

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

But then i get the same error as on top :-( Does anyone know, where i have to put the second condition? I think the solution must be easy, but i don't see it... :-/

score 0 · Accepted Answer · answered Jan 01 '15 at 17:57

Well, here is a "ugly" solution that works, if someone is interested in:

        $userId = $this->Auth->user('id');
        $joins = array(
            array('table'=>'sdirectories_users',
                'alias' => 'SdirectoriesUser',
                'type'=>'inner',
                'conditions'=> array(
                    'Sdirectory.id = SdirectoriesUser.sdirectory_id'
                )),
            array('table'=>'users',
                'alias' => 'User',
                'type'=>'inner',
                'conditions'=> array(
                    'User.id = SdirectoriesUser.user_id',
                    'User.id' => $userId
                ))
        );
        $options = array(
            'joins' => $joins,
            'contain' => array(
                'User' => array('fields' => 'id')
            ),
            'fields' => array('DISTINCT Sdirectory.id', 'Sdirectory.*')
        );
        $allSdirectories = $this->Sdirectory->find('all',
            $options
        );

Maybe someone else knows a better and sexier way to create the same result just with contain and conditions and without "hardcoded" joins? Would be nice ;-)

CakePHP 2.x HABTM find data which have relationship by condition

1 Answers1