CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Eduardo Serrano
Eduardo Serrano

Reputation: 97

Why does the active records join method produce duplicate values?

I am making a simple query in rails using the join method but I am getting duplicated values. I want to understand the way this works and why I get the duplicates.

as an example: I have 10 users is the db with 2 pets each

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

This returns 2 of each user

Upvotes: 1

Views: 1538

Answers (1)

Alex
Alex

Reputation: 29820

This is how sql INNER JOIN behaves.

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

Pet has the user_id column, so for every pet that has a user a result is added.

We can see this database result (with user.id and pet.id selected)

>> 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}
   ]

By default rails just doesn't select any values from joined tables, it only does SELECT "users".* to give you a valid User object at the end.

>> 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"}
   ]

So, you only see duplicated results.

If you're trying to filter users who have pets use 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">
   ]

It you're trying to preload pets, use 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
   ]

For more details see 'Related' questions =>

and my other answer from yesterday https://stackoverflow.com/a/72005685/207090

Upvotes: 1

Related Questions