在Postgresql中模拟MySQL的ORDER BY FIELD()




ruby-on-rails (8)

刚刚试用PostgreSQL,来自MySQL。 在我们的Rails应用程序中,我们有几个带SQL的位置,如下所示:

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

没过多久就发现PostgreSQL不支持/不允许这样做。

有谁知道如何在PostgreSQL中模拟这种行为,还是我们必须将整理到代码中?


Answer #1

在mysql中排序:

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

在postgres:

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]

Answer #2

只需定义FIELD函数并使用它。 它很容易实现。 以下应该在8.4中工作,因为它有像row_number这样的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() ,那么同一个主体。


Answer #3

使用此功能创建迁移

CREATE OR REPLACE FUNCTION field(anyelement, VARIADIC anyarray) 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;

然后就这样做

sequence = [2,4,1,5]
Model.order("field(id,#{sequence.join(',')})")

瞧!


Answer #4

实际上postgres 8.1的版本是另一个优势。

当调用postgres函数时,你不能传递超过100个参数,所以你的排序最多可以在99个元素上完成。

使用数组作为第二个参数而不是具有可变参数的函数只是删除此限制。


Answer #5

正如我here回答的那样,我刚刚发布了一个gem( order_as_specified ),允许你像这样进行本机SQL排序:

CurrencyCode.order_as_specified(code: ['GBP', 'EUR', 'BBD', 'AUD', 'CAD', 'USD'])

它返回一个ActiveRecord关系,因此可以与其他方法链接,并且它适用于我测试过的每个RDBMS。


Answer #6

你可以这样做...

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

但是为什么要将这些硬编码到查询中 - 支持表不是更合适吗?

-

编辑:根据评论将其翻转


Answer #7

更新 ,充实了@Tometzky的极好建议。

这应该在pg 8.4下给你一个MySQL FIELD() alike函数:

-- 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

Mea culpa ,但我现在无法在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)

Answer #8

如果您经常运行此操作,请添加新列和预插入/更新触发器。 然后根据此触发器设置新列中的值,并按此字段排序。 您甚至可以在此字段上添加索引。





postgresql