Rails complex order_by with argument

Question

I have an app for rails. I would like to display user profiles sorted by the number of common tasks that they have with the current user. Each task has one assignor and one executor. The number must include both execute_tasks and assign_tasks for the same user. So, for example, if current_user assigned 5 tasks to User4 and User4, assigned 3 tasks to current_user, then this number will be 8.

My main problem is that I do not know how to use this user as arg for count. Should I do in the model somehow or when I set the instance variable (@users) in the controller?

task.rb

belongs_to :assigner, class_name: "User" belongs_to :executor, class_name: "User" scope :between, -> (assigner_id, executor_id) do where("(tasks.assigner_id = ? AND tasks.executor_id = ?) OR (tasks.assigner_id = ? AND tasks.executor_id = ?)", assigner_id, executor_id, executor_id, assigner_id) end 

user.rb

 has_many :assigned_tasks, class_name: "Task", foreign_key: "assigner_id", dependent: :destroy has_many :executed_tasks, class_name: "Task", foreign_key: "executor_id", dependent: :destroy 

Answer 1

Assuming you want to accomplish this with a single SQL query to improve performance, you can do something like:

 class User < ActiveRecord::Base def assigners Task.where(executor_id: id).select('assigner_id AS user_id') end def executors Task.where(assigner_id: id).select('executor_id AS user_id') end def relations_sql "((#{assigners.to_sql}) UNION ALL (#{executors.to_sql})) AS relations" end def ordered_relating_users User.joins("RIGHT OUTER JOIN #{relations_sql} ON relations.user_id = users.id") .group(:id) .order('COUNT(relations.user_id) DESC') end end 

Since the comment requests accounting for unrelated users and limits 6, this is a bit more complicated, since we use FULL_OUTER_JOIN . The changed function will be:

 def ordered_relating_users User.joins("FULL OUTER JOIN #{relations_sql} ON relations.user_id = users.id") .where.not(id: id) .group(:id) .order('COUNT(relations.user_id) DESC') .limit(6) end