How do I create a scope on an associated closure_tree model?

Question

I have two types: blogs and posts. Post uses the closure_tree gem (an acts_as_tree variant) to allow posts nested under posts. Also, each blog has_many posts.

class Post < ActiveRecord::Base
  acts_as_tree
end

Given a set of blogs (by the same author, say), I would like to get all the posts in those blogs as a scope (i.e., as an ActiveRecord::Relation not as an array).

Something like:

Blog.all_posts_by('john')

I have tried two things so far:

Approach #1, using arrays (not scopes), is as follows:

class Blog
  has_many :posts
  def self.all_posts_by author_name
    self.where(author_name: author_name).map(&:posts).flatten.map(&:self_and_descendants).flatten
  end
end

But I would like to have a scope, as the array map approach may not perform well with large data sets.

Approach #2: This approach yields a true scope, but using sql unions and sql strings:

class Blog
  has_many :posts
  def self.all_posts_by author_name 
    post_collections = []
    Blog.where(author_name: author_name).each do |blog|
      post_collections = blog.posts.map(&:self_and_descendants)
    end
    posts_sql = ""
    post_collections.each do |post_collection|
      posts_sql << "( #{post_collection.to_sql} ) union "
    end
    final_sql = posts_sql.chomp('union ')
    result = Post.from("
        (
            #{final_sql}
        ) #{Post.table_name}
    ").distinct
  end
end

This might work, but I am looking for a better way, hopefully using some available scope magic.

Answer 1

If you store the blog_id on the nested posts as well and not only on the root level posts you can do the following and don't need to query for descendants:

class Blog
  has_many :posts
  def self.all_posts_by author_name
    self.where(author_name: author_name).includes(:posts).map(&:posts).flatten
  end
end

The includes statement eager loads all posts from the database which is much faster than sequentially loading them. http://www.spritle.com/blogs/2011/03/17/eager-loading-and-lazy-loading-in-rails-activerecord/

UPDATE:

If you want to return them as a scope I think it would be the best to actually have this on the Post model, since this makes a lot more sense:

class Post
  belongs_to :blog

  def self.all_by author_name
    self.joins(:blog).where(blog: [name: author_name])
  end 
end

Note that again this really only works if you set the blog_id on all nested posts.

If it is really a high performance app i would also suggest you to go for a search index engine like elasticsearch, since it performs really well in this type of scenarios, even if you dont have any search strings. This would allow you to build even more filters like this and combine them, but it also brings more complexity to the apps infrastructure.