Экто-запрос и пользовательская функция MySQL с переменной арностью

Question

Я хочу выполнить запрос, подобный следующему:

SELECT id, name
FROM mytable
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

FIELD является специфической функцией MySQL, и 'B', 'A', 'D', 'E', 'C' значения, поступающие из списка.

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

За исключением того, чтобы работать в сыром виде Ecto.Adapters.SQL.query Есть ли способ справиться с этим с помощью DSL запрос Ecto?

Изменить: Вот первый, наивный подход, который, конечно, не работает:

ids = [2, 1, 3] # this list is of course created dynamically and does not always have three items
query = MyModel
        |> where([a], a.id in ^ids)
        |> order_by(fragment("FIELD(id, ?)", ^ids))

Answer 1

ОРМ прекрасны, пока они не протекают. Все делают, в конце концов. Экто молод (например, он только приобрел способность OR где предложения вместе 30 дней назад), так что просто недостаточно для разработки API, который учитывает расширенные возможности SQL-вращения.

Изучив возможные варианты, вы не одиноки в запросе. Неспособность понять списки во фрагментах (как часть order_by или же where или где-либо еще) упоминалось в выпуске Ecto № 1485, в Stackru, на форуме Elixir и в этом сообщении в блоге. Последнее особенно поучительно. Подробнее об этом чуть позже. Сначала давайте попробуем несколько экспериментов.

Эксперимент № 1: сначала можно попробовать Kernel.apply/3 передать список fragment, но это не сработает:

|> order_by(Kernel.apply(Ecto.Query.Builder, :fragment, ^ids))

Эксперимент № 2: Тогда, возможно, мы сможем построить его с помощью манипуляции со строками. Как насчет давать fragment строка, встроенная во время выполнения с достаточным количеством заполнителей для ее извлечения из списка:

|> order_by(fragment(Enum.join(["FIELD(id,", Enum.join(Enum.map(ids, fn _ -> "?" end), ","), ")"], ""), ^ids))

Который будет производить FIELD(id,?,?,?) дано ids = [1, 2, 3], Нет, это тоже не работает.

Эксперимент № 3: Создание полного, окончательного SQL-кода, созданного на основе идентификаторов, с размещением необработанных значений идентификаторов непосредственно в составленной строке. Помимо того, что он ужасен, он тоже не работает:

|> order_by(fragment(Enum.join(["FIELD(id,", Enum.join(^ids, ","), ")"], "")))

Эксперимент № 4: Это подводит меня к тому посту в блоге, о котором я упоминал. В ней автор взламывает отсутствие or_where используя набор предопределенных макросов, основанный на количестве условий для объединения:

defp orderby_fragment(query, [v1]) do
  from u in query, order_by: fragment("FIELD(id,?)", ^v1)
end
defp orderby_fragment(query, [v1,v2]) do
  from u in query, order_by: fragment("FIELD(id,?,?)", ^v1, ^v2)
end
defp orderby_fragment(query, [v1,v2,v3]) do
  from u in query, order_by: fragment("FIELD(id,?,?,?)", ^v1, ^v2, ^v3)
end
defp orderby_fragment(query, [v1,v2,v3,v4]) do
  from u in query, order_by: fragment("FIELD(id,?,?,?)", ^v1, ^v2, ^v3, ^v4)
end

Хотя это работает и использует, так сказать, ORM "с зерном", требуется, чтобы у вас было ограниченное, управляемое количество доступных полей. Это может или не может изменить игру.

---

Моя рекомендация: не пытайтесь подтасовать утечки ORM. Вы знаете лучший запрос. Если ORM не примет его, напишите его напрямую с использованием необработанного SQL и задокументируйте, почему ORM не работает. Защитите его от функции или модуля, чтобы в будущем вы могли оставить за собой право изменить его реализацию. Однажды, когда ORM догонит вас, вы можете просто переписать его без каких-либо последствий для остальной системы.

Answer 2

Создайте таблицу с 2 столбцами:

B 1
A 2
D 3
E 4
C 5

затем JOINLEFT(name, 1) к нему и получите порядковый номер. Тогда сортируй по этому.

(Извините, я не могу помочь с Эликсиром / Экто / Арити.)

Answer 3

Это сводило меня с ума, пока я не обнаружил, что (по крайней мере, в MySQL) существует FIND_IN_SETфункция. Синтаксис немного странный, но он не принимает переменных аргументов, поэтому вы должны иметь возможность сделать это:

ids = [2, 1, 3] # this list is of course created dynamically and does not always have three items
ids_string = Enum.join(ids, ",")
query = MyModel
        |> where([a], a.id in ^ids)
        |> order_by(fragment("FIND_IN_SET(id, ?)", ^ids_string))

Answer 4

Я бы попытался решить эту проблему с помощью следующего оператора SQL SELECT:

[Примечание: сейчас у меня нет доступа к системе для проверки правильности синтаксиса, но я думаю, что все в порядке]

SELECT A.MyID , A.MyName
  FROM (
         SELECT id                                   AS MyID            , 
                name                                 AS MyName          , 
                FIELD(name, 'B', 'A', 'D', 'E', 'C') AS Order_By_Field
           FROM mytable
       ) A
  ORDER BY A.Order_By_Field 
;

Обратите внимание, что список "B", "A",... может быть передан как массив или любой другой метод и заменить то, что написано в приведенном выше примере кода.