Felix Eggbert Sat Nov 12 10:19:15 -0500 2011

Subject: Find all blog records from blog > posts > comments > user > name = x

Hi,

I am new to this but am getting good results already...however i am struggeling with how to approach such a query:

My associations look something like this:

blog 1:n Posts 1:n Comments n:1 Users

How can i find all Blogs including Posts and Comments where there are comments by User = 'Peter'?

I was trying something like this:

$blogs = Blog::find('all','include' => array('posts' => array('comments' => array('users', 'condition' => array('name','Peter')))));

But it doesn't work...

How would you approach this?

Thanks a lot!

Felix


Ian B Sun Nov 13 06:11:55 -0500 2011

This is a bit complicated because it appears you want to separate the data by blog, when the blog table is the furthest one away from the data you know (the user). What you'd usually have to do is work backwards from either the user or comments table, because AFAIK the join option only works one table away from the model (unless you define the join using SQL syntax).

AR including works by querying the included table(s) individually using data found in the original query, for the sake of performance and simplicity. That means you can't use conditions involving other tables, unless you join them instead.

If you just wanted to list comments, with extra data about the post and blog for each comment (eg what you'd see on a user's profile), something like this should work, using comments as a pivot table.

$comments = Comment::all(array(
'join' => array('user', 'post'),
'include' => array('user', 'post' => array('blog')),
'conditions' => array('users.name = ?', array('Peter')),
));

I'm not sure exactly what your requirement is, but I'm guessing you want to find any blog Peter's commented in, and get all the posts and comments in that blog, regardless of Peter's activity in an individual post.

My approach would be to identify the matching blogs, then start from there.

$blog_ids = Comment::all(array(
'select' => 'DISTINCT posts.blog_id AS blog_id',
'join' => array('user', 'post'),
'conditions' => array('users.name = ?', array('Peter')),
));
$ids = array();
foreach ($blog_ids as $a) { $ids[] = $a->blog_id; }
$blogs = Blog::find_by_id($ids, array(
'include' => array('posts' => array('comments' => array('users')))
));

Ian B Sun Nov 13 06:18:09 -0500 2011

or, using array_map instead of that foreach mess:

$blog_ids = Comment::all(array(
'select' => 'DISTINCT posts.blog_id AS blog_id',
'join' => array('user', 'post'),
'conditions' => array('users.name = ?', array('Peter')),
));
$blogs = Blog::find_by_id(
array_map(function($a) { return $a->blog_id; }, $blog_ids),
array( 'include' => array('posts' => array('comments' => array('users'))) )
);

(1-2/2)