Using Dynamic queries in Ecto

When you have a Phoenix Controller and you need to do a query based on the params, you might end up with something likes this:

defmodule App.PostController do
  def index(conn, params) do
     posts = App.Context.list_posts(params)
     render(conn, "index.html", posts: posts)
  end
end

defmodule App.Context do
   ......
   def  list_posts(params) do
    query = Post 

    query = if user_id = params["owner_id"] do
      query |> where([p], p.user_id == ^user_id)
    else
      query
    end

    Repo.all(query)
  end
  .....
end

There is a better way! Dynamic queries (https://hexdocs.pm/ecto/dynamic-queries.html)

defmodule App.Context do
   ......
   def  list_posts(params) do
    Post 
    |> where(^filter_where(params))
    |> Repo.all()
  end

  defp filter_where(params) do
    Enum.reduce(params, dynamic(true), fn
      {"owner_id", user_id}, dynamic ->
        dynamic([p], ^dynamic and p.user_id == ^user_id)
      {_, _}, dynamic ->
        dynamic
    end)
  end
  .....
end

Now, all your where clauses are in one place :)

Tweet