Note: The query was not possible dynimally with ecto according to some of my search
I am trying to implement query in this post with dynamic AND+OR parts. I really couldnt think of how to do it with dynamic AND+OR parts. I can get the AND + OR list dynamically like this [[first_or_1, first_or_2, first_or_3], [second_or_1, second_or_2]]. The lists have to be joined. Here are schemas
defmodule MainTable do
@moduledoc false
use Web, :model
schema "main_table" do
field(:name, :string)
many_to_many(:others, OtherTable, join_through: "bridge_table")
end
end
defmodule OtherTable do
@moduledoc false
use Web, :model
schema "other_table" do
field(:name, :string)
many_to_many(:mains, MainTable, join_through: "bridge_table")
end
end
defmodule BridgeTable do
@moduledoc false
use Web, :model
@derive Jason.Encoder
schema "bridge_table" do
belongs_to(:main, MainTable)
belongs_to(:other, OtherTable)
end
end
and here is simple join query
MainTable |> join(:inner, [m], bt in BridgeTable, on: bt.main_table_id == m.id) |> join(:inner, [m, ..., bt], ot in OtherTable, on: bt.other_table_id == ot.id)
I built my having part dynamically like this
"""? = COUNT( DISTINCT CASE WHEN ot.name = ANY ? THEN ot.name END )
OR ? = COUNT( DISTINCT CASE WHEN ot.name = ANY ? THEN ot.name END ) """
and the params part I get like this. two lists
[["name 1", "name 2"], ["name 3"]]
Problem:: The above query will work if it gets a correct reference to otther_table ie ot table but ecto with create query with some other reference eg other_table as o1
I can also convert the above having part to
"""? = COUNT( DISTINCT CASE WHEN ? = ANY ? THEN ? END )
OR ? = COUNT( DISTINCT CASE WHEN ? = ANY ? THEN ? END ) """
But how do I get/merge ot reference dynamically
Note: I created above sql like query so that I can use it with fragement as I dont find other way. and pass params eg
from q in query,
having: fragment(sql, params) // But how do I get reference of **ot** table here in params passed