Adding Group By clause in preload

Question

I have a model that has a many to many association through a HABTM join table. However, my join table does not uniquely map my row, hence I need to add a group_by clause to get my unique rows.

Is there a way to add a group by expression when using Repo.preload?

Repo.get!(P1, p1_id)
|> Repo.preload(:c0)

Generated query:

SELECT 
    c0.*, p1."id" FROM "c0" AS c0
LEFT JOIN "p1" AS p1
    ON p1."id" = 2
LEFT  JOIN "c2" AS c2
    ON c2."p1" = p1."id"
WHERE (c2."c1_id" = c0."id")

Target query:

SELECT 
    c0.*, p1."id" FROM "c0" AS c0
LEFT JOIN "p1" AS p1
    ON p1."id" = 2
LEFT  JOIN "c2" AS c2
    ON c2."p1" = p1."id"
WHERE (c2."c1_id" = c0."id")
GROUP BY c0."id", p1."id"

Answer 1

New to this, I haven't tested on local but I think it is achievable as following;

query = from p in P1,
        join: c in assoc(p, :c0)
        group_by: p.id
        preload: [c0: c]
Repo.get(query, p1_id)