voger
How to combine two dynamic queries in a where clause?
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.
Marked As Solved
OvermindDL1
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?
Also Liked
OvermindDL1
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.
voger
Adding the ^ did the trick. A detail I missed in the docs.
Thank you very much.







