Simulando o ORDER BY FIELD () do MySQL no Postgresql

Apenas experimentando o PostgreSQL pela primeira vez, vindo do MySQL. Em nosso aplicativo Rails, temos alguns locais com SQL assim:

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

Não demorou muito para descobrir que isso não é suportado / permitido no PostgreSQL.

Alguém sabe como simular esse comportamento no PostgreSQL ou temos que separar o código?

Ah gahooa estava tão perto:

 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; 

classifique no mysql:

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

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

Atualize , desenvolvendo uma ótima sugestão de @Tometzky.

Isso deve dar a você uma function similar a MySQL FIELD() no 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 , mas não posso verificar o acima em 8.4 agora; no entanto, posso retroceder para uma versão “moralmente equivalente” que funcione na instância 8.1 à minha frente:

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

Mais desajeitadamente, você ainda pode portably usar uma tabela (possivelmente derivada) de classificações de código de moeda, assim:

 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 é a maneira mais simples 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) 

No PostgreSQL 8.4 você também pode usar uma function com um número variável de argumentos (function variádica) para a function de field porta.

Na verdade, a versão do postgres 8.1 é outra vantagem.

Ao chamar uma function postgres, você não pode passar mais de 100 parâmetros para ela, portanto, sua ordenação pode ser feita no máximo em 99 elementos.

Usando a function usando uma matriz como segundo argumento em vez de ter um argumento variádico, basta remover esse limite.

Apenas defina a function FIELD e use-a. É fácil de implementar. O seguinte deve funcionar em 8.4, pois tem unnest e funções de janela 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; 

Você também pode definir outra cópia com a assinatura:

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

e o mesmo corpo se você quiser suportar field() para qualquer tipo de dados.

Crie uma migration com esta function

 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; 

Então faça isso

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

voila!

Você consegue fazer isso…

 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 

Mas por que você está codificando isso na consulta – uma tabela de suporte não seria mais apropriada?

Edit: virou-se como por comentários

Se você executar isso com frequência, adicione uma nova coluna e um acionador de pré-inserção / atualização. Em seguida, você define o valor na nova coluna com base nesse acionador e ordena por esse campo. Você pode até adicionar um índice nesse campo.

Como eu respondi aqui , acabei de lançar uma gem ( order_as_specified ) que permite que você faça uma ordenação SQL nativa como esta:

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

Ele retorna uma relação ActiveRecord e, portanto, pode ser encadeado com outros methods, e funciona com todos os RDBMS que testei.