Ecto AND OR precedence

I’m struggling with precedence issues in an Ecto query with AND and OR.

I have a list of keyword lists:
filtersArray = [[city: "Stockholm", activity: "Driving"], [city: "London", activity: "Eating"]]
and a specific user_id.

I’m trying to create a query that returns:

( (city = "Stockholm" AND activity = "Driving") OR (city = "London" AND activity = "Eating") )
user_id = 1

I’ve gotten to the following:

query = from a in Activity
filtersArray = [[city: "Stockholm", activity: "Driving"], [city: "London", activity: "Eating"]]
query = Enum.reduce(filtersArray, query, fn (filters, query) ->
  Ecto.Query.from q in query, or_where: ^filters
query = Ecto.Query.from q in query, where: q.user_id = 1

However, this doesn’t put the parentheses around the OR, leading to the following query:

(city = "Stockholm" AND activity = "Driving") OR
(city = "London" AND activity = "Eating") AND
user_id = 1

which given precedence will return all results for (city = “Stockholm” AND activity = “Driving”) ignoring the requirement that user_id = 1

It looks like this is what you’re looking for:

Is this query possible in Ecto?

Thanks, unfortunately I have a relative condition as well (activity.done_at >= ^sometime), which I don’t think I can inject into each filters within filtersArray as it has to be an = condition?

I’ve tried to get the dynamic method to work, but I can’t figure out how to do it for my specific use case above.

I wonder if the other syntax would work?

activity = Keyword.get(filter, :activity)
city     = Keyword.get(filter, :city)

|> or_where([q], q.activity == ^activity and == ^city and q.user_id == 1 and q.done_at >= ^some_time)

It looks like this might be what you’re looking for?

d1 = dynamic([a], a.activity == "Eating"  and == "London")
d2 = dynamic([a], a.activity == "Driving" and == "Stockholm")
d3 = dynamic([a], (^d1 or ^d2) and a.user_id == 1)

q = from a in Activity, select:, where: ^d3

# SELECT a0."id" FROM "activities" AS a0 WHERE ((((a0."activity" = "Eating") AND (a0."city" = "London")) OR ((a0."activity" = "Driving") AND (a0."city" = "Stockholm"))) AND (a0."user_id" = 1)) []

Thank you so much for all your help! The filtersArray is of an unknown size however, and I can’t see how to do the (^d1 or ^d2 or ...) and a.user_id == 1) with a dynamic amount of ^dn clauses?

This is a bug. I will investigate it in Ecto master.

The bug has been fixed on Ecto master. If you use Ecto from git and run mix deps.update ecto, your original query should work.

Fantastic, can confirm it works. Many thanks!

1 Like

I needed to know. :slight_smile:

filters = [
  [activity: "Driving", city: "Stockholm"],
  [activity: "Eating",  city: "London"]

d = Enum.reduce(filters, nil, fn(filter, acc) ->
  activity = Keyword.get(filter, :activity)
  city     = Keyword.get(filter, :city)

  d2 = dynamic([a], a.activity == ^activity and == ^city)

  if acc do
    dynamic([_], ^acc or ^d2)

from a in Activity, where: ^dynamic([a], ^d and == 1)

Edit: I didn’t test it, but I think you might be able to replace that last line with:

from a in Activity, where: ^d, where: == 1