has_many :through when join table doesn't contain FK to both tables

I have a structure that isn't really a has_many :through example, but I'd like it to behave like one:

# user.rb
belongs_to :blog
has_many :posts

# post.rb
belongs_to :user

# blog.rb
has_many :users
has_many :posts, :through => :users  # this obviously doesn't work becase
                                     # both FKs aren't in the blogs table

I want to get ALL posts for a blog in an array. I'm aware that I can do this with Ruby using each or getting fancy with collect, but I'd like to let SQL do the work.

Can someone explain how I can set up my models in a way that lets me call @blog.posts using SQL, and Ruby, and explain which way is "better"?

Edit:

I know in SQL I can write something like:

SELECT * FROM posts WHERE posts.user_id IN (
  SELECT users.id FROM users WHERE users.blog_id = 7
)

which obviously shows two queries are needed. I don't think this is possible with a join, but I'm not totally sure. It's obvious that a subquery is needed, but how 开发者_如何学Cdo I get rails to build that subquery with ARel instead of having to return and use Ruby to loop and collect and such?

Try putting this in your Post model

scope :for_blog, lambda{|blog|
  where("user_id IN (SELECT u.id FROM users u WHERE u.blog_id = ?)", blog.id)
}

This will allow you to call Post.for_blog(@blog). The objects returned by SQL must be cast as Posts (that's why I'd put the scope in the Post model).

Then, in your Blog model, put

def posts
  Post.for_blog(self)
end

By defining the virtual attribute, you should now be able to access a blog's posts via @blog.posts.