Postgres的ORDER BY使用Rails活动​​记录在列表中值 [英] Postgres ORDER BY values in IN list using Rails Active Record

问题描述

我收到的用户ID（约1000在时间）排序收入的列表。我在我的系统中的数据库，而是收入列中是不存在的用户记录。我想要从我的系统的数据库的用户 在排序顺序列表中的好评。我试着做以下使用Active Record的预期，该记录将被检索的顺序相同的排序清单，但它不能正常工作。

I receive a list of UserIds(about 1000 at a time) sorted by 'Income'. I have User records in "my system's database" but the 'Income' column is not there. I want to retrieve the Users from "my system's database" in the Sorted Order as received in the list. I tried doing the following using Active Record expecting that the records would be retrieved in the same order as in the Sorted List but it does not work.

//PSEUDO CODE
User.all(:conditions => {:id => [SORTED LIST]})

我找到了答案，在下面的链接了类似的问题，但我不知道如何使用活动记录落实建议的解决方案。

I found an answer to a similar question at the link below, but am not sure how to implement the suggested solution using Active Record.

SQL ORDER BY的IN值列表

有没有其他办法做到这一点？

Is there any other way to do it?

请指导。

Shardul。

推荐答案

您链接到提供答案正是你需要的，你只需要code它在Ruby中以灵活的方式。

Your linked to answer provides exactly what you need, you just need to code it in Ruby in a flexible manner.

事情是这样的：

class User
  def self.find_as_sorted(ids)
    values = []
    ids.each_with_index do |id, index|
      values << "(#{id}, #{index + 1})"
    end
    relation = self.joins("JOIN (VALUES #{values.join(",")}) as x (id, ordering) ON #{table_name}.id = x.id")
    relation = relation.order('x.ordering')
    relation
  end
end

事实上，你可以很容易地把在一个模块并混入成任何需要它的ActiveRecord类，因为它使用了 table_name的和自它不与任何特定的类名来实现。

In fact you could easily put that in a module and mixin it into any ActiveRecord classes that need it, since it uses table_name and self its not implemented with any specific class names.

这篇关于Postgres的ORDER BY使用Rails活动​​记录在列表中值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！