Dynamic having query help needed

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

Take a look at this for building dynamic conditions: https://hexdocs.pm/ecto/Ecto.Query.html#dynamic/2

Thank you so much for a quick reply. But how would you join different AND+OR parts in a having using dynamic? I have tried but failed

condition_a = dynamic(…, …)
condition_b = dynamic(…, …)
condition_c = dynamic(…, …)
full_condition = dynamic(^condition_a or (^condition_b and ^condition_c))
query = from query, having: ^full_condition
4 Likes