Creating a friendship suggestion query that spans 3 Models

Question

I could use help with the following query which is for suggesting friends to a user

I have three models:

User (id, fb_id, org_id)
FbFriends (user_id, fb_id, org_id)
Friendships (user_id, friend_id)

FbFriends is caching the user's friends on Facebook. Friendships is a friendship on my app.

Goal, Return Users based on the following conditions:

Sorted by the User's org_id
Filter out anyone if their is already a Friendship record between the two users.

Is it possible to return @users based on the above with one query? Or do I need to build this with multiple queries?

Thanks

Adding sample output would help, I don't think the query itself is difficult, but I am not 100% sure what you are looking for. Is it simply a list of org_id, user_id, <potential new friends>? — Sparky
I'm look for an output of Users. A list of users from the user table based on the conditions above. Is that clear? — Rachela Meadows
Do you want to see users in the user table with no "friends" in either FBfriends or Friendships? Or do you want to see per user, a list of possible friends (other users they are not already friends with?) — Sparky
Sparky, this should help. Does it? ;;; Sorry about that. Given a User. I first want all the users on my app who are their FB Friends. This is provided with the FbFriends model. I'd like that sorted by org_id, so the items at the top are in the same org as the current_user. Then I want to remove all users from that list that are already the current_users friends on my app (friendships). Does that help — Rachela Meadows

Jordan Running Jordan Running · Accepted Answer · 2011-12-20T22:03:45

In SQL you'd do it like this:

SELECT * FROM users
JOIN friendships friendships_l ON users.id = friendships.user_id
JOIN friendships friendships_r ON users.id = friendships.friend_id 
WHERE {{some_user_id}} <> users.id
  AND {{some_user_id}} <> friendships_l.friend_id,
  AND {{some_user_id}} <> friendships_r.user_id
ORDER BY users.org_id

In ActiveRecord:

User.
  joins('JOIN friendships friendships_l ON users.id = friendships_l.user_id').
  joins('JOIN friendships friendships_r ON users.id = friendships_r.friend_id').
  where(%[ :uid <> users.id AND
           :uid <> friendships_l.friend_id AND
           :uid <> friendships_r.user_id
         ],
         { :uid => some_user.id }
  ).
  order('users.org_id').all

Creating a friendship suggestion query that spans 3 Models

1 Answers