Complex CakePHP data return and combining

Question

I am aiming to create a summary page of activity on an application I am currently working on. I have identified that I must do the following:

Get all stories from people I am subscribed to and format them like the following:

[Username] has posted [StoryName] - View story

Get all stories that users I am connected to have posted comments on

[Username] has posted a comment on [StoryName] - View story

I am unsure how I can get both arrays, display them the format I want but order them by the posted date (in the same way people like Facebook do)

What is the best way to go about this?

Please Note: The answer must be something which is easily extendible. I am considering following wordpress' model and creating a Posts table which has a Post Type field.

Answer 1

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.