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

Question

Hi, 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: http://stackoverflow.com/questions/813294/in-cakephp-how-can-i-do-a-find-with-conditions-on-a-realted-field

Answer 1

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;

Answer 2

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

Answer 3

I know this post is old, but I found an alternative way to finding results deeper in the model.

// How my models are linked
Post  hasMany  Comment  hasOne  User

This is how my page works. When on the post page, CakePHP gets the comments without the user's info. To fix that I added a filter that runs after CakePHP gets the comments.

// Inside of Comment Model
function afterFind($results) {

  // Loop through the number of results
  for( $i = 0; $i < count($results); $i++ ) {

    // At each result get the user
    $user = $this->User->find($results[$i]['Comment']['user_id']);

    // Save the username as a field in the comment data
    $results[$i]['Comment']['username'] = $user['User']['username'];

    /**
     * Alternatively you could do something like this
     * 
     * $results[$i]['Comment']['User'] = $user['User'];
     * you can then access it like so
     * 
     * [in foreach loop]
     *  echo $comment['Comment']['User']['username']
    */
  }

  // Give CakePHP the modified results
  return $results;
}

// In my controller I run it like this
$post = $this->Post->findBySlug($this->params['slug']);
$this->set('post', $post);
$this->set('comments', $post['Comment']);

// And in my view
foreach( $comments as $comment ) { $comment = $comment['Comment'];
  <h3>Posted by: <?php echo $comment['username']; ?></h3>
  <div class="message"><?php echo $comment['message']; ?></div>
}