How to write a rails query that returns the count by Month?

Question

take a standard NewsFeed model (id,user_id)

How can I query for the # of records per month in the NewsFeed model, and then exclude a few user_id's?

Results would yield:

Jan - 313
Feb - 3131
Mar - 44444
etc...

Is there a simple way to do this with rails or do you need to write a query for each month?

Thanks

Answer 1

In Rails 4, the way to do this is to create scopes on your model.

class NewsFeed < ActiveRecord::Base
  scope :group_by_month,   -> { group("date_trunc('month', created_at) ") }
  scope :exclude_user_ids, -> (ids) { where("user_id is not in (?)",ids) }
end

And then you would call it like:

@counts = NewsFeed.exclude_user_ids(['1','2']).group_by_month.count

This will give you:

{2014-01-01 00:00:00 UTC=>313, 2014-02-01 00:00:00 UTC=>3131}

Then you output (haml):

- @counts.each do |m|
  = "Month: #{m[0].strftime("%b")}, Count: #{m[1]}"

Which would result in:

Month: Jan, Count: 313
Month: Feb, Count: 3131

Answer 2

There are count and group statements available in active record so you could do something similar to

NewsFeed.count(:group=>"date_trunc('month', created_at)",:conditions=>"user_id NOT IN (?)",[exluded_ids])

Answer 3

Maybe this will work:

monthly_counts = NewsFeed.select("date_trunc('month', created_at) as month, COUNT(id) as total").where("user_id NOT IN (?)",[exluded_ids]).group("month")
monthly_counts.each do |monthly_count|
  puts "#{monthly_count.month} - #{monthly_count.total}"
end

Answer 4

http://railscasts.com/episodes/29-group-by-month

NewsFeed.where("user_id is not in (?)",[user_ids]).group_by { |t| t.created_at.beginning_of_month } => each {|month,feed| ...}

NewsFeed.select("*,MONTH(created_at) as month").where("user_id is not in (?)",[user_ids]).group("month") => ...