Имитация 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, или мы должны вытащить сортировку в код?

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

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

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

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

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

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

вуаля!

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

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

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

 Craig Ringer28 июн. 2014 г., 17:29
@chrismarx Pg версия?Select version()
 Craig Ringer30 июн. 2014 г., 05:10
Это звучит довольно обманчиво.unnest работает с 8.4 ... Показать\df unnest пожалуйста
 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-разрядный "
 chrismarx28 июн. 2014 г., 16:46
Я думаю, что это лучший ответ, но когда я пытаюсь создать функцию, pgadmin говорит: «возвращаемое несоответствие типов в объявленной функции, возвращающей bigint, Function 'окончательное утверждение должно быть выбрано / вставлено идеи?

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 предложение, которое япопробую реализовать.

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

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:52
точно правильная подушка
 Peer Allan21 авг. 2009 г., 05:53
Я бы хотел сказать, почему SQL такой, какой есть, мы работаем над рефакторингом, но я признаю, что сейчас ищу быстрое решение

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

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

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

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

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

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

Обновить, воплощая потрясающее предложение @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)

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