Rails ActiveRecord按时间组但pad日期为空值?

So I'm using activerecord to get the count of items for a certain day:

所以我使用activerecord获取某一天的物品数量:

Item.group("date(created_at)").count.values

But this doesn't give me 0 if there are no records for a given day, how can I fix this? Or is there are more "Rails" way do to something like this?

但这并没有给我0如果某一天没有记录，我怎么修正呢?或者有更多的“Rails”方法来做这样的事情吗?

Any help is hugely appreciated, thanks again!

非常感谢您的帮助，再次感谢!

1 个解决方案

#1

Using a GROUP BY query, you are going to get groups based on the records that exist, so if there are no records with a given value in the grouping column, there will be no groups for that value. The database does not psychically know to add in other date values that you might be interested in.

使用GROUP BY查询，您将根据存在的记录获得组，因此，如果在分组列中没有具有给定值的记录，那么该值将没有组。数据库在精神上不知道添加您可能感兴趣的其他日期值。

What I would do to get what you want is create a new Hash with a default value of 0 and then merge the result of your query into that. When you subsequently try to retrieve any value from that Hash for which there is no entry, you'll get 0 as the result.

我要做的是创建一个默认值为0的新哈希，然后将查询结果合并到其中。当您随后尝试从没有条目的散列中检索任何值时，您将得到0作为结果。

Edit: Remove .values

编辑:删除. values

date_counts = Hash.new(0)
date_counts.merge! Item.group("date(created_at)").count

Alternatively, if you want to make sure there are entries in the Hash for every date in a range, so you can iterate over those, ...

或者，如果您想要确保在一个范围内的每个日期的哈希中都有条目，那么您可以对它们进行迭代，……

date_counts = {}
(Date.civil(2010,1,1)...Date.civil(2010,2,1)).each do |date|
  date_counts[date]=0
end
date_counts.merge! Item.group("date(created_at)").count

Update

更新

Here is some output when I pass my date range and model into here, oddly enough it gets different length hashes:

当我将日期范围和模型传递到这里时，这里有一些输出，奇怪的是，它会得到不同的长度散列:

{Sun, 01 May 2011=>0, Mon, 02 May 2011=>0, Tue, 03 May 2011=>0, Wed, 04 May 2011=>0, Thu, 05 May 2011=>0, Fri, 06 May 2011=>0, Sat, 07 May 2011=>0, Sun, 08 May 2011=>0, Mon, 09 May 2011=>0, Tue, 10 May 2011=>0, Wed, 11 May 2011=>0, Thu, 31 Mar 2011=>2, Sun, 03 Apr 2011=>1, Fri, 08 Apr 2011=>643, Sat, 09 Apr 2011=>2360, Sun, 10 Apr 2011=>705, Mon, 11 Apr 2011=>34}
{Sun, 01 May 2011=>0, Mon, 02 May 2011=>0, Tue, 03 May 2011=>0, Wed, 04 May 2011=>0, Thu, 05 May 2011=>0, Fri, 06 May 2011=>0, Sat, 07 May 2011=>0, Sun, 08 May 2011=>0, Mon, 09 May 2011=>0, Tue, 10 May 2011=>0, Wed, 11 May 2011=>0, Sat, 02 Apr 2011=>1, Fri, 08 Apr 2011=>4158, Sat, 09 Apr 2011=>12206, Sun, 10 Apr 2011=>4279, Mon, 11 Apr 2011=>169}
{Sun, 01 May 2011=>0, Mon, 02 May 2011=>0, Tue, 03 May 2011=>0, Wed, 04 May 2011=>0, Thu, 05 May 2011=>0, Fri, 06 May 2011=>0, Sat, 07 May 2011=>0, Sun, 08 May 2011=>0, Mon, 09 May 2011=>0, Tue, 10 May 2011=>0, Wed, 11 May 2011=>0}

Not sure whats causing it... hmm.

不知道是什么引起的……嗯。

#1