How to combine two dynamic queries in a where clause?

Questions / Help
, ,
1

I want to create 2 dynamic queries using the Ecto.Query.dynamic/2 macro.

I have this code

  def get_link(original, custom) do
    filter_original = filter_original(original)

    filter_link = filter_link(custom)

    from u in "urls",
      join: l in "links",
      on: l.url_id == u.id,
      where: ^filter_original,
      where: ^filter_link,
      select: %{identifier: l.identifier}
  end

  def filter_original(url) do
    dynamic([u, l], u.original == ^url)
  end

  def filter_link(nil) do
    dynamic([u, l], l.custom == false)
  end

  def filter_link(custom) do
    dynamic([u, l], l.custom == true and l.identifier == ^custom)
  end

Here I use two where clauses to combine the two filters and this works perfectly. But now I want to combine those filters in one where clause. So I replace those two lines with this

      where: dynamic([u, l], ^filter_original and ^filter_link),

but it doesn’t even compile. When I try to recompile the module I get

** (Ecto.Query.CompileError) `%Ecto.Query.DynamicExpr{fun: fn query ->
  _ = query
  {{:and, [], [{:^, [], [0]}, {:^, [], [1]}]}, [{filter_original, :boolean}, {filter_link, :boolean}]}
end, binding: [{:u, [line: 24], nil}, {:l, [line: 24], nil}], file: "/home/voger/projects/tinyclone/tinyclone_backend/lib/tinyclone/shortener.ex", line: 24}` is not a valid query expression.

* If you intended to call a database function, please check the documentation
  for Ecto.Query to see the supported database expressions

* If you intended to call an Elixir function or introduce a value,
  you need to explicitly interpolate it with ^

    (ecto) expanding macro: Ecto.Query.where/3
    (tinyclone) lib/tinyclone/shortener.ex:21: TinyClone.Shortener.get_link/2
    (ecto) expanding macro: Ecto.Query.select/3
    (tinyclone) lib/tinyclone/shortener.ex:21: TinyClone.Shortener.get_link/2
    (ecto) expanding macro: Ecto.Query.from/2
    (tinyclone) lib/tinyclone/shortener.ex:21: TinyClone.Shortener.get_link/2
    (iex) lib/iex/helpers.ex:436: IEx.Helpers.do_r/1
    (iex) lib/iex/helpers.ex:415: IEx.Helpers.r/1

I don’t understand what is wrong in this case and how to fix it.

1 Like
2

This is the way to do it, or you can just combine them up into another singular dynamic query too. Two where:'s are equal to where: ^first and ^second. Dynamics just need to be top level for a given where: expression is all as they are expressions and not values.

4 Likes
3

Isn’t this line

where: dynamic([u, l], ^filter_original and ^filter_link),

combining the two queries in a singular dynamic query? Is there any other way to do it?

4

I don’t think so, I think because dynamic is inside the where: expression then it gets expanded and interpolated like a normal query instead of a dynamic expression. Maybe adding a ^ in front of dynamic to become ^dynamic might work there? I’d do it outside of it though. But really, nothing wrong with two where:'s, they are equivalent.

A PR might be accepted to change this though, ask about it to the dev’s first? Issue tracker perhaps?

2 Likes
5

Adding the ^ did the trick. A detail I missed in the docs.

Thank you very much.

1 Like