轨道复杂ORDER_BY与参数 [英] rails complex order_by with argument

问题描述

我有一个Rails应用程序。我想显示有序通过他们与当前用户的共同任务数的用户配置文件。每个任务都有一个分配器和一个执行者。的数量应既包括executed_tasks和assigned_tasks对于相同的用户。因此，例如，如果分配5任务用户4和用户4 CURRENT_USER分配3任务CURRENT_USER那么这个数字将是8。

我的主要问题是，我不知道如何使用给定用户为ARG为计数。我应该做的模型以某种方式或当我在控制器中设置的实例变量（@users）？

task.rb

  belongs_to的：分配器，将class_name：用户
belongs_to的：执行者，将class_name：用户

适用范围：之间， - ＆GT; （assigner_id，executor_id）办
  其中（（tasks.assigner_id =？AND tasks.executor_id =？）OR（tasks.assigner_id =？AND tasks.executor_id =？），assigner_id，executor_id，executor_id，assigner_id）
结束
 

user.rb

 的has_many：assigned_tasks，将class_name：任务，foreign_key：assigner_id，取决于：摧毁
的has_many：executed_tasks，将class_name：任务，foreign_key：executor_id，取决于：摧毁
 

解决方案

假设你想用一个SQL查询执行此以提高性能，你可以这样做：

 类用户的LT;的ActiveRecord :: Base的
  高清assigners
    Task.where（executor_id：ID）。选择（assigner_id AS USER_ID）
  结束

  高清执行人
    Task.where（assigner_id：ID）。选择（executor_id AS USER_ID）
  结束

  高清relations_sql
    （（＃{assigners.to_sql}）UNION ALL（＃{executors.to_sql}））AS关系
  结束

  高清ordered_relating_users
    User.joins（RIGHT OUTER JOIN＃{relations_sql} ON relations.user_id = users.id）
      。集团（：ID）
      .order（'COUNT（ID）DESC）
  结束
结束
 

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

解决方案

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(id) DESC')
  end
end

这篇关于轨道复杂ORDER_BY与参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！