funcion - order by mysql string

Simulando el ORDER BY FIELD() de MySQL en Postgresql

Question

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?

Answer 1

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;

Answer 2

Como respondí here , acabo de lanzar una gema ( 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.

Answer 3

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!

Answer 4

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.

Answer 5

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 .

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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]

Answer 10

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)