1

I have some n + 1 queries that are making the load times for my website very slow. It's a social media website just like Facebook and the source of n + 1 queries are friends. Details -

Friendships table has 3 columns - User_id (user who send the request), friend_id (user who got the request) and pending which is a boolean to indicate if the friendship was accepted or not.

Friend requests are implemented in my User model using 

def friend_requests
  friend_ids = Friendship.where('friend_id = ? AND pending = true', self.id).all
end

I specifically need the friendship id because I'll need to update the pending boolean if the user wants to accept or reject the request.

The Friendship model has a belongs_to => friend association declared.

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

The source of n+1. For the view, when I want to fetch the friend requests received by a user, I also want to include the name and profile picture of the user who sent the request. It looks some thing like this - 

json.friend_requests @user.friend_requests.includes(friend: :name) do |friendship|
  json.extract! friendship, :id, :user_id
  json.name User.find(friendship.user_id).name # n + 1
  json.profile_pic User.find(friendship.user_id).profile_pic # n + 1
end

I originally had some doubts about the syntax of includes(friend: :name) but I've tried all the permutations mentioned in this thread but it gives me

Association named 'name' was not found on User; perhaps you misspelled it?

Which is right since name is an attribute of User and not an association.

The only way so solve this, that I can think of, is my changing my friend_requests table to a find_by_sql query which looks like this - 

SELECT f.id, f.user_id, f.friend_id, f.pending, users.name, users.profile_pic
FROM friendship AS f
JOIN users ON users.id = friendship.friend_id
WHERE f.friend_id = ? AND pending = true

It'll give me the name and profile_pic of the user, but I don't wanna do that since it looks dirty but more importantly I wanna know if there is a better and more intelligent way to do this.

Community
  • 1
  • 1
satnam
  • 1,457
  • 4
  • 23
  • 43

1 Answers1

2

From the API documentation on #includes:

Specify relationships to be included in the result set.

Keyword is relationships: you specify a whole relationship to be included, not a field.

You should try this:

@user.friend_requests.includes(:friend) do |friendship|

  # whatever you need before

  # access the friend's name through the friend relationship
  # this should not require another query as the friend object was
  # preloaded into friendship using #includes
  friendship.friend.name

  # whatever you need after

end

Edit: actually, this will trigger 2 SQL queries, a first to get the user's friend_requests, then a second to get all friend relations at once. The second query being of the form

SELECT "users".* FROM "users" WHERE "users"."id" IN (/*LIST OF IDs*/)

using all the ids found in the first friend_requests query.

If you want only one query to be fired, you should try a JOIN, probably like this:

@user.friend_requests.joins(:friend).includes(:friend)
David Stosik
  • 879
  • 9
  • 17