How to combine different classes from an STI table into a single result set using ActiveRecord?

Question

We're building an application that creates group pages similar to Facebook's group pages. Someone can post to a page and that post can have replies. Since they have very similar properties, the posts and the replies are combined into the same STI table

class Page < ActiveRecord::Base
  has_many :posts
  has_many :replies, through: :posts
end

class BasePost < ActiveRecord::Base
  ...
end

class Post < BasePost
  belongs_to :page
  ...
end

class Reply < BasePost
  belongs_to :post
  ...
end

I would like to be able to get page.posts_and_replies

In order to find things like the "most liked posts or replies" for a page, we would have to combine the posts and the replies so that we can get result sets such as:

top_messages = page.posts_and_replies.order_by('total_likes DESC').limit(10)

It's tricky to get posts and replies out together

To order posts_and_replies as a single result set though we would normally need to query them based on a single join:

class Page < ActiveRecord::Base
  has_many :posts
  has_many :replies, through: :posts
  has_many :posts_and_replies, class_name: 'BasePost'
end

# ideally we could then do queries such as
most_recent_messages = @page.posts_and_replies.order('created_at DESC').limit(10)

We can't do this though because page_id only exists on posts. 'replies' reference the page only through the Post they belong to. So this join gives us the Posts but not the Replies.

---

Possible solutions:

Denormalize the data...

We could duplicate the page_id onto the Reply as well as the Post but I would really like to avoid doing that if at all possible since denormalized data tends to end in tears.

Use a custom finder_sql

We could use custom finder sql to get the Posts and the Replies separatel and then combine them:

class Page < ActiveRecord::Base
  has_many :posts
  has_many :replies
  has_many :posts_and_replies, class_name: 'BasePost', finder_sql: Proc.new{ 
    "select base_posts.* from base_posts 
      where page_id = #{self.id}
    UNION
      select 
      base_posts.*
      from base_posts left join base_posts as head_posts
      on base_posts.post_id = head_posts.id
      where head_posts.page_id = #{self.id}"
  }
end

custom finder_sql works but won't work with association extensions

The above finder_sql does in fact work however none of the association extensions work. Whenever I attempt to use an association extension (such as .where) it falls back to the built in finder_sql:

This works

 Page.find(8).posts_and_replies
 => select base_posts.* from base_posts 
      where page_id = 8
    UNION
      select 
      base_posts.*
      from base_posts left join base_posts as head_posts
      on base_posts.post_id = head_posts.id
      where head_posts.page_id = 8

*however this falls back to the incorrect finder_sql*

  Page.find(8).posts_and_replies.where('total_likes > 1')
  => select "base_posts".* from "base_posts" where "base_posts"."page_id" = 8 
     and (total_likes > 12)

For some reason, the association extensions don't use the correct finder_sql

How can you make the association extensions respect the stated finder_sql?

The problem appears to have boiled down to the association extensions not properly using the finder_sql. Is there any way to enforce this?

Answer 1

The easiest way to go about this might be to use some named scopes, and avoid the associations entirely. This is a slightly hacky way to go about it, but might give you what you are looking for - and I'm not sure Rails associations are really going to give you the behaviour you desire. Also, note below that I'm going to be using ActiveRecord's safe parameter interpolation - you should never do string interpolation yourself into SQL statements - it summons evil spirits to come and infest your database when you aren't looking.

First off, you're going to define a named scope in your BasePost model that does the following:

class BasePost < ActiveRecord::Base

  scope :popular_posts_for_page, lambda { |page_id|
    where("base_posts.page_id = ? OR parent_posts.page_id = ?", page_id, page_id).
     joins("left outer join base_posts parent_posts on parent_posts.id = base_posts.post_id").
     order("total_likes DESC")
  }

end

Also of benefit here is that you aren't going to get back an actual list just yet, only a scope proxy. If you want, you can now chain your limit or whatever statements you want onto that.

If you want it to be accessible on your Page model, one way to do this is:

class Page < ActiveRecord::Base

  def popular_posts_and_replies
    BasePost.popular_posts_for_page(self.id)
  end

end

Since you previously defined this as a named scope, and the scope will return a scoping object, not an actual list of results - it will be have so crazily close to an association that the difference should be invisible. For example:

my_page.popular_posts_and_replies.limit(10)

Should do exactly what you expect.

I apologize for any mistakes in my SQL.