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

Просто пробую PostgreSQL в первый раз, исходя из MySQL. В нашем Rails-приложении у нас есть пара мест с SQL, например:

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

Не потребовалось много времени, чтобы обнаружить, что это не поддерживается / не разрешено в PostgreSQL.

Кто-нибудь знает, как смоделировать это поведение в PostgreSQL, или мы должны вытащить сортировку в код?

 Peer Allan21 авг. 2009 г., 13:47
Великая точка Депес! По сути, мы ищем индивидуальный порядок сортировки. Функция FIELD позволяет вам создать пользовательский набор для сортировки.
 user8016821 авг. 2009 г., 11:14
Может быть полезно объяснить, чего вы хотите достичь. Как бы трудно это ни было представить - не все знают MySQL :)

Ответы на вопрос(10)

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

 Will Sheppard06 февр. 2013 г., 13:54
Триггеры плохие, ммка? Избегайте, если это вообще возможно!
 jirigracik18 сент. 2017 г., 13:14
@WillSheppard Это неправильно. Они не могут использоваться бездумно и часто, но вы просто не можете сказать «Триггеры плохие»

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

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(',')})")

вуаля!

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

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

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

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

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

Решение Вопроса

Ах, Гахуа был так близко

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;
 NeverEndingQueue10 окт. 2018 г., 13:41
@Corey Может быть, вы можете обернуть результаты из вашего DISTINCT с другим SELECT, который реализует вышеупомянутое?
 NeverEndingQueue10 окт. 2018 г., 13:40
НЕ ДОЛЖНО БЫТЬ 0? Это то, что ПОЛЕ возвращается без совпадения.
 Corey25 июл. 2011 г., 18:47
Этот метод не работает, когда вы используете DISTINCT. Есть другие идеи для этого сценария?
 gahooa21 авг. 2009 г., 17:53
К сожалению !! немного поздней вечерней дислексии ... у вас есть мой голос!
 jakeonrails07 янв. 2016 г., 21:04
Я не уверен, почему это работает, но я нашел альтернативу. Если вы хотите, чтобы результаты в порядке по j, a, k, e, то выorder by id=e, id=k, id=a, id=j.

Просто определите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() для любого типа данных.

 chrismarx28 июн. 2014 г., 18:39
также unnest, похоже, не является зарегистрированной функцией, "PostgreSQL 9.3.4 на x86_64-apple-darwin13.1.0, скомпилированный Apple LLVM версии 5.1 (clang-503.0.38) (на основе LLVM 3.4svn), 64-битный "
 Craig Ringer28 июн. 2014 г., 17:29
@chrismarx Pg версия?Select version()
 Craig Ringer30 июн. 2014 г., 05:10
Это звучит довольно обманчиво.unnest работает с 8.4 ... Показать\df unnest пожалуйста
 chrismarx28 июн. 2014 г., 16:46
Я думаю, что это лучший ответ, но когда я пытаюсь создать функцию, pgadmin говорит - «несоответствие типа возвращаемого значения в функции, объявленной для возврата bigint, в последнем утверждении функции должны быть идеи выбора / вставки»?

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

Это должно дать вам MySQLFIELD()-подобная функция под стр. 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

Моя вина, но я не могу проверить выше на 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)

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 вы также можете использоватьфункция с переменным числом аргументов (переменная функция) в портfield функция.

 pilcrow21 авг. 2009 г., 16:57
+1 за заказ, и заVARIADIC предложение, которое я постараюсь реализовать.

сортировать в 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]
 mklb12 апр. 2015 г., 20:12
люблю эту идею!
 armchairdj13 дек. 2017 г., 01:13
Это прекрасно работает для меня!
 Prashant Vardhan Singh02 февр. 2016 г., 11:33
Хорошая идея @ilgam

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

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

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

-

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

 pilcrow21 авг. 2009 г., 04:26
@gahooa, я думаю, что вы перевернули смысл «кода» - код - это три альфа-аббревиатуры, которые ОП хочет отсортировать не-альфа-образом.
 Peer Allan21 авг. 2009 г., 05:53
Я бы хотел сказать, почему SQL такой, какой есть, мы работаем над рефакторингом, но я признаю, что сейчас ищу быстрое решение
 Peer Allan21 авг. 2009 г., 05:52
точно правильная подушка

Ваш ответ на вопрос