How to write a query on rails that returns a monthly number?

Question

take the standard model NewsFeed (id, user_id)

How can I request the number of entries per month in the NewsFeed model and then exclude multiple user_id?

Results will result in:

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

Is there an easy way to do this using rails, or do you need to write a query for each month?

thanks

Answer 1

There are counting and group statements in the active record so you can do something similar to

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

Answer 2

In Rails 4, the way to do this is to create areas 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 name it like this:

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

This will lead to:

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

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") => ...