Simulando el ORDER BY FIELD () de MySQL en Postgresql

Simplemente probando PostgreSQL por primera vez, proveniente de MySQL. En nuestra aplicación Rails tenemos un par de ubicaciones con SQL como estas:

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

No pasó mucho tiempo para descubrir que esto no es compatible / permitido en PostgreSQL.

¿Alguien sabe cómo simular este comportamiento en PostgreSQL o tenemos que extraer la clasificación en el código?

Ah, Gahooa estaba muy cerca:

 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; 

ordenar en mysql:

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

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

Actualiza , completa la estupenda sugerencia de @Tometzky.

Esto debería darle una función similar a MySQL FIELD() en la página 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 , pero no puedo verificar lo anterior en 8.4 en este momento; sin embargo, puedo retroceder a una versión “moralmente” equivalente que funciona en la instancia 8.1 que tengo delante:

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

De manera más torpe, aún puede usar de forma portátil una tabla (posiblemente derivada) de clasificaciones de códigos de divisas, como las siguientes:

 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) 

Esta es la forma más simple de pensar:

 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) 

En PostgreSQL 8.4 también puede usar una función con número variable de argumentos (función variadic) para portar la función de field .

En realidad, la versión para postgres 8.1 es otra ventaja.

Cuando llame a una función postgres, no podrá pasarle más de 100 parámetros, por lo que su orden se puede hacer al máximo en 99 elementos.

Usando la función usando una matriz como segundo argumento en lugar de tener un argumento variado simplemente elimine este límite.

Simplemente defina la función FIELD y úselo. Es bastante fácil de implementar. Lo siguiente debería funcionar en 8.4, ya que tiene funciones de ventana y row_number como 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; 

También puede definir otra copia con la firma:

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

y el mismo cuerpo si desea admitir el field() para cualquier tipo de datos.

Crear una migración con esta función

 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; 

Entonces solo haz esto

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

voila!

Puedes hacerlo…

 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 

Pero ¿por qué está codificando estos en la consulta? ¿No sería más adecuada una tabla de soporte?

Editar: dio la vuelta según los comentarios

Si va a ejecutar esto a menudo, agregue una nueva columna y un desencadenador preinsertar / actualizar. Luego, establece el valor en la nueva columna en función de este desencadenador y ordena en este campo. Incluso puede agregar un índice en este campo.

Como respondí aquí , acabo de lanzar una gem ( order_as_specified ) que le permite hacer un ordenamiento SQL nativo como este:

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

Devuelve una relación de ActiveRecord, y por lo tanto se puede encadenar con otros métodos, y se ha trabajado con cada RDBMS que he probado.