CakePHP：如何计算查找中的 hasMany 记录数？

发布于 2024-09-07 15:18:35 字数 778 浏览 5 评论 0原文

我有两个模型，Post hasMany Comment。如何选择所有少于两个 Comment 的 Post？

我尝试使用 find 和 'fields'=>array('COUNT(Comment.id) as numComments','Post.*'), （然后做'conditions' 中的 numComments <2)。但是，我收到“字段列表”中的未知列“Comment.id” 错误。

谢谢！

编辑：我已经让 CakePHP 生成此查询：

SELECT `Post`.*, FROM `posts` AS `Post` 
    LEFT JOIN comments AS `Comment` ON (`Post`.`id` = `Comment`.`text_request_id`)  
    WHERE COUNT(`Comment`.`id`) < 2 
    GROUP BY `Comment`.`post_id` 
    LIMIT 10

但我在 COUNT 函数上收到错误 #1111 - Invalid use of group function 。

编辑：已解决，使用 HAVING COUNT 而不是 WHERE COUNT。

原文

I have two models, Post hasMany Comment. How do I select all Post that have less than two Comment?

I tried using a find with 'fields'=>array('COUNT(Comment.id) as numComments','Post.*'), (and then doing a numComments < 2 in 'conditions'). But, I get a Unknown column 'Comment.id' in 'field list' error.

Thanks!

EDIT: I've gotten CakePHP to generate this query:

SELECT `Post`.*, FROM `posts` AS `Post` 
    LEFT JOIN comments AS `Comment` ON (`Post`.`id` = `Comment`.`text_request_id`)  
    WHERE COUNT(`Comment`.`id`) < 2 
    GROUP BY `Comment`.`post_id` 
    LIMIT 10

But I get an error #1111 - Invalid use of group function on the COUNT function.

EDIT:
Resolved, use the HAVING COUNT instead of WHERE COUNT.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

债姬 2024-09-14 15:18:35

class Post extends AppModel
{
    var $name = "Post";
    var $hasMany = array('Comment'=>array('counterCache'=>true));
}

将 comment_count 字段添加到帖子中

，仅此而已:-)

class Post extends AppModel
{
    var $name = "Post";
    var $hasMany = array('Comment'=>array('counterCache'=>true));
}

add comment_count fields into posts

an that's all :-)

回复收藏 0 原文

北座城市 2024-09-14 15:18:35

在原始 SQL 中，查询将如下所示：

SELECT Post.*
FROM Post LEFT JOIN Comment ON Post.id = Comment.post_id
GROUP BY Comment.post_id
HAVING COUNT(Comment.id) < 2

大多数查询都可以轻松转换为 Cake：

array(
    'having' => array('COUNT(Comment.id) <' => 2),
    'group'  => array('Comment.post_id')
)

尽管 Cake 不会自动连接 hasMany 表，但您需要手动执行此操作。有关详细信息，请参阅文档。

编辑：

您可以执行 having 子句，如下所示：

array(
    'group' => 'Comment.post_id HAVING COUNT(Comment.id) < 2'
)

限制是 string 仅适用于组，如果没有分组依据则无法完成。 Cake 3 可能会包含更多 SQL 语法，例如 HAVING

In raw SQL, the query would look something like this:

SELECT Post.*
FROM Post LEFT JOIN Comment ON Post.id = Comment.post_id
GROUP BY Comment.post_id
HAVING COUNT(Comment.id) < 2

Most of these are easily translated to Cake:

array(
    'having' => array('COUNT(Comment.id) <' => 2),
    'group'  => array('Comment.post_id')
)

Cake does not automatically join hasMany tables though, this is something you'll need to do manually. Have a look at the documentation for the details.

Edit:

You can do a having clause as follows:

array(
    'group' => 'Comment.post_id HAVING COUNT(Comment.id) < 2'
)

The limitations are string only for the group and cant be done without the group by. Cake 3 will probably include more SQL syntax such as HAVING

回复收藏 0 原文