whitered whitered - 1 year ago 70
SQL Question

Complex JOIN with ActiveRecord and Rails 3

I have the following models:

class User < ActiveRecord::Base
has_many :memberships
has_many :groups, :through => :memberships

class Group < ActiveRecord::Base
has_many :memberships
has_many :users, :through => :memberships

class Membership < ActiveRecord::Base
belongs_to :user
belongs_to :group

class Post < ActiveRecord::Base
belongs_to :group

I have to find all posts that belong to groups where user is a member. I have made it with this method:

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

but it produces unefficient 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 Source

something like this should work for you, although it requires mixing in a little raw SQL

Post.joins("INNER JOIN memberships ON memberships.group_id = posts.group_id").where(:memberships => {:user_id => current_user.id})
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download