JOIN complex with ActiveRecord and Rails 3

Question

I have the following models:

class User < ActiveRecord::Base has_many :memberships has_many :groups, :through => :memberships end class Group < ActiveRecord::Base has_many :memberships has_many :users, :through => :memberships end class Membership < ActiveRecord::Base belongs_to :user belongs_to :group end class Post < ActiveRecord::Base belongs_to :group end 

I need to find all posts that belong to groups in which the user is a member. I did this with this method:

 @post = Post.joins(:group => {:memberships => :user}).where(:memberships => {:user_id => current_user.id}) 

but it creates inefficient SQL:

 SELECT "posts".* FROM "posts" INNER JOIN "groups" ON "groups"."id" = "posts"."group_id" INNER JOIN "memberships" ON "memberships"."group_id" = "groups"."id" INNER JOIN "users" ON "users"."id" = "memberships"."user_id" WHERE "memberships"."user_id" = 1 

I want to make a query like this:

 SELECT posts.* FROM posts INNER JOIN memberships ON memberships.group_id = posts.group_id WHERE memberships.user_id = 1 

How can I do this without using raw SQL?

Answer 1

something like this should work for you, although it does require mixing in a bit raw SQL

 Post.joins("INNER JOIN memberships ON memberships.group_id = posts.group_id").where(:memberships => {:user_id => current_user.id}) 

Answer 2

You can get closer without changing your model at all by removing an unused connection from your call:

 Post.joins(group: :memberships).where(memberships: { user_id: 1 }) 

compiled in SQL

 SELECT "posts".* FROM "posts" INNER JOIN "groups" ON "groups"."id" = "posts"."group_id" INNER JOIN "memberships" ON "memberships"."group_id" = "groups"."id" WHERE ("memberships"."user_id" = 1)