CODEWITHSUNDEEP
phoenix-frameworkecto
Secret
Secret

Reputation: 3358

Add a where clause on ecto assoc join table

I have three models Table1, Table2 and Table1to2. Table1to2 is a join table from Table1 to Table2, with the schema of Table1 and Table2 being:

schema "table1" do
  many_to_many :table2, Table2, join_through: Table1to2
end 

schema "table2" do
  many_to_many :table1, Table1, join_through: Table1to2
end

The key thing is that my join table, Table1to2 has a column/field which I need to query against. Essentially, I want to do something like this:

Repo.get!(Table1, id) |> Repo.preload(table2: (from j in Table1to2, where: [main: true]))

This understandably does not work because there is no direct defined association from Table1 to Table1to2. Doing this however:

Repo.get!(Table1, id) |> Repo.preload(table2: (from j in Table2, where: [main: true]))

results in this query:

from p0 in Table1,
  join: u in Table1to2,
  on: u.table1_id == p0.id,
  join: p1 in Table2,
  on: u.table2_id == p1.id,
  where: p1.main == true,        #should be u.main == true
  where: p0.id == ^"2",
  select: p0,
  preload: [table_2: p1]

Upvotes: 1

Views: 2526

Answers (1)

Alex Garibay
Alex Garibay

Reputation: 391

You can always use the query syntax like the resulting query you posted to fit your needs.

query = 
  from t1 in Table1,
  join: t12 in Table1to2, on: t12.table1_id == t1.id,
  join: t2 in Table2, on: t12.table2_id == t2.id,
  where: t12.main == :true and t1.id == ^table1_id,
  preload: [table2: t1],
  select: t1

result = Repo.one(query)

Upvotes: 2

Related Questions