CODEWITHSUNDEEP
ruby-on-railsjoininclude
Charley Hine
Charley Hine

Reputation: 85

Rails join or includes?

I've got a User model/table and a Friend model/table. The users table has a uid column that stores a user's Facebook uid. The friends table stores their Facebook friends (associated by a user_id column).

How can I query all of a specific user's friends who have an account in the users table? In other words - I want the user models returned for user 13's friends who have signed up (exist in the users table).

Upvotes: 1

Views: 91

Answers (3)

Mike Szyndel
Mike Szyndel

Reputation: 10592

My models for same problem

class User < ActiveRecord::Base
    has_many :friendships
    has_many :friends, through: :friendships
    # ...
end

class Friendship < ActiveRecord::Base
    belongs_to :user
    belongs_to :friend, :class_name => 'User', :foreign_key => 'friend_id', :primary_key => 'facebook_id'
end

Upvotes: 1

Martin M
Martin M

Reputation: 8668

If I have the models:

class User < ActiveRecord::Base
  attr_accessible :name, :uid
  has_many :friends
end

class Friend < ActiveRecord::Base
  attr_accessible :uid
  belongs_to :user
  belongs_to :account, foreign_key: 'uid', primary_key: 'uid', class_name: 'User'
end

then I can do:

u=User.create(name: 'Alice', uid: 'fb1')
u2=User.create(name: 'Bob', uid: 'fb2')
u.friends.create(uid: 'fb2') # add Bob as friend
u.friends.create(uid: 'fb9') # add someone as friend

Now

u.friends # gives all friends
  Friend Load (0.3ms)  SELECT "friends".* FROM "friends" WHERE "friends"."user_id" = 4
=> [#<Friend id: 3, user_id: 4, uid: "fb2">, #<Friend id: 4, user_id: 4, uid: "fb9">]

u.friends.joins(:account) # only friends having an account
  Friend Load (0.2ms)  SELECT "friends".* FROM "friends" INNER JOIN "users" ON "users"."uid" = "friends"."uid" WHERE "friends"."user_id" = 4
=> [#<Friend id: 3, user_id: 4, uid: "fb2">]

while includes doesn't do an inner join and gives all friends:

u.friends.includes(:account)
  Friend Load (0.2ms)  SELECT "friends".* FROM "friends" WHERE "friends"."user_id" = 4
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."uid" IN ('fb2', 'fb9')
=> [#<Friend id: 3, user_id: 4, uid: "fb2">, #<Friend id: 4, user_id: 4, uid: "fb9">]

Upvotes: 0

Michael Lawrie
Michael Lawrie

Reputation: 1554

Assuming you the user of interest is referenced by @user:

User.joins("inner join friends on friends.uid = users.uid")
.where("friends.user_id = ?", @user.id)

There is probably a more "railsy" way to do this that makes better use of arel.

Upvotes: 0

Related Questions