Building Dynamic Where Clauses involving Joins

[Kyle Hanson]

Hello,

What I want to do is build a where clause dynamically involving a join.

This is what I would like to do:

pattern =[
   {:and, [
       {:eq, :parent, :some_field, 8},
       {:or, [
            {:eq, :child, :other_field, "blah"},
            {:and, [
                {:not_eq, :child, :blah_field, 10},
                {:gt, :parent, :oof_field, 10},
            ]},
       ]}
   ]}
]}

from parent in Parent,
     inner_join: child in assoc(parent: children)
     where: build_where(parent, child, pattern)

And then the end result would be a query that looks like this

from parent in Parent,
     inner_join: child in assoc(parent: children)
     where: (
         parent.some_field = 8
         and (
            child.other_field = "blah"
            or (
               child.blah_field != 10
               and parent.oof_field > 10
            )
         )
     )

When I try to do it I get

`build_where(parent, child, [])` is not a valid query expression

Building the reducer to process the list is the easy part. What I don't understand is how to dynamically build WHERE clauses in ecto that are more sophisticated than piping `where` and `or_where`. I have searched quite a bit and can't find anything. This seems like something pretty basic that I would hope ecto supports.

Thanks,

Kyle

[José Valim]

Can you provide examples of different queries you may want to build in build_where and the different parameters you may pass and how those different parameters affect the results?

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/c54827cd-092b-4a43-bc69-76d42e5347bc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kyle Hanson]

Thanks for responding,

 

I don't want to solve the specific narrow use case instead of answering more generally.

My question is: Given a join, how can I dynamically generate a where clause that takes into account the parent table and the joined table. Or even more basic: how do I generate a WHERE clause for a query. Dynamically generating WHERE seems like such a basic thing that I can't find an answer to. The closest thing is to use bindingless queries but that really only works for AND and equality comparisons.

Here is a rough mock up of the behavior that I want.

pattern =[
   {:and, [
       {:eq, :parent, :some_field, 8},
       {:or, [
            {:eq, :child, :other_field, "blah"},
            {:and, [
                {:not_eq, :child, :blah_field, 10},
                {:gt, :parent, :oof_field, 10},
            ]},
       ]}
   ]}
]}

pattern =[
   {:or, [
       {:or, [

            {:gt, :parent, :oof_field, 10},

            {:lt, :parent, :count_field, 8},
            {:eq, :child, :nib_field, 28},
       ]},
       {:and, [

            {:eq, :child, :other_field, "blah"},

            {:not_eq, :child, :blah_field, 10},

            ]},
       ]}
   ]}
]}

def build_where(parent, child,{:and, [first_clause | and_clauses]}) do
    Enum.reduce(and_clauses, build_where(parent, child, first_clause), fn(clause, acc) -> do

        acc and build_where(parent, child, clause)
    end)
end
def build_where(parent, child,{:or, [first_clause | and_clauses]}) do
    Enum.reduce(and_clauses, build_where(parent, child, first_clause), fn(clause, acc) -> do

        acc or build_where(parent, child, clause)
    end)
end
def build_where(parent, child,{:eq, :parent, field, val}) do
    field(parent, field) == val
end
def build_where(parent, child,{:eq, :child, field, val}) do
    field(child, field) == val
end

...

from parent in Parent,
     inner_join: child in assoc(parent, :children)
     where: build_where(parent, child, pattern)

It looks like the closest thing I can do is flatten the logic and then represent it though the macro syntax using where and or_where, but that seems like a pretty weak solution since it doesn't preserve the logical groupings and doesn't allow me to compose the where clause separately from the query.

Thanks,

Kyle

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.

[José Valim]

If you want a generic answer on how to convert your AST to Ecto Query AST then it is not possible today. Maybe you could try to venture into building %Ecto.Query.Expr{...} but it is a private API. For example, where will use BooleanExpr instead of Expr on 2.1. But if you want to have an idea of what it entails:

query = from p in Post, where: p.foo == ^"some value"

IO.inspect query.wheres

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/53f13300-61ea-4a77-8d17-5e9530e430b7%40googlegroups.com.

[Kyle Hanson]

OK thanks for letting me know even though it is sad news.

I am not sure of how Ecto will work in the future, but in other database wrappers WHERE clauses can be composed separately from Queries. For example, Django uses the Q object to compose WHERE which is separate from the QuerySet object. Its a powerful abstraction.

The biggest snags that I see see over and over from ecto when it comes to implementing new Query behavior are the limitation of the match clause binding syntax and not being able to pass the binding to something else. It causes a lot of code to be pushed to fragments. The recent addition of using the spread operator in the macro bindings (where([a, ..., b], ) helps but in my opinion hides a major problem lurking in ecto: the match syntax within the Query behaves fundamentally differently then the syntax outside of the query. Even the core premise of the macro syntax doesn't match the rest of Elixir

Element

   |> join(:inner, [e], assoc(e, :children)

   |> where([e, c1], blah == blah or blah == blah)

   # Everywhere else in Elixir you would expect this to cause an error since the 

   # list length doesn't match the match pattern.

   |> where([e], e.something == 9)
   # Can I do this?
   |> where(tables, tables[1].something == 9 and Enum.first(tables).blah <= 10 )

This leads to confusion since now you have to know how the Query syntax works differently than Elixir. This mismatch between the match clause and native Elixir is essentially what is causing my problem.

A slightly more ambiguous but more powerful way to generate queries would greatly benefit Ecto. 

Have you thought about more robust query builders that don't involve match bindings? I might take a crack at something small that generates Ecto AST and it would be nice to know if there are reasons behind the limitations of bindingless queries.

Thanks,

Kyle

[José Valim]

Yes, those are the two biggest issues with Ecto today. Joins and dynamic expressions (inside a where, for example).

Before I go into those, I would like to say that it is important that where([e], ...) does not raise in case of more bindings because it would negatively impact query composition. Imagine you would have to write where([e], e.public) for one binding, where([e, _], e.public) for two, etc.

Anyway, this ties to the problem of joins we mentioned above. There are two ways we can consider joins and both ways may be available at compile-time based or runtime.

1. The first mechanism is positional. That's how joins work today. Your solution of doing tables[0] and tables[1] is also based on position. The issue with position is that you may not know how many joins you had in the past and that's why we introduced "..."

2. The second mechanism is based on names. For example, whenever I use join: c in "comments", we could store the name of the join as "c" and allow a position match later on using maps: where(%{c: c}, c.private)

In any case, the solutions above are still limited if they are restricted to compilation time. And Ecto joins today *are* compile time limited.

I believe your tables suggestion is dangerous because you are considering where([a, b], ...) or where(tables, ...) to be an assignment on the first argument and that wouldn't work with Ecto. It makes sense for Python and Ruby, because the tables could be an object with entries being able to collect state at runtime, but that won't work with Elixir.

In other words, your tables suggestion is mixing runtime with compile time, which is a feature we absolutely need in some shape in Ecto, but it can be quite tricky to get right. And that's directly related with the inability to write complex and/or. Those require the ability the shape queries at runtime but we can't do it.

Sorry, that's a long answer to agree that the issue with Ecto is the inability to build queries at runtime. That reflects joins and wheres (and others). Being able to write tables[0] or write joins based on names may be an improvement but it won't be enough to solve the main problem of writing dynamic queries.

We should definitely support this feature but we never figured out exactly how to write it. Maybe one option is to actually expose Ecto's query expressions, allowing you to convert complex logic into trees, which could even be done using Elixir's quote/unquote.

One idea is to allow expressions to be composed and added dynamically. For example:

    part_a = dynamic([p, q], p.field == q.field)

    part_b = dynamic([p], p.other == ^value or ^part_a)

    from query, where: ^part_b

I am not convinced about the name "dynamic" though but I hope you get the idea. "fragment" would be a good one but it is already taken. :)

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/6003961d-5067-49de-9bb0-8812936d8e7d%40googlegroups.com.

[Michał Muskała]

Yes, the ecto queries are limited in the way joins work. The main reason is ecto wanting to do as much work as possible at compile-time to limit the runtime overhead. One of the main tasks being type checking the query.

A workaround, I use for the join limitation is a trick where you first join all the tables you need in the particular filter, and later refer to those using positions. It's a bit verbose, but it's possible to make to work correctly today. For example:

query = from f in Foo, join: a in assoc(f, :bar), left_join: b in assoc(a, :baz)

And later:

from [f, a, b] in query, where: a.id == ^a_id

This is not perfect and can get quite verbose, but extends the range of queries that can be expressed with ecto syntax.

As to other query builders, ecto has couple things that differentiate it from the other builders, I worked with: it's strongly typed and when using the query syntax it's impossible to expose oneself to SQL injection. That is true, even when using fragments - this is verified at compile-time. Ensuring those guarantees unfortunately limits the syntax in some ways (for example it's not possible to pass dynamic strings to fragment exactly to prevent SQL injection). Other builders, of course, also allow to be used in a safe way, e.g. .where("foo >= ?", foo) in Rails, but the amount of times, where I've seen .where("foo >= #{foo}") (or similar) in Rails, written by seasoned developers is surprisingly high.

Michał.

> To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/6003961d-5067-49de-9bb0-8812936d8e7d%40googlegroups.com.

[José Valim]

Support for dynamic expressions have been added to master. Currently we support only "where" but throughout the week I will add support for the remaining expressions. More information can be found here:

https://github.com/elixir-ecto/ecto/blob/2f5190dac96da71da62b8c03ec2ac72463b6fd47/lib/ecto/query.ex#L333

Notice you will need Ecto master for that.

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

> To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/6003961d-5067-49de-9bb0-8812936d8e7d%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.

To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/092F7E08-D9C3-4544-93C5-59219F3C4B02%40muskala.eu.