在PostgreSQL中模拟MySQL的ORDER BY FIELD()函数

Question

第一次尝试使用PostgreSQL，之前一直使用MySQL。在我们的Rails应用程序中，有几个位置使用类似如下的SQL语句：

SELECT * FROM `currency_codes` ORDER BY FIELD(code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD') DESC, name ASC

很快就发现PostgreSQL不支持/允许这个。有人知道如何在PostgreSQL中模拟这种行为，还是必须将排序拉出来放到代码中？

- Peer Allan

---

请问需要翻译的内容是："It might be useful to explain what you want to achieve. As hard to imagine as it is - not everybody knows MySQL :)" 吗？ 如果是的话，我的翻译如下：解释你想要实现什么可能会很有用。虽然很难想象，但并不是每个人都知道MySQL :) - user80168

很好的观点，Depesz！基本上，我们需要的是自定义排序顺序。FIELD函数允许您创建自定义集合以进行排序。 - Peer Allan

现在在Rails中，您可以考虑使用#in_order_of https://github.com/rails/rails/pull/42061 - João Souza

Answer 1

啊，gahooa差点就成功了：

SELECT * FROM currency_codes
  ORDER BY
  CASE
    WHEN code='USD' THEN 1
    WHEN code='CAD' THEN 2
    WHEN code='AUD' THEN 3
    WHEN code='BBD' THEN 4
    WHEN code='EUR' THEN 5
    WHEN code='GBP' THEN 6
    ELSE 7
  END,name;

- Greg Smith

---

当使用DISTINCT时，这种方法无效。在这种情况下还有其他的想法吗？ - Corey

我不确定为什么这个有效，但我找到了一个替代方案。如果你想按照 j、a、k、e 的顺序得到结果，那么你可以使用 order by id=e, id=k, id=a, id=j。 - jakeonrails

ELSE 应该是 0 吗？这是 FIELD 在没有匹配时返回的值。 - NeverEndingQueue

@Corey 也许你可以用另一个SELECT来包装DISTINCT的结果，以实现上述功能？ - NeverEndingQueue

Answer 2

MySQL中的排序：

> ids = [11,31,29]
=> [11, 31, 29]
> User.where(id: ids).order("field(id, #{ids.join(',')})")

在PostgreSQL中：

def self.order_by_ids(ids)
  order_by = ["CASE"]
  ids.each_with_index do |id, index|
    order_by << "WHEN id='#{id}' THEN #{index}"
  end
  order_by << "END"
  order(order_by.join(" "))
end

User.where(id: [3,2,1]).order_by_ids([3,2,1]).map(&:id) 
#=> [3,2,1]

- ilgam

---

黄金。Railsy方式。 - vinibol12

order_by_ids 在 Rails 6.1 上无法使用，它会引发 ActiveRecord::UnknownAttributeReference 错误 https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html - gaotongfei

对于Rails 6.1及以上版本，您需要使用Arel。如果您正在寻找Rails 6.1的解决方案，请参见https://dev59.com/xHM_5IYBdhLWcg3wmkUK#66517571。 - gaotongfei

有人能提供一个Laravel版本吗？ - Riza Khan

Answer 3

更新：根据@Tometzky的绝妙建议进行完善。

这应该可以在pg 8.4下提供类似于MySQL FIELD() 的功能：

-- SELECT FIELD(varnames, 'foo', 'bar', 'baz')
CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE(
     ( SELECT i FROM generate_subscripts($2, 1) gs(i)
       WHERE $2[i] = $1 ),
     0);
$$ LANGUAGE SQL STABLE

我的过失，但我现在无法验证上面在8.4上的内容；不过，我可以倒推到一个在我面前的8.1实例上“道德”上等效的版本：

-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz'])
CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL STABLE

更尴尬的是，您仍然可以便携地使用一个（可能派生的）货币代码排名表，如下所示：

pg=> select cc.* from currency_codes cc
     left join
       (select 'GBP' as code, 0 as rank union all
        select 'EUR', 1 union all
        select 'BBD', 2 union all
        select 'AUD', 3 union all
        select 'CAD', 4 union all
        select 'USD', 5) cc_weights
     on cc.code = cc_weights.code
     order by rank desc, name asc;
 code |           name
------+---------------------------
 USD  | USA bits
 CAD  | Canadian maple tokens
 AUD  | Australian diwallarangoos
 BBD  | Barbadian tridents
 EUR  | Euro chits
 GBP  | British haypennies
(6 rows)

- pilcrow

---

第一个在Postgres 11上运行良好，将返回类型更改为“integer”后非常方便，可用于按“外部”数据排序。 - Nigel Atkinson

Answer 4

我认为这是最简单的方法：

create temporary table test (id serial, field text);
insert into test(field) values
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD'),
  ('GBP'), ('EUR'), ('BBD'), ('AUD'), ('CAD'), ('USD');
select * from test
order by field!='GBP', field!='EUR', field!='BBD',
  field!='AUD', field!='CAD', field!='USD';
 id | field 
----+-------
  1 | GBP
  7 | GBP
  2 | EUR
  8 | EUR
  3 | BBD
  9 | BBD
  4 | AUD
 10 | AUD
  5 | CAD
 11 | CAD
  6 | USD
 12 | USD
(12 rows)

在PostgreSQL 8.4中，您还可以使用具有可变数量参数的函数（可变函数）来移植field函数。

- Tometzky

---

对于 order-by-bang 的建议加一，对于 VARIADIC 的建议，我会尝试实现。 - pilcrow

Answer 5

SELECT * FROM (VALUES ('foo'), ('bar'), ('baz'), ('egg'), ('lol')) t1(name)
ORDER BY ARRAY_POSITION(ARRAY['foo', 'baz', 'egg', 'bar'], name)

这样怎么样？上面的代码获取结果如下：

foo
baz
egg
bar
lol

如你已经知道的那样，如果一个元素不在数组中，它就会被移到数组末尾。请注意，如果您需要使用自定义排序顺序的IN操作符，也可以使用相同的技巧：

SELECT * FROM table
WHERE id IN (17, 5, 11)
ORDER BY ARRAY_POSITION(ARRAY[17, 5, 11], id);

- 0xF4D3C0D3

---

[42883] 错误：函数 array_position(text[], uuid) 不存在。提示：没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换。位置：240 - Akim Kelar

@AkimKelar 是的，就像提示所说的那样，在 ARRAY_POSITION 中，你需要为后一个参数添加显式类型转换。 - 0xF4D3C0D3

我知道这是一个Rails的问题，但对于任何使用Django的人，有一种优雅的方法来实现这个。 - Benoit Blanchon

Answer 6

ilgam的回答在Rails 6.1中已经不可行，会引发ActiveRecord::UnknownAttributeReference错误: https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html

推荐的方法是使用Arel代替原始SQL。

除了ilgam的解答，以下是Rails 6.1的解决方案：

def self.order_by_ids(ids)
  t = User.arel_table
  condition = Arel::Nodes::Case.new(t[:id])
  ids.each_with_index do |id, index|
    condition.when(id).then(index)
  end
  order(condition)
end

- gaotongfei

---

完美。刚刚在开头添加了“return self unless ids.any？” - colinux

实际上，“return all unless ids.any?”（所有保留作用域链接） - colinux

Answer 7

实际上，Postgres 8.1 版本有另一个优点。

调用 Postgres 函数时，您不能传递超过 100 个参数，因此您的排序最多可以在 99 个元素上进行。

使用数组作为第二个参数来使用函数，而不是使用可变参数，可以消除此限制。

- jc.

Answer 8

您可以通过以下步骤完成此操作：

SELECT 
   ..., code
FROM 
   tablename
ORDER BY 
   CASE 
      WHEN code='GBP' THEN 1
      WHEN code='EUR' THEN 2
      WHEN code='BBD' THEN 3
      ELSE 4
   END

但是为什么你在查询中进行硬编码，使用一个支持表不更合适吗？

--

编辑：根据评论反转了它。

- gahooa

---

@gahooa，我认为你把“代码”的意义搞反了——代码是三个字母的缩写，而OP希望以非字母方式进行排序。 - pilcrow

我希望我能为SQL的编写方式负责，我们正在进行重构，但我承认现在我正在寻找快速解决方案。 - Peer Allan

Answer 9

对于后来者，Rails 7 添加了 in_order_of，因此您可以执行以下操作：

CurrencyCode.in_order_of(:code, 'GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD')

参见：https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-in_order_of。

- Dan Draper

---

像魔法一样有效！需要更多点赞。确认一下，你可以将其与其他方法链接在一起。我就是这样使用的：query.reorder(nil).in_order_of(:current_status, ordered_statuses).order(starts_on: :asc) - Topher Fangio

太棒了！需要更多点赞。并且确认一下，你可以将这个方法与其他方法链接使用。我就是这样使用的：query.reorder(nil).in_order_of(:current_status, ordered_statuses).order(starts_on: :asc) - undefined

Answer 10

只需要定义FIELD函数并使用它即可。实现起来非常简单。以下代码可以在8.4中使用，因为它有unnest和窗口函数，如row_number:

CREATE OR REPLACE FUNCTION field(text, VARIADIC text[]) RETURNS bigint AS $$
SELECT n FROM (
    SELECT row_number() OVER () AS n, x FROM unnest($2) x
) numbered WHERE numbered.x = $1;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

您可以使用以下签名定义另一个副本：

CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS bigint AS $$

如果您想要支持任何数据类型的field()，需要相同的代码体。

- Craig Ringer

---

我认为这是最好的答案，但当我尝试创建函数时，pgadmin会显示“在声明返回bigint的函数中返回类型不匹配，函数的最终语句必须是select/insert”。有什么想法吗？ - chrismarx

@chrismarx Pg 版本？Select version() - Craig Ringer

同时，unnest 函数似乎未注册。"PostgreSQL 9.3.4 on x86_64-apple-darwin13.1.0, compiled by Apple LLVM version 5.1 (clang-503.0.38) (based on LLVM 3.4svn), 64-bit" - chrismarx

听起来相当糟糕。unnest 从8.4版本开始就存在了...请显示\df unnest - Craig Ringer