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?

You mean having a foreign key column user_id in the vote table and directly associate user and vote? That seems a little bit redundant and creates circular relationships in my schema. "Having said that" it would work I guess. Or am I misunderstanding what you mean by multiple belongsTo? If so, is it possible/necessary to explicitly add an association for Vote belongsTo User and configure it to "go through" comment and vote? — Wolfram, Nov 25 '09 at 11:25
Adding a foreign key to every table that it is associated with through another table would be way too redundant. — BWelfel, Feb 27 '10 at 23:14

score 17 · Accepted Answer · answered Dec 24 '10 at 09:21

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

Super upvote... Thanks mate.. I was so struggling with these joins, reference of this code helped me... — Fr0zenFyr, Jun 07 '15 at 20:53

score 7 · Answer 2 · edited Oct 11 '12 at 19:58

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!

I'm getting a different outcome when trying this technique: the condition only applies to 'User'. If the condition isn't met, only 'User' gets nulled while all Votes get returned. In truth, in my code I have HABTM relationships such that I cannot even add 'User' to the original array because Cake complains that Vote is not associated with User. In any case, for me the condition seems to apply only to the leaf, not the whole tree when I wanted the whole tree. — JP Beaudry, May 25 '13 at 18:25
I hope your answer will hep me in my own problem. After 10 minutes, I try in my context, It works, great! Remember, put `public $actsAs = array('Containable');` inside `AppModel` class. — Vy Do, Apr 13 '15 at 16:42

score 1 · Answer 3 · answered Nov 21 '09 at 17:18

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;

Thanks, I am aware of the query() function and what it is used for but I really wanted to know how to "walk the model" using find() to inject a condition somewhere else. — Wolfram, Nov 24 '09 at 14:16

score 0 · Answer 4 · answered Feb 27 '10 at 23:13

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

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

4 Answers4

Linked