What you're trying to do is pretty much built-in with CakePHP. The big thing is to make sure that your models are properly associated. Once this is accomplished, Cake will do most of the heavy lifting for you.
For your situation, use 3 models associated like this:
class Story extends AppModel{
var $belongsTo = 'Author';
var $hasMany = 'Comment';
}
class Author extends AppModel{
var $hasMany = array( 'Story', 'Comment' );
var $hasAndBelongsToMany = 'User';
}
class Comment extends AppModel{
var $belongsTo = array( 'Author', 'Story' );
}
Set up your tables according to the Cake conventions. Then a little CakePHP magic in your controller:
$this->Story->Author->bindModel( array( 'hasOne' => array( 'AuthorsUsers' ) ) );
$myAuthors = $this->Story->Author->find( 'list', array(
'fields' => array( 'id' ),
'conditions' => array( 'AuthorsUsers.user_id' => $userId ),
'recursive' => false
) );
$stories = $this->Story->find( 'all', array(
'fields' => array( 'Story.id', 'Story.title', 'Author.id', 'Author.name' ),
'order' => 'published_date DESC',
'conditions' => array( 'Author.id' => $myAuthors ),
'recursive' => 2
) );
Quick explanation of what's going on:
-
bindModel() lets Cake know that you want to use the HABTM association to find Authors by the associated User id. (Cake convention is to have a table called 'authors_users' to join the Author->User HABTM association.)
- If you debug the
$myAuthors variable, you'll see that it gets a simple array of ids.
-
'conditions' => array( 'field' => array() ) will get parsed as "WHERE field IN (...)". In this example, we get all models WHERE 'Author.id' IN $myAuthors.
- The short
$this->Story->find() call is the beauty of Cake. It will automatically find all Story models matching the specified conditions, and it will find the other models associated with each found Story. (You can tell it not to find associated models by turning off Recursive or using Containable behavior.)
- Debugging the
$stories variable will show you a structure like the following:
Array
(
[0] => Array
(
[Story] => Array
(
[id] => 1
[title] => 'Common Sense'
[published_date] => 1776-01-10
)
[Author] => Array
(
[id] => 1
[name] => 'Thomas Paine'
)
[Comment] => Array
(
[0] => Array
(
[id] => 1
[text] => 'Revolutionary!'
[Author] => Array
(
[id] => 3
[name] => 'Silence Dogood'
)
)
[1...n] => ...
)
)
[1...n] => ...
)
You can then use that structure in your View to display data as you desire.
I have a feeling there should be a way to do it with just 1 query, but this way works and doesn't require you to do any subqueries or insert custom SQL into your Cake find() call.
