Reputation: 1042
given the following schema
class User
has_many :page_views
#[id]
end
class Page
has_many :page_views
#[id]
end
class PageView
belongs_to :user
belongs_to :page
#[id, created_at, updated_at]
end
How can i query for a given page, all users most recent page views in order by created_at date. One row per user, showing only their most recent view.
Upvotes: 0
Views: 262
Reputation: 64373
Try this:
class Page
has_many :page_views
has_many :recent_page_views, :class_name => "PageView",
:joins => "INNER JOIN (
SELECT a.id, COUNT(*) AS ranknum
FROM page_views AS a
INNER JOIN page_views AS b ON a.user_id = b.user_id AND
a.page_id = b.page_id AND
a.created_at <= b.created_at
GROUP BY a.id
HAVING COUNT(*) = 1
) AS d ON (page_views.id = d.id)",
:order => "page_views.created_at"
has_many :recent_viewers, :through => :recent_page_views,
:source => :user, :order => "page_views.created_at"
end
Now
page.recent_viewers
Upvotes: 1
Reputation: 3816
You want the PageViews with the maximum created_at for each user_id for a particular page_id. This is a representation of this query in SQL:
SELECT max(created_at) AS last_visit, page_views.*
FROM page_views
WHERE (page_id = 1)
GROUP BY user_id
ORDER BY last_visit DESC
In Rails 3, you can write this like so:
Page.first.page_views.group(:user_id).select('max(created_at) AS last_visit, page_views.*').order('last_visit DESC')
Upvotes: 1