Using Rails 3.2.21, postgresql db
I need to get the first 3 comments for a set of posts, and I want to get them all in a single query.
class Post < ActiveRecord::Base
has_many :comments
has_many :comments_latest3, :class_name => 'Comment',
:order => 'created_at DESC', :conditions => 'latest_rank <= 3',
:select => 'comments.*,
ROW_NUMBER() OVER(PARTITION BY discussion_id ORDER BY created_at DESC)
AS latest_rank'
...
This generates
select *, latest_rank from comments where discussion_id in ... and latest_rank<=3
Which is invalid because latest_rank doesn't exist yet. Also I can't use the window function directly in the where clause. So what I need is
select *
from (select *, latest_rank from comments where discussion_id in ...)
where latest_rank<=3
Not sure how to get this done, finder_sql doesn't seem to be working for me.
Aucun commentaire:
Enregistrer un commentaire