Looking for a better solution to preload on many_to_many using sub-queries

Questions / Help
1

Hey folks,

I have a solution to a problem that I am not quite happy with.

Let’s say I have a schemaA and a schemaB, that have a many_to_many join through association using schemaA_schemaB.
The many_to_many schemaA_schemaB has one field called count that is also availble on schemaB using a virtual field.
Now, I have a query, where I want to get all associated schemaBs for each schemaA and preload them.

My query looks like this:

def get_schemaA(id) do
schemaB_subquery =
      from(b in schemaB,
        left_join: ab in schemaA_schemaB,
        on: ab.schemaB_id == b.id,
        select_merge: %{count: ab.count},
        where: ab.schemaA_id == ^id
      )

      query =
      from(a in schemaA,
        left_join: ab in schemaA_schemaB, on: a.id == ab.schemaA_id,
        where: a.id == ^id,
        preload: [
          [schemaBs: ^schemaB_subquery],
        ]
      )

    Repo.one(query)
end

I hope this makes my problem clear.

Cheers