Grouping where and or_where clauses

Hello again!

Within less than a day I realized that my advantures haven’t finished yet :smile:

Yesterday I asked a question about binding joins and - thanks to the docs and José Valim - found an answer.

But now I have another obstacle. I need to solve it in the same way (subsequent parse and extending base query) as in the prevoius question by the link above.

For example, I can make an SQL query like this:

SELECT *
FROM cities as c
WHERE c.founded > '1900-01-01' AND c.founded < '1950-12-31' AND (c.population > 1000000 OR c.has_landmark = true)

If I would do it in the one query, I build it like this: q = from c in Cities, where: c.founded > ^date1 and c.founded < ^date2 and (c.population > 1000000 or c.has_landmark = true)

But how to do it in subsequent passes? I will have a number of AND parts as well as a number of OR parts. I can pipe AND parts first and OR pass after. There will joins in addition to almost every where/or_where clauses.

So, is it possible to do it subsequently or it’s only possible to build the whole query at once with that conditions?

Many thanks in advance!

1 Like

To be honest, on second thoughts I can’t imagine how to make all in one query as I don’t know how many parts I will need to include in the OR group…

@heathen can you give a more detailed example? Because the recursion you have set up in the previous question should be enough to allow you do it step by step. Also please see my reply on this other question: Is this query possible in Ecto?

1 Like

Sure, thanks for the attention!

I already saw that answer but still don’t get it, sorry.

I’ll try to explain in more details my situation.

So I’m doing search requests from web front-end and take it as a map like this:

%{
  q: %{
    offset:        0,
    limit:         10,

    # AND part - restrictions
    date_from:      '2007-31-23',
    date_till:      nil,
    grnti:          [],
    oecd_fos:       ["0101PJ", "0102LJ"],

    str:            "some string to search",

    # OR part - variations
    in_names:       true,
    in_annotations: false,
    in_keywords:    false,
    in_customers:   true,
    in_contractors: true,
    in_regnums:     true,
    in_authors:     false,
  }
}

I need to parse this request and make a query, which will restrict records with values from AND part and return ANY records from the set with applied restrictions with str inclusion in the corresponding fields which is marked by true. By the way, some fields come from the main model itself, but many - from left join associations.

The map I wrote - is just a part of the bigger one, but principles will be the same.

So in the simple query I showed in the first post we have two parts: with restrictions and with variations. Both parts has to be built with composition (or, may be you could offer me another way to parse all request fields when it’s not possible to know how many fields will be ‘true’ in advance?).

And again - many thanks for your attention to beginners! :+1:

1 Like

I believe the secret is to break the or and and parts apart:

params = params["q"]
ands = params |> Map.take([:date_from, :date_till, :grnti, :oecd_fos, :str]) |> Enum.to_list
ors = params |> Map.take([:in_names, ...]) |> Enum.to_list
query = City

Now you will have a reduce loop for ands and then ors:

query =
  Enum.reduce(ands, query, fn
    {:date_from, value}, query ->
      from q in query, where: q.date_from > ^value
    # One clause for each and parameter
    ... ->
      ...
  end)

Then you do exactly the same for ors:

query =
  Enum.reduce(ors, query, fn
    {:in_names, true}, query ->
      from q in query, or_where: q.in_names == ^true
    # One clause for each and parameter
    ... ->
      ...
  end)

It doesn’t really matter if it the clause inside the reduce needs to do a join or something else, it should just work.

There is a more sequential/“imperative” way you could write the code above too. It may be simpler to understand conceptually:

query = City
params = params[:q]

# Write an if for every AND
query =
  if date_from = params[:date_from] do
    from q in query, where: ...
  else
    query
  end

query =
  if date_other = params[:date_other] do
    from q in query, where: ...
  else
    query
  end

# ...

# Write an IF for every OR
query =
  if params[:in_names] do
    from q in query, or_where: ...
  else
    query
  end

# ...

query
2 Likes

Jose,

probably I just don’t understand what you mean, but as far as I can see (and from my research) where and or_where will be just added as is, sequentially. But we need to use parentheses (please take a look at the first SQL example) to restrict result set by the field in AND part and allow at the same time to have any combination of the fields from the OR part but from this restricted set only.

My bad, I can’t explain in a normal way, probably. Sorry, it’s my bad English. :frowning:

If we use where and or_where sequentially, I believe we will get a set with records which strictly correspond to all restrictions from AND part and at the same time any records which correspond to one of the OR clauses but not any of the AND fields.

So it will be like this (no parentheses):

SELECT *
    FROM cities as c
    WHERE 
    c.founded > '1900-01-01' AND c.founded < '1950-12-31' 
    AND c.population > 1000000 
    OR c.has_landmark = true

But actually I need something like this - with parentheses:

SELECT *
    FROM cities as c
    WHERE 
      c.founded > '1900-01-01' AND c.founded < '1950-12-31' 
      AND ( c.population > 1000000 OR c.has_landmark = true )

May be it will be better to get this example search query map and show how would look SQL request in this case:

SELECT *
  FROM nioktr as nioktr
  LEFT OUTER JOIN nioktr_to_oecd AS nioktr_oecd ON nioktr.id = nioktr_oecd.nioktr_id
  LEFT OUTER JOIN spr_oecd AS oecd ON oecd.id = nioktr_oecd.spr_oecd_id
  LEFT OUTER JOIN spr_org as customer ON nioktr.customer_id = customer.id
  LEFT OUTER JOIN spr_org as contractor ON nioktr.contractor_id = contractor.id

  WHERE
      oecd.id = ANY(ARRAY['0101PJ', '0102LJ'])
  AND nioktr.date_from < '2007-31-23'
  AND
      (
           nioktr.name ilike '%some string to search"%'
        OR nioktr.regnum ilike '%some string to search"%'
        OR customer.f_name ilike '%some string to search"%'
        OR contractor.f_name ilike '%some string to search"%'
      )
  OFFSET 0
  LIMIT 10

Is it possible to repeat the same with Ecto.Query?

Oh, I see. No, I don’t think it is possible right now.

2 Likes

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.

10 Likes

Really good news!
I’ll look into this as soon as I can, thank you very much!

Sorry to resurrect an old thread, but I’m facing a similar issue composing this query (which is used in a larger composed query). I’m using Ecto.Query to implement an AND and grouped OR query, similar to

SELECT *
    FROM cities as c
    WHERE 
      c.founded > '1900-01-01' AND c.founded < '1950-12-31' 
      AND ( c.population > 1000000 OR c.has_landmark = true ) #this is what I'm trying to replicate

I tried chaining or_where/2, like so:


or_where(query, status: "Locked") |> or_where(status: "Unlocked") |> or_where(status: "Closed")

but, the composed query above are not grouped OR statements.

I tried dynamic/2, but received the protocol Ecto.Queryable not implemented for dynamic error).

dynamic([q], q.status == "Locked" or q.status == "Unlocked" or q.status == "Closed")

update: found the solution, all I had to do was use a keyword list.

or_where(query, [status: "Locked", status: "Unlocked", status: "Closed"])
2 Likes