CODEWITHSUNDEEP
sqlruby-on-railsactiverecordscopesql-order-by
Sean Magyar
Sean Magyar

Reputation: 2380

rails complex order_by with argument

I have a rails app. I would like to display user profiles ordered by the number of the common tasks they have with the current user. Every task has one assigner and one executor. The number should include both the executed_tasks and assigned_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 would be 8.

My main problem is that I don't know how to use the given user as arg for the 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

Upvotes: 0

Views: 306

Answers (1)

Hoang Phan
Hoang Phan

Reputation: 2166

Assuming that you want to execute this with a single SQL query to improve performance, you could 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

As the comment requests for taking into account the unrelating users, and limit to 6, it's a little bit trickier, since we use FULL_OUTER_JOIN.The edited function would 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

Upvotes: 1

Related Questions