ActiveRecord(Rails 4)确定查询中记录的索引 [英] ActiveRecord (Rails 4) determine index of record in query

问题描述

我要做的是确定销售人员在一个月内晋升到什么级别，只要赚取佣金的步骤尽可能少即可.我认为可以结合使用ActiveRecord和一些枚举方法，但是从本质上讲，我有一个模型，如下所示:

What I'm trying to do is determine what rank a sales person came in for a month as far as earning commission goes in the least amount of steps possible. I think it might be possible to do with a combination of ActiveRecord + some enumerable methods, but essentially I have a model that looks like the following:

class Employee < ActiveRecord::Base
  validates :commission, presence: true
end

commission只是一个整数.因此，为了使所有员工从获得的佣金中最高到最低，我们可以做一些琐碎的事情:

commission is just an integer. So in order to get all employees ordered from highest to lowest for commission earned, we can do something as trivial as:

Employee.order('commission desc')

我的问题是，假设我要查找的employee的ID为50.要了解他们在佣金方面所处的排名"，这是一种简单的方法.如果他们在列表的顶部，他们将获得#1的排名.如果它们在降序排列中排在最后，则它们的排名最终将是Employee.all.count

My question is, assume that the employee I'm looking for has an id of 50. What's an easy way of knowing what "rank" they are in as far as commission earned. If they are at the top of the list, they have earned a rank of #1. If they are last in the descending sort, their rank is ultimately the size of Employee.all.count

分享相同佣金金额的员工应排名相同.多名员工共享一个职位是正确的.

Employees who share the same commission amount should be ranked the same. Multiple employees sharing a rank is correct.

推荐答案

MySQL没有任何本机排名功能，因此您需要

MySQL doesn't have any native ranking functions, so you'll need to resort to the GROUP_CONCAT method to get this to work efficiently:

Employee.
  select('employees.*, FIND_IN_SET(commission, ranks) AS rank').
  joins(', (SELECT GROUP_CONCAT(DISTINCT commission, ORDER BY commission DESC) AS ranks FROM employees) ranks').
  order('commission desc')

您还可以使用常用的自联接方法，但是随着表的增长，性能可能会大大降低.

You can also use the self-join approach commonly used, but performance will likely degrade significantly as the table grows.

这篇关于ActiveRecord(Rails 4)确定查询中记录的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！