Beware Ecto's "or_where" pitfall
Tags: ecto, elixir
Published: 2022-06-29 15:12:07

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:

```elixir
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`:

```elixir
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:
```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:

```elixir
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: 
```SQL
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 `OR`ed 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`:
```elixir
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: 
```sql
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`. 

```elixir
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:
```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"]
```
Hope this helps someone and if you have any comments on improvements, please feel free to share. 

If you want to play around with the code, here is the sample project on github: [https://github.com/Ivor/medicine_cupboard](https://github.com/Ivor/medicine_cupboard)