CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-4
Justin
Justin

Reputation: 4940

Query to find random 5 users that current_user isn't following

I've done my due diligence and haven't been able to figure out this problem.

I'm trying to find a random set of 5 users, that (1) cannot be the current_user and (2) cannot already be followed by the current_user.

I'm trying to put this into the variable @users. My relationships are set up where Users have followed_users (which are the users they are following), and Users have followers.

User model

has_many :relationships, foreign_key: "follower_id", :dependent => :destroy
has_many :followed_users, through: :relationships, source: :followed
has_many :reverse_relationships, foreign_key: "followed_id",
                                  class_name: "Relationship",
                                   dependent: :destroy
has_many :followers, through: :reverse_relationships, source: :follower

Relationship model

belongs_to :follower, class_name: "User", touch: true
belongs_to :followed, class_name: "User", touch: true

Unfortunately, I'm terrible at queries so I'm not sure how to find this array of users. On top of a solution that one of you would implement, any resources where I can learn on my own how to do queries efficiently would be really appreciated.

If you need any more code to help solve the problem, please let me know.

Thanks

Upvotes: 1

Views: 47

Answers (1)

MrYoshiji
MrYoshiji

Reputation: 54882

This might work for you:

User.includes(:reverse_relationships)
    .where("#{Relationship.table_name}.follower_id <> :id", id: current_user.id)
    .where("users.id <> ?", current_user.id)
    .order("RAND()")
    .limit(5)

Not sure about the RAND() function, it depends on your DB.

Eventually replace the id <> :id with id NOT IN (?) to be able to pass more than just one id:

scope :some_other_users, lambda { |user_ids|
  user_ids = user_ids.kind_of?(Array) ? user_ids : [user_ids]
  includes(:reverse_relationships)
    .where("#{Relationship.table_name}.follower_id NOT IN (?)", user_ids)
    .where("users.id NOT IN (?)", user_ids)
    .order("RAND()")
    .limit(5)
}

query_getting_followed_user_ids = Relationship.where("follower_id IN (?)", [current_user.id]).select("relationships.followed_id").to_sql
User.where("id NOT IN (#{query_getting_followed_user_ids})")
    .where("id <> ?", current_user.id)
    .order("RAND()")
    .limit(5)

As you can see, I want to make a query on the result of an other query: Query-ception!

Upvotes: 2

Related Questions