Ecto: Compose queries with OR

[josevalim]

See: https://groups.google.com/forum/#!topic/elixir-ecto/3JrSPYOPCFA

The biggest blocker is to come up with a decent syntax for doing such. Ideas?

[michalmuskala]

We could adapt similar approach that is used for join. We have join(:left | :right, ...). We could extend where to accept additional parameter :and | :or (defaulting to :and) that would join this condition with other conditions already present in the query properly.

Alternative solution is to add or_where and and_where with where being alias to and_where.

[josevalim]

@michalmuskala the problem with or_where is that it has two meanings:

1. It will add new WHEREs, wrapping all of the previous ones in a OR. For example, where |> where |> or_where will compile to: (... AND ...) OR ...
2. It will treat the KW list as an OR: where(foo: 1, bar: 2) will compile to foo = 1 OR bar 2

The issue with 1. is that or_where |> and_where will have different results to and_where |> or_where which may be confusing. With all that said, I was thinking about adding rather something like:

where: join(kw, :or)

Obviously join here is a bad name but hopefully you get the idea. Another approach is to have a single macro that will transform ALL previous wheres into an OR:

query |> where |> where |> where |> make_or

More ideas?

[aphillipo]

Would it be better/clearer to group things more like:

query
|> where_group(:or, where |> where |> where)
|> where_group(:and, where |> where |> where)

Thoughts? Clearly keeping the piping in the second parameter is trickier but when trying different things that one of the best things about elixir code it's ability to quickly copy a block of stuff and have it still work.

[josevalim]

@aphillipo thus is tricky because where expects a query as an input. For example, we would need to write it as:

query 
|> where_group(:or, query |> where |> where |> where) 
|> where_group(:and, query |> where |> where |> where)

Or pass an anonymous function.

[aphillipo]

Yes, I like this a lot less now. The repetition of query feels redundant...

I secretly always wanted to be able to specify where the first parameter goes in a pipe anyway (for functions that don't take the piped thing as the first item)...

I have a feeling there is a perfect and clear solution here but I haven't been able to think of it yet.

[vladshcherbin]

Maybe, you can take some ideas here:

WHERE

AND

$users = DB::table('users')->where('votes', '=', 100)->get();

$users = DB::table('users')->where([
    ['status','1'],
    ['subscribed', '<>' ,'1'],
])->get();

OR (same as AND)

$users = DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();

Additional ones

// whereBetween / whereNotBetween
$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
// whereIn / whereNotIn
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
// whereNull / whereNotNull
$users = DB::table('users')->whereNull('updated_at')->get();

ADVANCED WHERE

DB::table('users')->where('name', '=', 'John')->orWhere(function ($query) {
  $query->where('votes', '>', 100)->where('title', '<>', 'Admin');
})->get();

sql

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Never had any issues, simple and easy to use. I think, with pipelines this syntax will look great.

What do you think?

[undr]

It could be in two various forms:

composable

query
|> where(company: 'My company')
|> where(
  query 
  |> where(admin: true) 
  |> or_where(company_admin: true))
|> where([t], t.inserted_at > '2016-02-20 00:00:00')

# SELECT * FROM table 
#   WHERE company = 'My company' AND (admin = 't' OR company_admin = 't') AND inserted_at > '2016-02-20 00:00:00'

and simple form

query
|> where(company: 'My company')
|> any_of(admin: true, company_admin: true)
|> where([t], t.inserted_at > '2016-02-20 00:00:00')

# SELECT * FROM table 
#   WHERE company = 'My company' AND (admin = 't' OR company_admin = 't') AND inserted_at > '2016-02-20 00:00:00'

Complex case:

nested_query = query |> where(x: 'x') |> where(y: 'y')

query
|> where(a: 'a')
|> where(
  query
  |> where(b: 'b')
  |> or_where(nested_query)
)
|> where(c: 'c')

# SELECT * FROM table WHERE a = 'a' AND (b = 'b' OR (x = 'x' AND y = 'y')) AND c = 'c'

and the same but using alias for the readability:

nested_query = query |> where(x: 'x') |> and_where(y: 'y')

query
|> where(a: 'a')
|> and_where(
  query
  |> where(b: 'b')
  |> or_where(nested_query)
)
|> and_where(c: 'c')

# SELECT * FROM table WHERE a = 'a' AND (b = 'b' OR (x = 'x' AND y = 'y')) AND c = 'c'

[josevalim]

Thanks @undr. I like any_of because it reads better than or_where. The issue is that any_of will do comparisons. I think we still need a mechanism for doing any operation.

I also think we should avoid APIs where we give a query to and_where and or_where because what would happen in cases like this:

nested_query = query |> join(...) |> where(x: 'x') |> and_where(y: 'y') 

query
|> where(a: 'a')
|> and_where(
  query
  |> where(b: 'b')
  |> or_where(nested_query)
)
|> and_where(c: 'c')

Notice how the nested_query has a join. Should it be merged into the main query then? What about other fields like group_by, limit, select? It will be easier if we don't "merge" queries and keep them at the query level. Unless we make it explicit:

|> where(a: 'a')
|> merge(nested_query, :where)

Or we can mix both ideas. We could introduce all_of and any_of as merge operations:

query |> all_of(other_query, :where) # merge as an AND
query |> any_of(other_query, :where) # merge as an OR
query |> all_of(other_query, :having) # merge as an AND
query |> any_of(other_query, :having) # merge as an OR
query |> all_of(other_query, :group_by) # merge by appending
query |> any_of(other_query, :group_by) # raises (it doesn't make sense)

[josevalim]

Another idea is to introduce an identifier that means "all of the previous entries". So we can do things like:

from q in query, where: ... or q.some_field == ^value

I believe this would clearly solve the problems I mentioned above regarding explicitness.

This "identifier" could also be used in select. Imagine you want to wrap a given query, return some new values for preprocessing, but still return the original result:

from q in query, select: {..., q.some_field}

This way we can extend queries by adding things on top. There are a couple places we could use this technique in Ecto. We need a better name/idea than ... though.

[undr]

@josevalim yeah, I have noticed it after I posted comment.

I like the merge way. It's very transparent and no magic there. all_of and any_of can be built using the merge function.

Idea with ... looks a little tricky for me. I would suggest etc as a keyword. :)

Nevertheless the first way looks like the best choice to me. Because it much easy to understand how it works. Even me, who don't have much experience in elixir and ecto.

[lexmag]

I like the idea of merging with all_of and any_of, I think "return some new values for preprocessing, but still return the original result" is another story. :bowtie:

[michalmuskala]

I think we add two separate things:

1. Simple way to add dynamic or queries based on keywords/maps, something similar to where(a: 1, b: 2). I think any_of is a perfect solution for this.
2. A way to compose multiple macro-based wheres together with or. I don't see a clear winner for this one yet.

[josevalim]

@michalmuskala should it be: where: any_of(...) or just any_of(...)? If so, should we also support it on from?

[michalmuskala]

I think where: any_of(...) is not a bad idea. It reads nice.

[josevalim]

Hello folks! We have a final proposal on this matter: combine_where(:and | :or). When such function is called, all previous where entries will be combined with the given operator. This means we can write different and/or with difference precedences. Examples:

query
|> where(a)
|> where(b)
|> where(c)
|> where(d)
|> combine_where(:or)
# a OR b OR c OR d

query
|> where(a)
|> combine_where(:and)
|> where(b)
|> combine_where(:or)
|> where(c)
|> combine_where(:and)
|> where(d)
|> combine_where(:or)
# ((a OR b) AND c) OR d

query
|> where(a)
|> where(b)
|> combine_where(:and)
|> where(c)
|> where(d)
|> combine_where(:or)
# (a AND b) OR c OR d

In other words, by using combine_where at distinct points, you have explicit control over the query and how it should behave regarding previous and upcoming expressions.

For example, if you have a list of dynamic values you want to apply as "ORs":

query
|> combine_where(:and)
|> apply_ors(filters)
|> combine_where(:or)

defp apply_ors(query, filters) do
  Enum.reduce filters, query, &where(&2, ^[&1])
end

[JackSt]

Looks very confusing.

Can we also have an alternative simple syntax with where and or_where so it works as described and discussed above?

query
|> where(a)
|> where(b)
|> where(c)
# a AND b AND c

query
|> where(a)
|> or_where(b)
|> where(c)
# a OR b AND c

query
|> where(a)
|> where(
  query 
  |> where(b) 
  |> or_where(c)
)
|> where(d)
# a AND (b OR c) AND d

[josevalim]

@JackSt this syntax is quite tricky to make it work because what if you do something like this:

query
|> where(a)
|> or_where(query |> join(...) |> limit(...) |> where(...))

Merging queries is _extremely complicated_. Even though we could say "we will merge only where fields", you will run into issues like this:

query
|> where(a)
|> exclude(:where)
|> where(query |> where(b) |> or_where(c))

Someone would expect (b OR c) but it would be (a AND b OR C) because it would just be impossible to know. The only option to make this more obvious is by supporting anonymous functions:

query
|> where(a)
|> exclude(:where)
|> where(fn q -> q |> where(b) |> or_where(c) end)

Which is more verbose and less flexible.

[JackSt]

@josevalim why would someone put limit, join, etc in where, seems like a bad choice. Can we make it impossible to pass there anything except allowed functions like where and or_where?

query
|> where(a)
|> where(
  query
  |> where(b)
  |> or_where(c)
)

Merging is making it very complicated and confusing while only few users will ever use it. So, maybe we can make it convenient for most of the users with simple syntax while rare hard cases could be solved with some kind of raw operator?

We can just take 10-15 common where cases and try to write them using this 2 syntaxes to see, how it will look like. For me, combine_where is very confusing and requires some time to understand, what the query is. The constant necessity of spending time just to understand a simple query really scares me.

[josevalim]

@JackSt my point is that there is no way to decouple "merging" from your proposed syntax because all you are doing is passing a query to where. Because you are writing everything in a single code snippet, it is not apparent but it becomes more obvious once you break it apart:

query
|> where(a)
|> where(anything_that_returns_a_query)

Also, it is not about this syntax or combine_where per-se, we are all discussing just options. :) Thanks for feedback!

[JackSt]

@josevalim I'm new here and also used Laravel before with similar syntax. When you use limit, etc there - it is not included in the query. So, I thought of similar behaviour here.

I like Ecto and really hope to see such simple syntax (or very similar) here. Thank you ;)

[chrismcg]

I'm finding the second example a little confusing. Is the first combine_where(:and) just ignored because there's only one where before it?

[josevalim]

@chrismcg yes. That's because queries should compose without caring if there are none or a single where before.

[michalmuskala]

I was thinking about this lately. I still think the where: any_of(...) syntax is easy to implement and we should go for it. But this is about solving the other issue: dynamic ors.

I propose adding a combine_queries([queries], :and | :or) function that is a slight variation on @josevalim's proposal. This function does not play well with pipeline, not does it have a keyword syntax variant. It solves a feature that is needed.

How it works? The function combines where and having clauses of all the queries with the requested link: either or or and. It requires that all the queries are the same in all the fields beside those two - all the joins, group bys, selects, etc. To avoid confusion neither query can use limit, offset or distinct - you can apply those after combining queries.

Give me some examples! Here you are:

base = from f in Foo, where: f.bar == 1 select: f.baz
# => SELECT baz FROM foo WHERE bar = 1

lhs = where(base, other_field: "abc")
# => SELECT baz FROM foo WHERE bar = 1 AND other_field = 'abc'
rhs = where(base, other_field: "def")
# => SELECT baz FROM foo WHERE bar = 1 AND other_field = 'def'

combine_queries([lhs, rhs], :or)
|> limit(5)
|> offset(10)
# => SELECT baz FROM foo 
# => WHERE (bar = 1 AND other_field = 'abc') OR (bar = 1 AND other_field = 'def')
# => LIMIT 5 OFFSET 10

It may seem awkward when used like that, but when combined with named functions works much better:

base = ...

[search_for(base, "abc"), search_for(base, "def")]
|> combine_queries(:or)
|> limit(5)
|> offset(10)

[erneestoc]

👍 sounds like a great solution.. I've been waiting for this for months...

[kuon]

I really like the Sequel composition, like so:

e1 = {name: "foo"}
e2 = {age: 2}
e3 = {size: 5}

where(Sequel.|(e1, Sequel.&(e2, e3)))

will result in something like

Where (name = "foo" or (age = 2 and size = 5))

This is similar to what @michalmuskala proposed.

[kipcole9]

I prefer @michalmuskala's proposed approach:

• Makes the combinations explicit. Whilst José's syntax works, my head explodes when I try to mentally unwrap the infix precedence
• The combine_queries strategy could also be used (later) as an api for UNION queries as well: combine_queries(:union) for example
• Composing the filter clauses seems to fit in nicely with all of the Enum goodness that helps build dynamic queries

[kuon]

Also, my use case (and I guess others too) includes dynamic query generation so we should be able to compose in a way that is friendly with dynamic queries.

[jeregrine]

Not sure I'm down with combine_queries since this will only really apply to the where clause. Possible combine_where

[michalmuskala]

It will work with things that can be combined with and/or - wheres and havings.

To be honest I can't imagine composing on other fields.

[kipcole9]

Composing UNION would also be good.

Sent from my iPhone

On 22 Jun 2016, at 12:04 PM, Michał Muskała [email protected] wrote:

It will work with things that can be combined with and/or - wheres and havings.

To be honest I can't imagine composing on other fields.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[kuon]

As speaking of only combine_where vs combine_query, there is something that should be supported, it's referencing joined tables.

combine_where would make something like this complicated:

# pseudo code

recent_announce = "(p.posted_at > 1.hour.from_now and p.kind = 'announce')"
query = "p.body ilike '%sometext%' or recent_announce"

from c in MyApp.Comment,
    join: p in assoc(c, :post),
   where: query,
  select: c

We need to be able to combine the same joined table while combining queries/where.

We could just raise if queries containing "uncombinable" clauses are combined (like select) or just have the last query override it.

Here is something I had to do lately (and ended up doing in raw sql, which was a pain).

# Pseudo pseudo code™
terms = ["foo", "bar"]

term_wheres = 
terms.map fn (t) ->
  from a in Asset, where: a.title ilike "%#{t}%"
end

labels = ["a", "b"]

label_wheres =
labels.map fn (t) ->
  from a in Asset,  join: l in assoc(a, :labels), where: l.name = ^t
end

final_query = from a  in Asset, where: orred(label_wheres) and orred(term_wheres)

It was a bit more complicated than that, as terms and labels could be arbitrary "orred" or "anded" together with an arbitrary tree like ["or", ["term", "jon"], ["and", ["label", "foo"], ["term", "bar"]].

[lxcodes]

Were there any plans to bring nested where conditions back into focus? Notice we have or_where, but I haven't been able to find a way to -- most searches end up here. @michalmuskala's looks good to me.

[josevalim]

Yes, look for Ecto.Query.dynamic in the docs.