CODEWITHSUNDEEP
ruby-on-rails
imderek
imderek

Reputation: 1316

Rails: group by day and one other column

class Visitor
  has_many :sessions
end

class Session
  belongs_to :visitor
  belongs_to :site
end

class Site
  has_many :sessions
end

I'd like to be able to get the number of visitors to a site for each day. Since a visitor is not directly associated with a site, but a session is, I need to get all sessions for a particular site, group by day, then group by visitor_id. Here's some sample data (ordered by created_at ASC):

visitor_id  site_id   created_at
6           3         2011-09-27
6           3         2011-09-27
7           3         2011-09-27
2           3         2011-09-29
7           3         2011-09-29

Ideally, I should see that on 09/27 there were 2 unique visitor, and on 09/29 there were also 2 unique visitors. I've tried this:

Session.group('date(created_at)').group('visitor_id').size

But I get this in return (which is not correct):

 # => {Tue, 27 Sep 2011=>3, Thu, 29 Sep 2011=>2}

Thanks guys!

Upvotes: 7

Views: 3849

Answers (3)

Kristian PD
Kristian PD

Reputation: 2695

@counts = Session.group('date(created_at), visitor_id').count

@counts.each do |(date, visitor), count|
  puts "#{visitor.name} visted the site #{count} times on #{date}"
end

Upvotes: 8

klochner
klochner

Reputation: 8125


@counts = Session.group('date(created_at)').
                  select("count(distinct sessions.visitor_id) as total, sessions.created_at").
                  all

@counts.each do |count|
  puts "#{count.total} on #{count.created_at.strftime('%m/%d/%y')}"
end

Upvotes: 0

Kyle Macey
Kyle Macey

Reputation: 8154

Session.where(:created_at => desired_date).group('visitor_id').count

No console to test on here, sorry.

Upvotes: 0

Related Questions