为什么活动记录连接方法产生重复的值?

Question

我正在使用连接方法在rails中进行简单查询，但我正在获得重复的值。我想了解这是如何工作的，为什么我得到了重复的。

为例:我有10个用户是数据库，每个有2个宠物

Users.joins(:pets).size # => 20

每个用户返回2个

Answer 1

这是sqlINNER JOIN的行为。

SELECT "users".* FROM "users" INNER JOIN "pets" ON "pets"."user_id" = "users"."id"

Pet有user_id列，所以对于每个有用户的宠物，添加一个结果。

我们可以看到这个数据库结果(使用user。我和宠物。id选择)

>> ActiveRecord::Base.connection.execute(User.select("users.id as user, pets.id as pet").joins(:pets).to_sql).to_a
(0.9ms)  SELECT users.id as user, pets.id as pet FROM "users" INNER JOIN "pets" ON "pets"."user_id" = "users"."id"
=> [
{"user"=>1, "pet"=>1},
{"user"=>1, "pet"=>2}
]        

默认情况下，rails只是不从连接表中选择任何值，它只做SELECT "users".*以在最后给您一个有效的User对象。

>> ActiveRecord::Base.connection.execute(User.joins(:pets).to_sql).to_a
(0.9ms)  SELECT "users".* FROM "users" INNER JOIN "pets" ON "pets"."user_id" = "users"."id"
=> [
{"id"=>1, "name"=>"User 1"},
{"id"=>1, "name"=>"User 1"}
]      

所以，你只能看到重复的结果。

如果你想过滤有宠物的用户，请使用distinct

>> User.joins(:pets).distinct
User Load (1.5ms)  SELECT DISTINCT "users".* FROM "users" INNER JOIN "pets" ON "pets"."user_id" = "users"."id"
=> [
#<User:0x00007f2c9ea235d8 id: 1, name: "User 1">
]

如果你想预加载宠物，使用includes

>> User.includes(:pets)
User Load (0.8ms)  SELECT "users".* FROM "users"
Pet Load (0.6ms)  SELECT "pets".* FROM "pets" WHERE "pets"."user_id" IN ($1, $2)  [["user_id", 1], ["user_id", 2]]
=> [
#<User:0x00007f2c9e6325f0 id: 1, name: "User 1">, 
#<User:0x00007f2c9e6324d8 id: 2, name: "User 2">  # <= user without pets
]

详情请参阅"相关"问题=>

和我昨天的其他答案https://stackoverflow.com/a/72005685/207090