I am trying to compose a dynamic query involving a dynamic table join (photos to albums). My first attempt works only on one to many (photos to place):
defmodule Test1 do
def filter_by_place_id(dynamic, id) do
dynamic([p], ^dynamic and p.place_id == ^id)
end
end
dynamic =
true
|> Test1.filter_by_place_id(248)
But this will not work for many to many fields. Which I believe require a table join. So my next attempt:
defmodule Test2 do
def filter_by_place_id({query, dynamic}, id) do
dynamic = dynamic([p], ^dynamic and p.place_id == ^id)
{query, dynamic}
end
def filter_by_album_id({query, dynamic}, id) do
query = join(query, :inner, [p], album in assoc(p, :albums), as: :x)
dynamic = dynamic([{:x, x}], ^dynamic and x.id == ^id)
{query, dynamic}
end
end
query = from(p in Photo)
{query, dynamic} =
{query, true}
|> Test2.filter_by_place_id(248)
|> Test2.filter_by_album_id(10)
|> Test2.filter_by_album_id(11)
But this fails because the binding :x is hard coded, and obviously I can't reuse it. But I need a binding to ensure that the where clause refers to the correct join.
But if I try to use as: ^binding instead of as :x, I get the error:
** (Ecto.Query.CompileError) `as` must be a compile time atom, got: `^binding`
(ecto 3.6.2) expanding macro: Ecto.Query.join/5
meow.exs:30: Test2.filter_by_album_id/2
So I am not sure where to go from here. Is it possibly to dynamically allocate the binding for a join?
The other answer gave me an idea, drop the dynamic and just use multiple where clauses. I wasn't sure this would be supported. It looks like multiple where clauses get anded together.
def filter_by_value({query, dynamic}, field, id) do
query = join(query, :inner, [p], album in assoc(p, ^field.id))
|> where([p, ..., x], x.id == ^id)
{query, dynamic}
end
Unfortunately this removes the flexibility you get with dynamic, which I would really like, but for now this is an adequate solution.