SQL 使用 ActiveRecord 和 Rails 3 的复杂连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5592875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Complex JOIN with ActiveRecord and Rails 3
提问by whitered
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 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:
但它会产生效率低下的 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?
如何在不使用原始 SQL 的情况下执行此操作？
回答by jamuraa
You can get closer without changing your model at all, by removing the unused join from your call:
通过从呼叫中删除未使用的连接，您可以在不更改模型的情况下接近：
Post.joins(group: :memberships).where(memberships: { user_id: 1 })
compiles to SQL
编译成 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)
回答by Will Ayd
something like this should work for you, although it requires mixing in a little raw SQL
像这样的东西应该适合你，虽然它需要混合一些原始的 SQL
Post
.joins("INNER JOIN memberships ON memberships.group_id = posts.group_id")
.where(:memberships => {:user_id => current_user.id})
