如何根据布尔值对同一字段进行两次计数?

问题描述

我想通过自定义选择和联接来解决我的N + 1计数问题.我的帖子和标签之间存在多对多关系.标签可以确认也可以不确认.我想在我的帖子选择语句中包括标签字段tag_confirmed_count和tags_unconfirmed_count(这样就可以避免以后为每个问题计算标签的数量).

I want to fix my N+1 count problem with a custom select and joins. I have a many-to-many relationship between my posts and tags.Tags can be confirmed or uncofirmed.I want to include the field tags_confirmed_count and tags_unconfirmed_count in my posts select statement (so I can avoid counting tags for each question later).

我已对带有以下查询的帖子执行标签计数:

I have executed counting tags for posts whith the following query:

Post.joins(:tags).select("posts.*, COUNT(tags.id) AS tags_count").group("posts.id")

现在，我找不到一种解决方案，对于已确认字段设置为true的标签和为false的标签进行计数一次.

Now I cannot find a solution to counting tags once for tags that have the field confirmed set to true and once for false.

我以这种方式尝试过，但是以这种方式，我只能算出已确认的那些，而不是未确认的:

I tried it this way, but I this way I can only count the confirmed ones, but not the unconfirmed ones:

Post.joins(:tags).select("posts.*, COUNT(tags.id) AS tags_confirmed_count").group("posts.id").where("tags.confirmed = true")

推荐答案

在真正的SQL中而不是Rails-ish中，您将编写:

In real SQL not Rails-ish, you'd write:

SELECT 
  count(CASE WHEN confirmed THEN 1 END) AS n_confirmed,
  count(CASE WHEN NOT confirmed THEN 1 END) AS n_unconfirmed,
  ...
FROM posts 
  ...

希望会对您有所帮助.不知道如何/是否可以将其翻译为ActiveRecord.

which will hopefully help you out. No idea how/if that can be translated to ActiveRecord-speak.

这篇关于如何根据布尔值对同一字段进行两次计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！