Mitunter möchte man ein Abfrageergebnis nach einer vorgegebenen Liste von IDs sortieren, also weder alphabetisch nach Namen, noch numerisch nach Wert, sondern auf Basis einer konfigurierten Reihenfolge von Werten. Da man in PostgreSQL auch Funktions-Ergebnisse als Argumente für eine Sortierung verwenden kann, können wir eine Hilfsfunktion in der Datenbank deklarieren, über die sich diese Form der Sortierung umsetzen lässt:
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE FUNCTION arridx(anyarray, anyelement) RETURNS integer AS $BODY$ SELECT i FROM ( SELECT generate_series(array_lower($1,1),array_upper($1,1)) ) g(i) WHERE $1[i] = $2 LIMIT 1; $BODY$ LANGUAGE sql IMMUTABLE; |
Jetzt können wir die Funktion zur Sortierung einsetzen:
1 2 3 |
SELECT n.node_id, n.name FROM nodes n ORDER BY arridx(ARRAY[5,1,3,8,12],n.node_id) NULLS LAST, n.name ASC; |
Das NULLS LAST stellt sicher, dass Werte die nicht in der Liste enthalten sind ans Ende des Ergebnisses sortiert werden.
Bei sehr umfangreichen Datenmengen kann die direkte Nutzung eines Index für die Funktion sinnvoll sein. Die ist aufgrund der IMMUTABLE-Deklaration auch möglich, allerdings nur für eine spezifische Werteliste, die damit Teil der Index-Deklaration werden muss. Im Normalfall genügt allerdings ein Index auf das verwendete Feld selbst.
In ColdFusion kann auch an dieser Stelle <cfqueryparam>
für die Sortierung eingesetzt werden. Wichtig ist dabei, dass hier der richtige Datentyp für die Liste gewählt wird – z.B. wenn die Spalte vom Typ int8/bigInt ist. Auch einfache int-Arrays (cf_sql_integer) oder Text-Arrays (cf_sql_varchar) sind natürlich möglich.
1 2 3 4 5 6 7 8 9 |
<cfset variables.lstIds='5,1,3,8,12' /> <cfquery name="variables.qGetExample" datasource="myDS"> SELECT n.node_id, n.name FROM nodes n ORDER BY arridx(ARRAY[ <cfqueryparam cfsqltype="cf_sql_bigint" list="yes" value="#variables.lstIds#" /> ],n.node_id) NULLS LAST, n.name ASC; </cfquery> |