Group by with counts

rubyonrails-talk
1

Hi all,

I have the following data columns in my table:

occurred_at, name 2012-01-01, Ryan 2012-01-01, Ryan 2012-01-01, Mark 2012-01-01, Ryan 2012-01-01, Paul 2012-01-01, John 2012-02-01, Ryan 2012-02-01, Mark 2012-02-01, Ryan 2012-02-01, Mark 2012-02-01, Paul 2012-02-01, Kevin 2012-03-01, Ryan 2012-03-01, Gary 2012-03-01, Ryan 2012-03-01, Mark 2012-03-01, Paul 2012-03-01, Kevin

What I'm trying to do is Group the names by date and add a count and output this to a json

{ date: 2012-01-01, ryan: 3, mark: 1, paul: 1, john: 1 }

I have the following code so far:

# user controller def index   @users = User.show_data   render :json end

# user model

def self.show_data(start = 14.months.ago)     total_users = users_by_month(start)     (start.to_date..Date.today).map do |date|       {         occurred_at: date,         total_users[date].name.to_sym: total_users[count],       }     end   end

def self.users_by_month(start)     users = where(occurred_at: start.beginning_of_day..Time.zone.now )     users = users.group("date(occurred_at)")     users = orders.select("occurred_at, count(name) as user_name")     users.each_with_object({}) do |user, names|        names[user.occurred_at.to_date] = user.user_name     end   end

Thanks in advance,

Ryan

2

def self.users_by_month(start)

users = where(occurred_at: start.beginning_of_day..Time.zone.now )

users = users.group("date(occurred_at)")

users = orders.select("occurred_at, count(name) as user_name")

users.each_with_object({}) do |user, names|

   names[user.occurred_at.to_date] = user.user_name

end

end

I think you’re overcomplicating a little - if you do User.where(…).group(‘date(occurred_at), name’).count then you should get back a hash of where the keys are [date,name] pairs and the values are the counts

Fred