%{ author: "Yuri Oliveira", author_link: "https://github.com/yuriploc", tags: , date: ~D[2021-11-12], title: "Writing a cleaner select query", excerpt: """ How learning more about the Ecto.Query module can help you write cleaner select queries. """ }
It's not unusual for database queries to avoid select *
when we can use an index (say, :id
) and have a performance gain.
So, instead of writing:
query = from p in Post
Repo.all(query)
And getting back more data than we would care using, we can explicitly tell Ecto (and the DB) which columns we want it to return us:
query = from p in Post, select: %{id: p.id, title: p.title, category_id: p.category_id}
Repo.all(query)
But why do we have to be so explicit and duplicate keys and values? Isn't there a better way?
It turns out Ecto.Query
already solved this for us with the [map/2](https://hexdocs.pm/ecto/Ecto.Query.API.html#map/2)
function. So this:
query = from p in Post, select: %{id: p.id, title: p.title, category_id: p.category_id}
Repo.all(query)
Becomes:
query = from p in Post, select: map(p, [:id, :title, :category_id])
Repo.all(query)
Or, in Pipeland:
Post
|> select([p], %{id: p.id, title: p.title, category_id: p.category_id})
|> Repo.all()
Post
|> select([p], map(p, [:id, :title, :category_id]))
|> Repo.all()
And we can even have dynamic fields when using it in a function, like:
def filter_posts(post_ids, fields \\ [:id, :title, :category_id]) do
Post
|> select([p], map(p, ^fields))
|> Repo.all()
end
Enjoy Ecto!
Thank you to the Groxio Mentoring folks for the support
This post looks GREAT! I'd suggest a catchier title, something that conveys that you'll be talking about the
map
Ecto query function. Maybe: "TIL: Cleaner Queries with Ectomap
"If you'd like to open this as a PR on Elixir School, you can follow these instructions to open your PR with the new blog post. https://github.com/elixirschool/elixirschool#posting-an-article. Feel free to @ me and I'll review it there!