PostgreSQL ActiveRecord Query Optimization

Question

I'm new to RoR and I need a query that returns the same instances as the following definition of queue :

@all = User.all
@membersOfActivity = User.where(id: User.joins(:memberships).where("activity_id = ?", session[:current_activity_id])) 
@usersIAlreadyLiked = User.where(id: User.joins(:likes).where("activity_id = ? AND activity_likes_user IS NOT NULL", session[:current_activity_id])) 
@notWanted = @membersOfActivity + @usersIAlreadyLiked 
@queue = @all - @notWanted

However, I understand how inefficient the query I just wrote is for it downloads all the Users first to then select the complement. Do you have any idea on how to make it more efficient and directly select the complement of @notWanted? I tried several queries but none of those worked. If you think you have a better solutions please let me know! Thanks!

EDIT : Each user has many memberships, that connect a user with an activity. So each activity has also many membership. Each membership has a user_id and an activity_id.

Each user has many likes, and each like connects a user and an activity. Therefore each activity has also many likes. A like has a user_id, activity_id, user_liked_activity, and activity_liked_user.

user_liked_activity is NULL if the user did not express an opinion about the activity, TRUE is the user liked the activity, and FALSE if the user disliked the activity. Viceversa for activity_liked_user.

Pertinent question, I apologize! You'll find a brief explanation on the edit. @moveson — Giulio Colleluori
– Giulio Colleluori, Commented Mar 4, 2018 at 4:39

moveson · Accepted Answer · 2018-03-04 07:17:52Z

1

Here's a Rails 5 solution, using not syntax. At first glance, this appears to be similar to your original solution, but this runs only one query, built from the separate excludable categories (members and already_liked).

class User < ApplicationRecord
  has_many :likes
  has_many :memberships

  def self.queue(current_activity_id)
    members = User.joins(:memberships).where(memberships: {activity_id: current_activity_id})
    already_liked = User.joins(:likes).where(likes: {activity_id: current_activity_id, activity_liked_user: [true, false]})

    where.not(id: members).where.not(id: already_liked)
  end
end

You will call this from your controller like so:

@queue = User.queue(session[:current_activity_id])

As I understand the problem, for the current session activity, we want a query that will return all users except those that belong to one of two categories: (1) Those who are members of the activity, represented by users having a membership linked to the current activity, and (2) those who already have a "liked" rating from the activity, represented by users having a "like" linked to the current activity for which activity_likes_user is either true or false (but not nil).

We establish ActiveRecord relations for each category, but we don't call anything that would actually trigger a query. Category 1 is members, and Category 2 is already_liked.

Now we build a query on the User model (this is implied, because we are calling from within a class method on the User class) asking for users where id is not one of the members and is not one of the already_liked.

There may be a more elegant solution, but this does work, and I believe it handles edge cases properly, though you should build tests to verify this.

edited Mar 4, 2018 at 7:17

answered Mar 4, 2018 at 4:52

moveson

5,2231 gold badge17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Giulio Colleluori Over a year ago

That's actually what I tried initially but I get the following error : "Relation passed to #or must be structurally compatible. Incompatible values: [:includes, :references]"

Giulio Colleluori Over a year ago

I get 'PG::UndefinedColumn: ERROR: column activities.activity_likes_user does not exist', I think it is from the last part : 'activities: {activity_likes_user: true}'. Thank you for your answer though, I'm still trying to understand the syntax and what your query actually does so I'm having some problems debugging it :/ @moveson

Giulio Colleluori Over a year ago

That works, except for we also don't want the users where activity_likes_user: false (we only want the ones that are null) so maybe we do need the or?@moveson

Giulio Colleluori Over a year ago

Your understanding of what the query is almost correct, expect (2) should include also the ones that have been disliked by the activity (activity_likes_user = false). Now does [true, false] the syntax for "or" (true or false)? The query seems right to me but it's not returning the right values, it's returning different values from the one in my question. Thanks again for the help so far!

moveson Over a year ago

I've completely revamped the solution. I built a Rails 5 app and tested it, and it appears now to be working properly.

|

Collectives™ on Stack Overflow

PostgreSQL ActiveRecord Query Optimization

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related