mardi 25 août 2015

Filter by windows function in rails has_many relationship

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