Ecto - how to get records by ids in exact order
I have a list of post IDs - [9, 1, 4, 3]
.
I want to get records from postgresql and I want them to be ordered like in this ids list. But when I make a request, the records are returned in no particular 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 can I get records with the same order?
source to share
I don't think there is an easy way to do it in a database, but here's a way to do it 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, let's build a map id => post
. Then for each id in ids
we will get the corresponding Post, if one was found. There were no messages in my application with ids 123 or 456, so they were ignored in for
.
source to share
You can use PostgreSQL function array_position
and Ecto function snippet . In your case it will look like this:
Ecto.Query.from(r in Record, where: r.id in [9, 1, 4, 3])
|> Ecto.Query.order_by([r], fragment("array_position(?, ?)", [9, 1, 4, 3], r.id)
|> Repo.all()
I would avoid processing data outside of the database engine. It shouldn't matter in this very simple example. However, this can affect performance for large datasets or for more complex data structures, because firstly, you must load the results into memory and then perform operations on them only to change the order.
source to share