Rail按 :created_at 对表进行分组，返回 :status 列的计数，然后对 :status 列进行子分组，

Question

我有一个模型通知，我想知道每天创建多少个通知以及每个状态有多少个通知。 status是模型通知的字符串键。

Notification.where(user: users).group('DATE(created_at)').count('created_at')

那给我：

{Mon, 05 Oct 2015=>2572,
 Tue, 06 Oct 2015=>555,
 Wed, 07 Oct 2015=>2124,
 Thu, 08 Oct 2015=>220,
 Fri, 09 Oct 2015=>36}

但我想要这样的东西：

{Mon, 05 Oct 2015=>{total=>2572, error=>500, pending=>12},
 Tue, 06 Oct 2015=>{total=>555, sent=>50, pending=>12}}

或类似。活动记录可以吗？也许group_by对我有好处。但它已被弃用。

目前我已经尝试过：

Notification.where(user: users).group('DATE(created_at), status').count('created_at') 
# => {"error"=>2, "sent"=>42}

这不是我想要的结果。

Answer 1

这对我来说很难测试可能的答案，因为日期有些独特，查询有点复杂。但以下是在哈希中获取计数的方法：

my_hash = {} #create your empty hash
notifications = Notification.where(user: users).select(:created_at).uniq
  #get a hash of Notification objects with unique created_at dates
notifications.each do |n|
  my_hash[n.created_at] = Notification.where(created_at: n.created_at).select(:status).group(:status).count
  #load your hash with <created_at date> => {"error" => 20, "pending" => 30}
  my_hash[n.created_at]["total"] = Notification.where(created_at: n.created_at).count
  #add the total count to your hash
end

编辑

正如OP发现的那样，随着查询日期数量的增加，这变得非常低效。这是因为它为第一个查询中返回的每个日期调用查询。我确信有一种方法可以将其作为一个长SQL查询并将其用作数据库中的视图。

Answer 2

我用一种不同的方法添加了第二个答案，我相信这种方法要好得多，因为它很有效并且可以转换为数据库视图。

每当我最终在数据库上出现大量重复命中或无法很好地转换的大型复杂查询时，我都希望使用纯 SQL，因为它可以用作数据库中的视图。我问这个问题是因为我的SQL很差。我认为这可以适应您的需求，特别是如果"状态"字段是一组已知的可能值。以下是我最初尝试的方式：

构造一个有效的 SQL 查询。您可以在 psql 中对此进行测试。

SELECT created_at, count(status) AS total,
sum(case when status = 'error' then 1 end) AS errors,
sum(case when status = 'pending' then 1 end) AS pending,
sum(case when status = 'sent' then 1 end) AS sent
FROM notifications
GROUP BY created_at;

这应该返回一个数据透视表，如下所示：

| created_at       |total|errors|pending|sent|
----------------------------------------------
| Mon, 05 Oct 2015 |2572 |500   |12     |null|
| Tue, 06 Oct 2015 |555  |null  |12     |50  |

太好了，任何单个表在 Rails 中都是一个简单的查询，它会将其作为对象数组加载。其中每个对象都有一个与每列相对应的方法。如果该行的列为 null，则 Rails 将传递nil作为值。

在 Rails 中进行测试

@stats = Notification.where(user: users).find_by_sql("SELECT created_at, count(status) 
  AS total,
  sum(case when status = 'error' then 1 end) AS errors,
  sum(case when status = 'pending' then 1 end) AS pending,
  sum(case when status = 'sent' then 1 end) AS sent
  FROM notifications
  GROUP BY created_at;")

这将返回一个Notification对象的数组...

=> [#< Notification id: nil, created_at: "2014-02-07 22:36:30">
#< Notification id: nil, created_at: "2014-06-26 02:07:51">,
#< Notification id: nil, created_at: "2015-04-26 21:37:09">,
#< Notification id: nil, created_at: "2014-02-07 22:48:29">,
#< Notification id: nil, created_at: "2014-11-04 23:39:07">,
#< Notification id: nil, created_at: "2015-01-27 17:46:50">,...]

请注意，Notification id:为零。这是因为这些对象并不表示数据库中的实际对象，而是查询生成的表中的一行。但是现在您可以执行以下操作：

@stats.each do |daily_stats|
  puts daily_stats.attributes
end
#{"created_at" => "Mon, 05 Oct 2015", "total" = 2572, "errors" => 500, "pending" => 12, "sent" => nil}
#{"created_at" => "Tue, 06 Oct 2015", "total" = 555, "errors" => nil, "pending" => 12, "sent" => 50}

等等..@stats变量很容易传递到视图中，在该视图中，它可以轻松地打印为.html.erb文件中的表。您可以访问数组中任何通知对象的属性，例如：

@stats[0].created_at
  #=> "Mon, 05 Oct 2015"
@stats[1].pending
  #=> 12

总的来说，你已经使用了一个查询来获取整个数据集。

将其存储为视图登录到数据库上的 SQL 控制台并执行

CREATE VIEW daily_stats AS
SELECT user_id, created_at, count(status) AS total,
   sum(case when status = 'error' then 1 end) AS errors,
   sum(case when status = 'pending' then 1 end) AS pending,
   sum(case when status = 'sent' then 1 end) AS sent
FROM notifications
GROUP BY user_id, created_at;

现在您可以通过以下方式获得结果

Select * FROM daily_stats;

请注意，我故意不按用户限制这一点，因为您在原始问题中，并user_id添加到 SELECT 中。我们直接在数据库中工作，它应该很容易处理从此视图生成一个表，其中包含每个日期的所有用户统计信息。对于您正在做的事情，这是一个非常强大的数据集。现在，您可以在 Rails 中设置虚拟模型，并轻松获得所有数据，而无需扭曲的 Rails 查询。

添加虚拟模型app/models/daily_stat.rb：

class DailyStat < ActiveRecord::Base
  belongs_to :user
  #this is a model for a view in the DB called dash_views
  #class name is singular and will automatically look for the table "daily_stats" which his snake_case and plural.
end

将相应的关系添加到User模型：

class User < ActiveRecord::Base
  has_many :daily_stats
end

现在，您可以以一种非常铁路的方式按用户访问您的统计数据。

users = [2]
DailyStat.where(user: users)
   => AllStat Load (2.8ms)  SELECT "all_stats".* FROM "all_stats" WHERE "all_stats"."category_id" = 2
   => [ #<AllStat user_id: 2, created_at: "2014-02-14 00:30:24", total: 300, errors: 23, pending: nil, sent: 3>,
        #<AllStat user_id: 2, created_at: "2014-11-29 00:18:28", total: 2454, errors: 3, pending: 45, sent: 323>,
        #<AllStat user_id: 2, created_at: "2014-02-07 22:46:59", total: 589, errors: 33, pending: 240, sent: 68>...]

在另一个方向：

user = User.first
user.daily_stats
 #returns array of that users DailyStat objects.

关键是要"在最低层次上解决问题"。解决数据库中的数据查询问题，然后使用 Rails 对其进行操作和呈现。