Retrieve posts with their latest comment in CakePHP

Question

Say I have a model Post and a model Comment related as follows:

Post hasMany Comment 
Comment belongsTo Post

How do use find('all') to retrieve every Post with its associated latest Comment?

I have tried defining a hasOne relationship in Post as:

var $hasOne = array('LatestComment' => array('className' => 'Comment', 'order' => 'LatestComment.created DESC'));

But when I do a Post->find('all') it returns every Post multiple times, once per each Comment, with LatestComment set to the different Comments.

Answer 1

To remove the duplicates you want to use: GROUP BY in your find all query. I believe Cake has a 'group' => option as well.

Answer 2

You can add 'limit' => 1 to your array of parameters to only return one comment.

Alternatively, instead of defining another relationship, you can simply limit the number of comments returned when you perform the find, using the Containable behaviour.

$this->Post->find('all',array(
                     'contain' => array(
                          'Comment' => array(
                              'order' => 'Comment.created DESC',
                              'limit' => 1
                          )
                      )
                  );

This is useful if you want to filter any related sets without defining relationships - by author, or within a date range, for example.

Make sure that you add the Containable behaviour to any model that you reference.