Pagination of Multiple Models in CakePHP 1.2.5

Question

I'm working on a view for a blog that mixes blog posts, comments, and uploaded media items into one large grid layout. I've set up the individual models in CakePHP and the associations, some of which are as follows:

Comment BelongsTo Post or Media
Post HasMany Media

What I'm working with is trying to sort all three models (Comment, Media, Post) into one large array of data which I can then paginate.

I've got a 'created' datetime field in the database already. I understand how to paginate each of the individual database calls using the CakePHP PaginationHelper. I also have merged the arrays. However, mixing individual database calls and then merging the arrays seems to break the pagination as it doesn't work with the PaginationHelper (as I understand).

Do you have any suggestions for doing this?

Also, I'd like to keep the number of database calls down, so any suggestions along those lines would be great. Thanks!

~Andrew

Answer 1

It sounds like you want to utilize some sort of UNION select regarding those 3 models. You can achieve this by overriding the model's default paginate and paginateCount methods. The basics are covered in the Cook Book (http://book.cakephp.org/view/249/Custom-Query-Pagination).

It sounds like you'll want to replace the find(all) with find('query') like so:

/**
 * Overridden paginate method - group by week, away_team_id and home_team_id
 */
function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
    $recursive = -1;
    $group = $fields = array('week', 'away_team_id', 'home_team_id');
    return $this->query('SELECT field1, field2 FROM table1 
                         UNION SELECT field1, field2 FROM table2 
                         LIMIT BY '.(($page-1)*$limit).', '.$limit);
}

Answer 2

so what about if i want to overriding this to smoe action and other work with the default way?