I posted this to stackoverflow, but was told here would a better forum.
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)
The idea is that I would have filter_by_album_id,
filter_by_category_id, filter_by_person_id
, etc that could be called 0…n times depending on the search string the user entered.
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?
On stack overflow I received the comment " dynamic
expressions are allowed for the join
's :on
option", but really not sure how this could solve the problem at hand.