Help me construct a complex SQL query (or queries).

Question

As part of a larger web-app (using CakePHP), I'm putting together a simple blog system. The relationships are exceedingly simple: each User has a Blog, which has many Entries, which have many Comments.

An element I'd like to incorporate is a list of "Popular Entries." Popular Entries have been defined as those with the most Comments in the last month, and ultimately they need to be ordered by the number of recent Comments.

Ideally, I'd like the solution to stay within Cake's Model data-retrieval apparatus (Model->find(), etc.), but I'm not sanguine about this.

Anyone have a clever/elegant solution? I'm steeling myself for some wild SQL hacking to make this work...

Answer 1

Shouldn't be too bad, you just need a group by (this is off the type of my head, so forgive syntax errors):

SELECT entry-id, count(id) AS c 
FROM comment 
WHERE comment.createdate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
GROUP BY entry-id 
ORDER BY c DESC

Answer 2

You probably want a where clause to get just last 30 days comments:

SELECT entry-id, count(id) AS c FROM comment WHERE comment_date + 30 >= sysdate GROUP BY entry-id ORDER BY c DESC

Answer 3

Heh, I was just about to come back with essentially the same answer (using Cake's Model::find):

$this->loadModel('Comment');

$this->Comment->find( 'all', array(
    'fields' => array('COUNT(Comment.id) AS popularCount'),
    'conditions' => array(
        'Comment.created >' => strtotime('-1 month')
    ),
    'group' => 'Comment.blog_post_id',
    'order' => 'popularCount DESC',

    'contain' => array(
        'Entry' => array(
            'fields' => array( 'Entry.title' )
        )
    )
));

It's not perfect, but it works and can be improved on.

I made an additional improvement, using the Containable behaviour to extract the Entry data instead of the Comment data.

Answer 4

If you weren't fussed about the time sensitive nature of the comments, you could make use of CakePHP's counterCache functionality by adding a "comment_count" field to the entries table, configuring the counterCache key of the Comment belongsTo Entry association with this field, then call find() on the Entry model.