tanweerdev

tanweerdev

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

Showing Posts 3 to 1

LostKobrakai

LostKobrakai

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
tanweerdev

tanweerdev OP

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

LostKobrakai

LostKobrakai

Take a look at this for building dynamic conditions: Ecto.Query — Ecto v3.14.0

— All posts loaded —

Where Next? Top

Trending in Questions Top

RSP87
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
New
nseaSeb
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New
apz
I’m new to elixir and just tried to install the elixirLS extension for VScode(ium) and it is throwing some errors that I would like help ...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
New
JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
marciok
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews