Имитация MySQL ORDER BY FIELD() в Postgresql

Ответ 1

А, gahooa был так близок:

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;

Ответ 2

сортировать в mysql:

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

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

Ответ 3

Обновить, создавая потрясающее предложение @Tometzky.

Это должно предоставить вам функцию MySQL FIELD() -alike в разделе 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, но я не могу проверить выше на 8.4 прямо сейчас; однако я могу вернуться к "морально" эквивалентной версии, которая работает на экземпляре 8.1, передо мной:

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

Более неловко, вы все же можете портативно использовать (возможно, полученную) таблицу ранжирования кода валюты, например:

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)

Ответ 4

Это, я думаю, самый простой способ:

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)

В PostgreSQL 8.4 вы также можете использовать функцию

Ответ 5

На самом деле версия для postgres 8.1 как еще одно преимущество.

При вызове функции postgres вы не можете передать более 100 параметров, поэтому ваш заказ может быть выполнен максимум на 99 элементах.

Использование функции с использованием массива в качестве второго аргумента вместо того, чтобы иметь переменный аргумент, просто удалите этот предел.

Ответ 6

Просто определите функцию FIELD и используйте ее. Это достаточно просто реализовать. Следующее должно работать в 8.4, так как оно имеет unnest и функции окна, такие как 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;

Вы также можете определить другую копию с подписью:

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

и то же тело, если вы хотите поддерживать field() для любого типа данных.

Ответ 7

Создайте перенос с помощью этой функции

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;

Тогда просто сделайте это

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

вуаля!

Ответ 8

Вы можете сделать это...

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

Но почему вы жестко кодируете их в запросе - не подходит ли вспомогательная таблица?

-

Изменить: перевернул его в соответствии с комментариями

Ответ 9

Если вы запустите это часто, добавьте новый столбец и триггер pre-insert/update. Затем вы устанавливаете значение в новом столбце на основе этого триггера и упорядочиваете это поле. Вы даже можете добавить индекс в это поле.

Ответ 10

Как я ответил здесь, я только что выпустили драгоценный камень ( order_as_specifiedа > ), который позволяет выполнять собственный SQL-заказ следующим образом:

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

Он возвращает отношение ActiveRecord и, следовательно, может быть привязан к другим методам, и он работал с каждой проверенной RDBMS.

Ответ 11

SELECT * FROM (VALUES ('foo'), ('bar'), ('baz'), ('egg'), ('lol')) t1(name)
ORDER BY ARRAY_POSITION(ARRAY['foo', 'baz', 'egg', 'bar'], name)

Как насчет этого? выше одной выборки, как показано ниже:

foo
baz
egg
bar
lol

как вы уже поняли, если элемент не находится в массиве, то он идет назад.