has_many :through when join table doesn't contain FK to both tables
- by seth.vargo
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, not Ruby?
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 do I get rails to build that subquery with ARel instead of having to return and use Ruby to loop and collect and such?
