给定以下架构
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我如何查询给定的页面,所有用户最近的页面浏览量按created_at日期排序。每个用户一行,仅显示其最近的视图。
发布于 2010-10-11 02:48:35
您需要具有特定PageViews的每个user_id的最大created_at的page_id。这是此查询在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在Rails 3中,你可以这样写:
Page.first.page_views.group(:user_id).select('max(created_at) AS last_visit, page_views.*').order('last_visit DESC')发布于 2010-10-11 02:55:13
试试这个:
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现在
page.recent_viewershttps://stackoverflow.com/questions/3901558
复制相似问题