如何按特定属性对ActiveRecord查询进行排序 [英] How to sort activerecord query by specific prority

问题描述

我正在使用rails 3和postrges。

I am using rails 3 and postrges.

我想按特定的优先级进行订购。

I would like order by a specific priority.

像这样的东西：

Assignment.order(priority: ['best', 'good', 'bad'])

，这将首先返回所有活动记录，依次为最佳，好，差

and this will return all activerecords first with 'best', then 'good', then 'bad'

我似乎找不到这样的东西。我不需要数组，它必须是activerecords。

I cannot seem to find anything like this. I do not need an array, it has to be activerecords.

推荐答案

顺序可以是任何SQL代码。您可以使用 CASE 语句将值映射到按照正确顺序自然排序的值。

Order can be any SQL code. You can use a CASE statement to map your values to values that naturally sort in the correct order.

Assignment.order("
    CASE
      WHEN priority = 'best' THEN '1'
      WHEN priority = 'good' THEN '2'
      WHEN priority = 'bad' THEN '3'
    END")

更好的是，您可以将此逻辑移至模型，以便从控制器调用更容易：

Even better, you could move this logic to the model so that it's easier to call from controllers:

class Assignment < ActiveRecord::Base
  ...
  def self.priority_order
    order("
        CASE
          WHEN priority = 'best' THEN '1'
          WHEN priority = 'good' THEN '2'
          WHEN priority = 'bad' THEN '3'
        END")
  end
end

然后，您只需调用 Assignment.priority_order 即可获取排序记录。

Then you can just call Assignment.priority_order to get your sorted records.

如果此列在视图中可排序，请向方法中添加方向参数：

If this column is sortable in the view, add a parameter to the method for direction:

def self.priority_order(direction = "ASC")
  # Prevent injection by making sure the direction is either ASC or DESC
  direction = "ASC" unless direction.upcase.match(/\ADESC\Z/)
  order("
      CASE
        WHEN priority = 'best' THEN '1'
        WHEN priority = 'good' THEN '2'
        WHEN priority = 'bad' THEN '3'
      END #{direction}")
end

然后，您将呼叫 Assignment.prior ity_order（params [：direction]）从控制器传递排序。

Then, you would call Assignment.priority_order(params[:direction]) to pass in the sorting from the controller.

这篇关于如何按特定属性对ActiveRecord查询进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！