Ecto - how to get records by ids in exact order

Question

I have a list of record ids - [9, 1, 4, 3].

I want to retrieve records from postgresql and want them be ordered as in this ids list. But when I make a query, records are returned in arbitrary order:

Ecto.Query.from(r in Record, where: r.id in [9, 1, 4, 3]) 
  |> Repo.all()
  |> Enum.map(&Map.get(&1, :id)) # => [4, 9, 1, 3]

How could I retrieve records with the same order?

Answer 1

I don't think there's any easy way to do this in the database, but here's a way to do this in Elixir:

ids = [123, 4, 1, 3, 2, 456]
posts = from(p in Post, where: p.id in ^ids, select: {p.id, p}) |> Repo.all |> Map.new
posts = for id <- ids, posts[id], do: posts[id]
posts |> Enum.map(&(&1.id)) |> IO.inspect

Output:

[4, 1, 3, 2]

First, we build a map of id => post. Then, for each id in ids, we get the corresponding Post, if it was found. In my app, there was no Post with id 123 or 456, so they were ignored in for.