Rails group a table by :created_at, returning a count of the :status column, and then sub-group the :status column with count of each unique value

Question

I have a model Notification and I would like to know how many notifications are created per days and how many with each status. status is a string key of the model Notification.

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

That give me :

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

But I would like something like :

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

Or similar. Is it possible with active record ? Maybe group_by was something good for me. But it's deprecated.

For the moment I've tried :

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

It's not the result I want.

Answer 1

This is tough for me to test possible answers since the date is somewhat unique and the query slightly complex. But here is how to get your counts in a hash:

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

Edit

As the OP found out, this becomes very inefficient as the number of dates queried against goes up. That's because it is calling a query for every date returned in the first query. I'm sure there is a way to do this as one long SQL query and use that as a view in the DB.

Answer 2

I'm adding a second answer with a different approach that I believe to be much better in that it is efficient and can be translated into a DB view.

Any time I end up with lots of repeated hits on the DB or large, complex queries that don't translate well, I look to use pure SQL as that can then be used as a view in the DB. I asked this question because my SQL is poor. I think this can be adapted to your needs, especially if the "status" field is a know set of possible values. Here's how I would try it initially:

Construct a SQL query that works. You can test this in 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;

This should return a pivot table like:

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

Great, any single table is an easy query in Rails that will load it up as an array of objects. Each of those objects will have a method that corresponds to each column. If the column is null for that row Rails will pass nil as the value.

Test it in 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;")

Which will return an array of Notification objects...

=> [#< 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">,...]

Note that the Notification id: is nil. That's because these objects do not represent the actual objects in the DB, but a row in the table produced by your query. But now you can do something like:

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

and so on.. Your @stats variable is easily passed to a view where it is easily printed as a table in an .html.erb file. You can access the attributes of any Notification object in the array like:

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

@stats[1].pending
  #=> 12

The overall point is you have used one query to get your entire dataset.

Store it as a view Log into the SQL console on your DB and do

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;

Now you can get the results with

Select * FROM daily_stats;

Note that I have purposefully not limited this by user as you are in your original question and added user_id to the SELECT. We are working in the DB directly and it should easily handle generating a table from this view with ALL users stats for every date. This is a very powerful dataset for what you are doing. Now you can set up a dummy model in Rails and easily have all of your data available without contorted Rails queries.

Add a dummy model 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

add the corresponding relation to your User model:

class User < ActiveRecord::Base
  has_many :daily_stats
end

Now you have access to your stats by user in a very rail-ish way.

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>...]

and in the other direction:

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

The key is to "solve things at the lowest level". Solve a data query problem in the database, then use Rails to manipulate and present it.