Elixir Ecto query - preserving order of output

Question

I have a list of ids ["123", "321", "101"]

And a query segments = Repo.all(from(s in Segment, where: s.id in ^ids))

The output of my matched segments is not in the same order as in the list. E.g. If this was an Enum.map then the order would be preserved. Is there a way I can do this, using just the one query, without having to do something like Enum.each?

See [this answer](https://stackoverflow.com/questions/866465/order-by-the-in-value-list) for some inspiration. — zwippie, Jan 20 '20 at 15:53

score 3 · Answer 1 · answered Jan 20 '20 at 18:31

Here's my attempt at converting one of the answers from @zwippie's comment to Ecto (for Postgres):

defmodule Segment do
  ...

  def by_id_in_order(query, ids) do
    query
    |> join(:inner, [s], o in fragment("SELECT * FROM UNNEST(?::int[]) WITH ORDINALITY AS o (id, ordinal)", ^ids), on: s.id == o.id)
    |> order_by([s, o], asc: o.ordinal)
  end
end

To be used like

Segment
|> Segment.by_id_in_order([123, 321, 101])
|> Repo.all()

Elixir Ecto query - preserving order of output

1 Answers1