Ecto AND OR precedence

Questions / Help
1

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:

WHERE
( (city = "Stockholm" AND activity = "Driving") OR (city = "London" AND activity = "Eating") )
AND
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
end)
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:

WHERE
(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

2

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

Is this query possible in Ecto?

3

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.

4

I wonder if the other syntax would work?

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

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

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

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

q = from a in Activity, select: a.id, 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)) []
6

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?

7

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

8

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.

9

Fantastic, can confirm it works. Many thanks!

1 Like
10

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 a.city == ^city)

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

from a in Activity, where: ^dynamic([a], ^d and a.id == 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: a.id == 1