Is this query possible in Ecto?

Hi

I am putting together a dynamic query with an arbitrary number of conditions

SELECT v.id
FROM visitors v JOIN
     trackings t
     ON t.visitor_id = v.id
WHERE v.app_id = '123' AND

      -- N conditions..
      ( (t.field = 'admin' AND t.boolean_value) OR
        (t.field = 'users_created' AND t.integer_value > 1) OR
       ...
        (t.field = 'username' AND t.string_value = 'jack')
      )
GROUP BY v.id
HAVING COUNT(*) = <number of conditions>

But I don’t seem to be able to find a way to AND with the OR’ing of the conditions using composite queries. Any ideas?

3 Likes

Gave it a quick try (with less conditions) and see no problems. The only thing I couldn’t directly translate to Ecto was the count(*) part for which I resorted to count(fragment("*")). Do you have a snippet of your code, I’m curious what error you get?

1 Like

Have totally the same question - wrote it cause didn’t find yours.

Hi mate, could you show an example?

It’s not that I get an error, I just that I haven’t found a way to accomplish it in Ecto

Nothing special about it, it’s almost a one-to-one translation:

query = from v in Shipment, 
  select: v.id, 
  where: (v.size == ^10 and v.kind == "package") or (v.kind == ^"pallet"), 
  group_by: v.id, 
  having: count(fragment("*")) == ^1
1 Like

Yes, it’s easy if you can build it in one query generation pass. But what if you need to composite query? Like in the situation I described here.

1 Like

Ecto 2.1 (there is a rc out) supports or_where . So let’s assume you have your n conditions defined as follows:

conditions =
  [{"admin", :boolean}, {"users_created", {:integer, 1}}, {"username", {:string, "jack"}}, ...]

What you need to do is to convert the data structure above into a query construct. Assuming you already have a query in the variable named query, we can do that with reduce:

query =
  Enum.reduce(conditions, query, fn
    {field, :boolean}, query ->
      query |> or_where([q], q.field == ^field and q.boolean_value) 
    {field, {:integer, integer}}, query ->
      query |> or_where([q], q.field == ^field and q.integer_value > ^integer) 
    {field, {:string, string}}, query ->
      query |> or_where([q], q.field == ^field and q.string_value == ^string)
    ... ->
      ...
  end)

Depending on what you are building, you can clean up that code a bit but I am leaving it “dirty” for now and you can choose how to refactor it later.

With the query built, now you just need to group by and having:

query |> group_by([q], q.id) |> having([q], count(q.id) == ^length(conditions))

In case you really need count(*) (I don’t think you do since you are grouping by id), you can use a fragment as others have said.

4 Likes

Hi Jose

Thanks for the answer. But as far as I can see, your example does not respect the parentheses around the OR queries, which is important

Also, the HAVING clause is because I want visitors for which there exists a tracking for all of the OR conditions (that’s why I count the number number of returned trackings and make sure it is equal to the amount of conditions specified.

2 Likes

Good catch. For now you can also move the v.app_id == 123 to inside each OR. I think the query engine will be able to optimize it anyway.

1 Like

My bad for the contrived example.

In reality there can also be N number of AND conditions outside the OR group, so I dont think that could work

If that’s the case, then it is not possible right now as well.

Good news!

Ecto master now allows developers to build dynamic queries. Here is an example from the documentation:

dynamic = false

dynamic =
  if params["is_public"] do
    dynamic([p], p.is_public or ^dynamic)
  else
    dynamic
  end

dynamic =
  if params["allow_reviewers"] do
    dynamic([p, a], a.reviewer == true or ^dynamic)
  else
    dynamic
  end

from query, where: ^dynamic

This means you should now be able to write the query above by building each dynamic piece bit by bit and then later adding to a given query field.

Currently we allow dynamic expressions only in where but we will add support for other expressions throughout the week. 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. If you have any questions, please let me know.

7 Likes

Awesome, great work as always!

1 Like

This is excellent, thank you!