groupingBy a join in KTORM

Question

I want to join a N x M relation, returning all N, and a count of how many M are there:

SELECT N.*, COUNT(M.id) FROM N LEFT JOIN M ON N.id = M.n_id GROUP BY N.id

but I don't see how. Joining and aggregation seem to exclude each other, or are not implemented, or not documented (or I didn't find it).

score 0 · Answer 1 · answered Jun 22 '23 at 09:42

Well, I asked ChatGPT, and it gave enough hits, so that I could actually do this.

val resultSet = database.from(N)
    .leftJoin(right = M, on = N.id eq M.nId)
    .select(N.columns + count(M.id))
    .groupBy(N.id)

Referencing the count column is a bit more tricky:

val countColumn = ColumnDeclaringExpression(
     expression = count(),
     declaredName = "count",
     sqlType = IntSqlType,
     isLeafNode = false,
     extraProperties = mapOf()
   )

resultSet.map{ it[countColumn] } // for each row, this returns the count

hope it helps someone!

groupingBy a join in KTORM

1 Answers1