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]

since the join_through option accepts an ecto model, i’m under the impression it should be possible to query against that model. However, everytime I’ve asked this question (in SO, in the IRC, and in Slack) most recommendations is to simply use explicit/manual queries, which is a bit of a PITA when I have to compose it with other queries.

Is this supported in ecto?

1 Like