ActiveRecord组计数为零 [英] ActiveRecord group count with zeroes

问题描述

Company.group('year(created_at)').group('month(created_at)').count
#=> {[2015, 4]=>62, [2015, 5]=>77, [2015, 6]=>61, [2015, 7]=>19, [2015, 8]=>16}

上面的AR查询按年份和月份返回公司的分组计数.

The above AR query returns grouped count of companies by year and month.

问题在于，对于具有零个公司的组，它不返回任何条目.

The issue is that it does not return any entry for group with zero companies.

我希望查询会返回所有组，其中从创建第一家公司的月份开始的每个月开始，直到当前月份，如下所示:

I would expect the query to return all the groups, containing every month starting from the month, when first company was created_at up to the current month, like this:

{[2015, 4]=>62, [2015, 5]=>77, [2015, 6]=>61, 
 [2015, 7]=>19, [2015, 8]=>16, [2015, 9]=>0} #note the last entry - it is September (9nth month) with zero companies.

我知道，MySQL需要至少一行来返回一组.

I know, that MySQL needs at least one row to return a group.

有(确定有)我需要的解决方法吗?

Are there (sure there are) workarounds for my need?

我正在寻找一种不引入任何新表的AR查询解决方案.

I am looking for a AR query solution without introducing any new tables or so.

推荐答案

您是否考虑过

Have you thought about a calendar table? Although the site is very old, it still describes the use of such a table very well:

• 每天产生连续销售总额，其中包括您没有销售的天数，例如周末.
• 需要按季节跟踪数据，无论这对您的公司意味着什么.
• 需要使用滚动月份/期间比较数据.

• Generate a running sales total per day and include days in which you have no sales such as weekends.
• Need to track data by seasons, whatever that means to your company.
• Need to compare data using rolling months/periods.

---

尽管许多问题可以通过编写SQL代码来解决，但使用专用的日期表则要容易得多.此外，可以自定义日期表以跟踪有关您想要的特定日期的任何信息.

While many of these issues can be handled by writing SQL code, they’re much easier with a dedicated date table. Also, the Date Table can be customized to track anything about a particular day you want.

因此，在您的情况下，您将创建一个这样的表，然后简单地将其与您的Company模型连接起来.

So in your case you would create such a table and then simply join it with your Company model.

通往类似这样的查询

CalendarDay.where(year: Date.current.year).joins(:companies).group('YEAR(companies.created_at)').count

这篇关于ActiveRecord组计数为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！