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

ilgam's Antwort funktioniert seit Rails 6.1 nicht mehr, ein ActiveRecord::UnknownAttributeReference wird ein Fehler ausgelöst: https://api.rubyonrails.org/classes/ActiveRecord/UnknownAttributeReference.html

Es wird empfohlen, Arel anstelle von Roh-SQL zu verwenden.

Zusätzlich zu ilgam's Antwort Hier ist die Lösung für 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

Answer 2

Eigentlich die Version für Postgres 8.1 als weiterer Vorteil.

Wenn Sie eine Postgres-Funktion aufrufen, können Sie ihr nicht mehr als 100 Parameter übergeben, so dass Ihre Bestellung maximal 99 Elemente umfassen kann.

Bei Verwendung der Funktion mit einem Array als zweitem Argument anstelle eines variadischen Arguments wird diese Begrenzung aufgehoben.

Answer 3

Sie können dies tun...

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

Aber warum kodieren Sie diese in der Abfrage fest - wäre eine unterstützende Tabelle nicht besser geeignet?

--

Edit: umgedreht, wie in den Kommentaren angegeben

Answer 4

Definieren Sie einfach die FIELD Funktion und verwenden Sie sie. Sie ist einfach genug zu implementieren. Das Folgende sollte in 8.4 funktionieren, da es über unnest und Fensterfunktionen wie 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;

Sie können auch eine weitere Kopie mit der Unterschrift definieren:

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

und dieselbe Stelle, wenn Sie Folgendes unterstützen möchten field() für jeden Datentyp.

Answer 5

Wenn Sie dies häufig tun, fügen Sie eine neue Spalte und einen Trigger vor der Einfügung/Aktualisierung hinzu. Dann setzen Sie den Wert in der neuen Spalte auf der Grundlage dieses Triggers und ordnen nach diesem Feld. Sie können sogar einen Index für dieses Feld hinzufügen.