在Postgresql中模拟MySQL的ORDER BY FIELD（）

Question

刚从MySQL首次尝试PostgreSQL。在我们的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

通过@Tometzky充实了很棒的建议。

这应该FIELD()在pg 8.4下为您提供一个类似于MySQL的函数：

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