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

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 开发者_JAVA技巧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?

$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));

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!

This may be one of those times you need to use the query method.

SQL calls that you can't or don't want to make via other model methods (careful - there are very few circumstances this is true) can be made using the model's query() method.

$votes = $this->Vote->query('SELECT Vote.* FROM votes Vote 
    JOIN comments Comment ON (Vote.comment_id = Comment.id)
    JOIN posts Post ON (Comment.post_id = Post.id)
    JOIN users User ON (Post.user_id = User.id)
    WHERE User.id = 1');

This should return an array of Vote entries like the find method would.

foreach ($votes as $vote):
    echo $vote['Vote']['id'];
endforeach;

Rather than doing a custom SQL query, you can explicitly join the tables in order to filter by a field of an indirectly associated table. Have a look at this page to see how to join the votes and users through comments: http://book.cakephp.org/view/872/Joining-tables