Beware Ecto's "or_where" pitfall

#ecto #elixir

In this post I want to highlight an error that can creep into your use of Ecto's or_where function. This is not a fault with Ecto, it is merely a way in which we can jump to the wrong conclusion about how this function works in conjunction with Ecto.Query functions.

Every now and then I find myself building a form to filter a specific resource. This form allows users to filter records where a specific attribute is equal to a certain value (think select), or where a specific attribute is in a list of values (think multi-select).

As an example, return all the medicines that are tablets, and treat either nausea or a headache. This means that records that are not tablets should not be returned. And tablets that do not have "nausea" or "headache" in the list of conditions that they can treat, should not be returned. In order to add an AND clause to all the queries, to highlight how this breaks, I also add a boolean field available. We will include a clause to filter out any unavailable records to all our queries.

Here is the relevant part of the schema:

schema "medicines" do
  ...
  field :type, :string
  field :conditions, {:array, :string}
  field :available, :boolean, default: false
end

The first part is easy. We return records where type is tablet:

defmodule MedicineCupboard.MedicineFilter do
  import Ecto.Query

  def type_filter(query, %{"type" => type}) do
    where(query, [m], m.type == ^type)
  end
  def type_filter(query, _no_type), do: query
end

Starting with the available clause we get this SQL:

SELECT m0."id", m0."name", m0."type", m0."conditions", m0."available" 
FROM "medicines" AS m0 WHERE 
(m0."available" = TRUE) AND (m0."type" = $1) ["tablet"]

The second filter is a bit more complicated. We want to return medicines where either of the the values passed as the condition filter is present in the conditions array of strings.

It is tempting to write this second filter the way it is written below:

defmodule MedicineCupboard.MedicineFilter do
  ...

  # This does not build the intended query!
  def condition_filter(query, %{"condition" => conditions_list}) do
    conditions_list
    |> Enum.reduce(query, fn condition, query -> 
      or_where(query, [m], ^condition in m.conditions)
    end)
  end
end

The problem with this query is that the SQL that it builds will look like this:

SELECT m0."id", m0."name", m0."type", m0."conditions", m0."available" 
FROM "medicines" AS m0 WHERE 
((m0."available" = TRUE)) 
OR ($1 = ANY(m0."conditions")) 
OR ($2 = ANY(m0."conditions")) ["nausea", "headache"]

The Ecto query looks like this:

#Ecto.Query<from m0 in MedicineCupboard.Medicine, where: m0.available == true,
 or_where: ^"nausea" in m0.conditions, or_where: ^"headache" in m0.conditions>

Any medicine that includes headache in the conditions field, whether it is available or not will be returned. The clause for availability is ORed with the clauses for the conditions.

What we want to have is available = true AND nausea in conditions, or headache in conditions.

The Ecto.Query or_where/3 function appends an OR clause to all the previous conditions, effectively resulting in:
Either satisfy all the conditions up to this point, OR just satisfy this new or_where clause.

A way that I have solved this is to use a subquery:

defmodule MedicineCupboard.MedicineFilter do
  import Ecto.Query

  def type_filter(query, %{"type" => type}) do
    where(query, [m], m.type == ^type)
  end
  def type_filter(query, _no_type), do: query

  def condition_filter(query, %{"condition" => condition_list}) do
    conditions_query = condition_list
    |> Enum.reduce(Medicine, fn condition, query -> 
      or_where(query, [m], ^condition in m.conditions)
    end)
    |> select([:id])

    where(query, [m], m.id in subquery(conditions_query))
  end
end

This groups the various or_where clauses in one subquery and then appends an AND where clause to the incoming query, where we return only records where their id is returned by the conditions subquery.

The resulting SQL looks like this:

SELECT m0."id", m0."name", m0."type", m0."conditions", 
m0."available" FROM "medicines" AS m0 WHERE 
(m0."available" = TRUE) 
AND 
(m0."id" IN 
  (SELECT sm0."id" FROM "medicines" AS sm0 WHERE 
    ($1 = ANY(sm0."conditions")) OR ($2 = ANY(sm0."conditions"))
  )
) 
["nausea", "headache"]

and the Ecto query looks like this:

#Ecto.Query<from m0 in MedicineCupboard.Medicine, where: m0.available == true,
 where: m0.id in subquery(#Ecto.Query<from m0 in MedicineCupboard.Medicine, or_where: ^"nausea" in m0.conditions, or_where: ^"headache" in m0.conditions, select: [:id]>)>

Lastly, we can now also combine the type and conditions filters and see that the available and type filter are joined with AND and that the conditions subquery, with it's embedded OR clauses, is also added to the existing queries with an AND.

params = %{"type" => "tablet", "conditions" => ["nausea", "headache"]}
Medicine
|> where([m], m.available == true)
|> MedicineFilter.type_filter(params)
|> MedicineFilter.condition_filter(params)
|> Repo.all()

This then gives us the following ecto query:

#Ecto.Query<from m0 in MedicineCupboard.Medicine, where: m0.available == true,
 where: m0.type == ^"tablet",
 where: m0.id in subquery(#Ecto.Query<from m0 in MedicineCupboard.Medicine, or_where: ^"nausea" in m0.conditions, or_where: ^"headache" in m0.conditions, select: [:id]>)>

and the SQL:

SELECT m0."id", m0."name", m0."type", m0."conditions", 
m0."available" FROM "medicines" AS m0 
WHERE (m0."available" = TRUE) 
AND (m0."type" = $1) 
AND (m0."id" IN (
  SELECT sm0."id" FROM "medicines" AS sm0 WHERE 
    ($2 = ANY(sm0."conditions")) OR ($3 = ANY(sm0."conditions"))
  )
) ["tablet", "fever", "headache"]