Compose Ecto Queries in Elixir

Question

I have created a list of queries from a list of parameters that were passed in from a client:

[
  #Ecto.Query<from v in Video, where: v.brand == ^"Cocacola">,
  #Ecto.Query<from v in Video, where: v.type == ^"can">
]

However, I need to iterate through this list and compose a single query which is the accumulation of all of them.. (the input of the next query is the current query, etc..)

Would someone be able to point me in the right direction about how to do this. It would be greatly appreciated!

I understand I can compose the queries one by one. But I am getting params from the client and have a long list of fields (brands, type, ...etc) and do not want to make a separate query for each one.

Answer 1

Unless you open up the individual query structs and go through their underlying implementation, it is neither possible nor recommended to join queries in Ecto like this. Instead you should try to break them up and make them composable.

Ecto makes it very easy for you to compose queries together:

defmodule VideoQueries do
  import Ecto.Query

  def with_brand(query, brand) do
    where(query, [v], v.brand == ^brand)
  end

  def with_type(query, type) do
    where(query, [v], v.type == ^type)
  end

  def latest_first(query) do
    order_by(query, desc: :inserted_at)
  end
end

And you can call them together like this:

Video
|> VideoQueries.with_brand("Cocacola")
|> VideoQueries.with_type("can")
|> VideoQueries.latest_first

---

Now let's say you get a Map or Keyword List of query parameters and you want to apply them, you could still call them together by iterating over the keys/values at runtime. You could build a filter method that does that for you:

# Assuming args are a Keyword List or a Map with Atom keys
def filter(query, args) do
  Enum.reduce(args, query, fn {k, v}, query ->
    case k do
      :brand -> with_brand(query, v)
      :type  -> with_type(query, v)
      _      -> query
    end
  end)
end

And can dynamically compose queries like this:

user_input = %{brand: "Cocacola", type: "can"}

Video
|> VideoQueries.filter(user_input)
|> Repo.all

---

Further Readings:

• Blog Post: Composable Queries with Ecto and Joins
• Screencast: Composing Ecto Queries
• Elixir Forum: Dynamically composing Ecto Queries

Answer 2

Although I agree with @sheharyar that composable queries is the best approach, sometimes we need need solutions that go beyond best practices. So, I'll provide an answer to your question as stated.

Don't let my example's schema distract you. It's just a project I had loaded to test the solution...

To examine a query structure, you might try this:

iex(128)> Map.from_struct(from(q in OneIosThemeGen.Themes.Entry, where: q.base_hex == ^base_hex))
%{
  assocs: [],
  distinct: nil,
  from: {"entries", OneIosThemeGen.Themes.Entry},
  group_bys: [],
  havings: [],
  joins: [],
  limit: nil,
  lock: nil,
  offset: nil,
  order_bys: [],
  prefix: nil,
  preloads: [],
  select: nil,
  sources: nil,
  updates: [],
  wheres: [
    %Ecto.Query.BooleanExpr{
      expr: {:==, [],
       [{{:., [], [{:&, [], [0]}, :base_hex]}, [], []}, {:^, [], [0]}]},
      file: "iex",
      line: 128,
      op: :and,
      params: [{"#E8EBED", {0, :base_hex}}]
    }
  ]
}

As you can see, the where clause is held in the wheres field. It contains a list.

So, we could extract the wheres field from each of the queries and concatenate the lists. That is what I demonstrate below.

Here is an example of composing the where clauses of multiple queries. It only handles the where clauses by 'and'ing them together.

base_hex =  "#E8EBED"
name = "bodyText"

queries = [
  from(q in OneIosThemeGen.Themes.Entry, where: q.base_hex == ^base_hex),
  from(q in OneIosThemeGen.Themes.Entry, where: q.name == ^name)
]
build = fn queries -> 
  wheres = Enum.reduce(queries, [], fn %{wheres: wheres}, acc -> wheres ++ acc end)
  from(q in OneIosThemeGen.Themes.Entry)
  |> Map.put(:wheres, wheres)
end
query = build.(queries)
rows = Repo.all(query)

# sort function for result equality assertion
sort = fn list -> Enum.sort(list, & &1.id <= &2.id) end

# Single query for results equality test
combined_query = from(q in OneIosThemeGen.Themes.Entry, where: q.base_hex == ^base_hex and q.name == ^name)
rows_combined = Repo.all(combined_query)

# Test that the results are the same
sort.(rows) == sort.(rows_combined)
# true

# Now test that running the queries individually does not return the same results
rows3 = Enum.map(queries, &Repo.all/1) |> List.flatten()
sort.(rows3) != sort.(rows)
# true

IO.puts("length {rows, rows3}: " <> inspect({length(rows), length(rows3)}))
# length {rows, rows3}: {2, 5}

Note that this solution relays on the internal structure of the Ecto query which is generally a bad practice. It could break in a future Ecto update. However, it is one potential solution to the specific question asked.