ORDER BY FIELD() von MySQL in Postgresql simulieren

Question

Ich probiere PostgreSQL gerade zum ersten Mal aus, da ich von MySQL komme. In unserer Rails-Anwendung haben wir ein paar Orte mit SQL wie so:

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

Es dauerte nicht lange, bis ich herausfand, dass dies in PostgreSQL nicht unterstützt/erlaubt ist.

Weiß jemand, wie man dieses Verhalten in PostgreSQL simulieren kann, oder müssen wir die Sortierung in den Code übernehmen?

Answer 1

Ah, gahooa war so nah dran:

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;

Answer 2

Sortieren in mysql:

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

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

Update , die den großartigen Vorschlag von @Tometzky ergänzt.

Damit sollten Sie eine MySQL FIELD() -ähnliche Funktion unter pg 8.4:

-- 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 aber ich kann das oben Gesagte im Moment nicht auf 8.4 verifizieren; ich kann jedoch rückwärts zu einer "moralisch" gleichwertigen Version arbeiten, die auf der 8.1-Instanz vor mir funktioniert:

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

Noch umständlicher ist es, eine (möglicherweise abgeleitete) Tabelle mit der Rangfolge der Währungscodes zu verwenden, etwa so:

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 4

Dies ist meiner Meinung nach der einfachste Weg:

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)

In PostgreSQL 8.4 können Sie auch eine Funktion mit variabler Anzahl von Argumenten (variadische Funktion) an Port field Funktion.

Answer 5

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

Wie wäre es damit? Oben holt man wie unten:

foo
baz
egg
bar
lol

Wenn ein Element nicht im Array enthalten ist, wird es nach hinten verschoben, wie Sie bereits wissen.