Rails ActiveRecord 按时间分组但用 nil 值填充日期？

发布于 2024-10-31 11:05:47 字数 209 浏览 0 评论 0原文

因此，我使用 activerecord 来获取某一天的项目数：

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

但是如果某一天没有记录，这不会给我 0 ，我该如何解决这个问题？或者有更多的“Rails”方式来做这样的事情吗？

非常感谢任何帮助，再次感谢！

原文

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

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?

Any help is hugely appreciated, thanks again!

分享到QQ

分享到微博

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

发布评论

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

紅太極 2024-11-07 11:05:47

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

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

编辑：删除 .values

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

或者，如果您想确保哈希中存在范围内每个日期的条目，以便您可以迭代这些条目，...

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

更新

这是当我将日期范围和模型传递到这里时的一些输出，奇怪的是它得到了不同长度的哈希值：

{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}

不确定是什么导致了它......嗯。

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.

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.

Edit: Remove .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.

回复收藏 0 原文

~没有更多了~