构造一个复杂的SQL查询（或查询）

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.

作为更大的网络应用程序(使用CakePHP)的一部分,我正在组建一个简单的博客系统。这种关系非常简单:每个用户都有一个博客,其中包含许多条目,其中包含许多评论。

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.

理想情况下,我希望解决方案保留在Cake的模型数据检索设备(Model-> find()等)中,但我对此并不乐观。

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

任何人都有一个聪明/优雅的解决方案?我正在为一些疯狂的SQL黑客做好准备来完成这项工作......

4 个解决方案

#1

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

嘿,我刚回来的时候回答基本相同(使用Cake的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.

我做了一个额外的改进,使用Containable行为来提取Entry数据而不是Comment数据。

#2

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

#3

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.

如果您没有对评论的时间敏感特性感到不满,可以通过在条目表中添加“comment_count”字段来使用CakePHP的counterCache功能,配置与此字段的Comment belongsTo Entry关联的counterCache键,然后在Entry模型上调用find()。

#4

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

你可能想要一个WHERE子句来获取最近30天的评论:

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

#1