Conditions in associated models using Model->find() (CakePHP)

Question

I am having some issues with CakePHP's find() method and conditions in 'deeper' model associations. There are some of these around but I could not find an answer to this so far.

My model associations are User hasMany Post hasMany Comment hasMany Vote and Vote belongsTo Comment belongsTo Post belongsTo User respectively. The belongsTo associations use inner joins ('type' => 'INNER').

How do I find all comment votes for posts of a specific user with CakePHP's model->find() method?

I used a chain of four models deliberately, because this seems to work for conditions in directly associated models. So there is no using the foreign-key-holding column in the neighbouring table (condition 'Post.user_id == 1' instead of 'User.id == 1').

In SQL this would be:

SELECT v.* 
FROM votes v 
    JOIN comments c ON (v.comment_id = c.id)
    JOIN posts p ON (c.post_id = p.id)
    JOIN users u ON (p.user_id = u.id)
WHERE u.id = 1

I am unable to reproduce these joins using find() + the Containable behavior. Although I could simply get a user with all his data, I would then have to collect all votes from inside the resulting array.

It is not working like this (Warning: unknown column 'User.id'):

$this->Vote->recursive = 2; // or higher
$this->Vote->find('all',array('conditions' => array('User.id' => 1)));

In fact, this doesn't even work using Post instead of User (Vote->Comment->Post) as soon as I add the condition. The manufactured SQL query only joins votes and comments.

The returning array should only contain votes the SQL query above would return, everything else should be "joined away" in the process.

Note: My question is quite close to this one, which helped me getting started: In cakephp how can I do a find with conditions on a related field?

Answer 1

$joins = array(
           array('table'=>'comments', 
                 'alias' => 'Comment',
                 'type'=>'inner',
                 'conditions'=> array(
                 'Comment.id = Vote.comment_id'
           )),
           array('table'=>'posts', 
                 'alias' => 'Post',
                 'type'=>'inner',
                 'conditions'=> array(
                 'Post.id = Comment.post_id'
           )),
           array('table'=>'users', 
                 'alias' => 'User',
                 'type'=>'inner',
                 'conditions'=> array(
                 'User.id = Post.user_id','User.id'=>$user_id
           ))
         );

$votes = $this->Vote->find('all',array('joins'=>$joins,'recursive'=>-1));

Answer 2

Use the Containable behavior to perform conditions on associated Models. It took me a bit to dig this up, but it works like a charm! And it uses LEFT JOINs so it will still pull in all values for the original Model.

See documentation.

Something like this should work:

$this->Vote->Behaviors->attach('Containable');
$this->Vote->find('all',array(
                            'contain'=>array(
                                'Comment'=>array(
                                    'Post'=>array(
                                        'User'=>array(
                                            'conditions'=>array(
                                                'User.id'=>1,
                                            ),
                                        ),
                                    ),
                                ),
                            ),
                        ));

And if you wanted to include the User data of the person who voted you could simply add one more item to the initial array:

$this->Vote->Behaviors->attach('Containable');
$this->Vote->find('all',array(
                            'contain'=>array(
                                'Comment'=>array(
                                    'Post'=>array(
                                        'User'=>array(
                                            'conditions'=>array(
                                                'User.id'=>1,
                                            ),
                                        ),
                                    ),
                                ),
                                'User',
                            ),
                        ));

Hope that helps!