Rails - eliminate n+1 queries occuring as a result of fetching the number of results

Question

I'm making a social media website which is similar to facebook and n+1 queries are slowing me down in the following situation -

Schema of my friends table - It has 3 columns, user_id (the user who sends the request), friend_id (the who receives the request) and pending (a boolean that indicates if a request has been accepted yet. If a user rejects a request then the entry is eliminated from the table.

Users have a friends method that looks like this -

def friends
  user_ids = Friend.where(
    '(user_id = :id OR friend_id = :id) AND pending = false',
     id: self.id
  ).pluck(:user_id, :friend_id)
   .flatten
   .uniq
   .reject { |id| id == self.id }

  User.where(id: user_ids)
end

I realize the method is not great since it executes 2 SQL queries. Any suggestions to improve that are welcome, but I want another problem solved in this question.

For my view, I want to get all the friends of a user and also the number of friends that each of the friends have. This'll enable me to implement a friend list which lists a friend, and along with it, the number of friends that friend has.

To ugly way of doing that is -

json.friends @user.friends do |friend|
  json.extract! friend, :id, :name, :profile_pic
  json.number_friends friend.friends.length
end

But obviously that's not efficient at all.

Is there a way by which I can fetch the friends of a user, and the number of friends that each of the friends have in a single query?

or is caching the number of friends for each user my only solution?

EDIT - A Friend model has a couple of belongs_to associations, i.e. belongs_to => user and belongs_to => friend

belongs_to :user,
    class_name: "User",
    foreign_key: :user_id,
    primary_key: :id

  belongs_to :friend,
    class_name: "User",
    foreign_key: :friend_id,
    primary_key: :id

A User model gets his friends using the method written above in the post

Answer 1

Another way to structure this friendship, and there are quite a few really, is to optimise it for reads at the expense of optimisation for changes by using two rows in a friendship table to represent each friendship-pair.

When adding a friendship, which is basically (:user_id, :friend_id, :pending) you also add (:friend_id, :user_id, :pending) -- the same pair of user_ids but the other way around.

On destruction of one you destroy the other, and on acceptance of one you accept the other. This is pretty trivial through callbacks.

This allows the friendship to always be expressed as a simple association:

class User
  has_many :friendships
  has_many :friends    , :through => :friendships
end

class Friendship
  belongs_to :user
  belongs_to :friend
end

Your association is eager-loadable, and expressed as:

@user.friends

So you move complexity and inefficiency into a less-rarely executed operation in order to optimise a very frequent operation.

Answer 2

This is how I would do it.

SQL Fiddle Demo

This should be in a postgresql function receiving parameter the USER_ID. But because in plain sql you can't use variable I use a litle trick in first CTE to define ID parameter instead.

Second CTE check all the rows where USER_ID is part of relatioship. And use a CASE to show the friend.

Last one return the friend count of each friend of USER_ID

WITH p as (
   SELECT 2 as ID
), allFriend AS (    
    SELECT CASE 
              WHEN f.user_id = p.id THEN f.friend_id
              ELSE f.user_id
          END myFriend
    FROM friends f, p
    WHERE f.user_id = p.ID or f.friend_id = p.ID
    and f.pending = 0
)
SELECT myFriend, Count(*)
FROM allfriend f 
inner join friends f1 
  on  f.myFriend = f1.user_id 
  OR  f.myFriend = f1.friend_id 
    WHERE f1.pending = 0
GROUP BY myFriend