<p>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.</p>

<p>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.</p>

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

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

[{'answer_id': 52814, 'author': 'jodonnell', 'author_id': 4223, 'author_profile': 'https://Stackoverflow.com/users/4223', 'pm_score': 2, 'selected': False, 'text': "<p>Shouldn't be too bad, you just need a group by (this is off the type of my head, so forgive syntax errors):</p>\n\n<pre><code>SELECT entry-id, count(id) AS c \nFROM comment \nWHERE comment.createdate &gt;= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) \nGROUP BY entry-id \nORDER BY c DESC\n</code></pre>\n"}, {'answer_id': 52838, 'author': 'Lost in Alabama', 'author_id': 5285, 'author_profile': 'https://Stackoverflow.com/users/5285', 'pm_score': 0, 'selected': False, 'text': '<p>You probably want a <strong>WHERE</strong> clause to get just last 30 days comments:</p>\n\n<pre><code>SELECT entry-id, count(id) AS c \nFROM comment \nWHERE comment_date + 30 &gt;= sysdate\nGROUP BY entry-id \nORDER BY c DESC\n</code></pre>\n'}, {'answer_id': 52848, 'author': 'Daniel Wright', 'author_id': 5030, 'author_profile': 'https://Stackoverflow.com/users/5030', 'pm_score': 3, 'selected': True, 'text': "<p>Heh, I was just about to come back with essentially the same answer (using Cake's Model::find):</p>\n\n<pre><code>$this-&gt;loadModel('Comment');\n\n$this-&gt;Comment-&gt;find( 'all', array(\n    'fields' =&gt; array('COUNT(Comment.id) AS popularCount'),\n    'conditions' =&gt; array(\n        'Comment.created &gt;' =&gt; strtotime('-1 month')\n    ),\n    'group' =&gt; 'Comment.blog_post_id',\n    'order' =&gt; 'popularCount DESC',\n\n    'contain' =&gt; array(\n        'Entry' =&gt; array(\n            'fields' =&gt; array( 'Entry.title' )\n        )\n    )\n));\n</code></pre>\n\n<p>It's not perfect, but it works and can be improved on.</p>\n\n<p>I made an additional improvement, using the Containable behaviour to extract the Entry data instead of the Comment data.</p>\n"}, {'answer_id': 66801, 'author': 'neilcrookes', 'author_id': 9968, 'author_profile': 'https://Stackoverflow.com/users/9968', 'pm_score': 1, 'selected': False, 'text': '<p>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.</p>\n'}]
['https://Stackoverflow.com/questions/52806', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5030/']