sql - 带有ActiveRecord和Rails 3的复杂JOIN

我有以下模型:

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

我必须找到属于user是成员的组的所有帖子。我已经用这种方法做到了:

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

但它会产生无效的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

我想这样查询:

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

不使用原始SQL怎么办？

最佳答案

像这样的事情应该为您工作，尽管它需要混入一些原始SQL

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