How can I achieve the following query in exposed? ...

# exposed

[Stephcraft]

How can I achieve the following query in exposed?

SELECT AVG(subquery.gameplay_session_count) FROM `Profile`
    JOIN (
        SELECT player_uuid, COUNT(gameplay_session_id) AS gameplay_session_count FROM `GameplayRecord`
        JOIN `PlayerRecord` ON `GameplayRecord`.id=`PlayerRecord`.gameplay_session_id
        GROUP BY player_uuid
    ) AS subquery ON `Profile`.uuid=subquery.player_uuid

I can't seem to do the final AVG(subquery.gameplay_session_count) from a subquery column alias
My implementation with Exposed:

val countColumn = PlayerRecordTable.gameplaySessionID.count().alias("gameplay_session_count")
val aggregatedPlayerRecords = (GameplayRecordTable innerJoin PlayerRecordTable)
    .select(PlayerRecordTable.playerUUID, countColumn)
    .where(filter.predicate)
    .groupBy(PlayerRecordTable.playerUUID)
    .alias("aggregated_player_records")

val aggregatedIdColumn = aggregatedPlayerRecords[PlayerRecordTable.playerUUID]
val aggregatedCountColumn = aggregatedPlayerRecords[countColumn]

ProfileTable.leftJoin(aggregatedPlayerRecords, { id }, { aggregatedIdColumn })
    .select(aggregatedCountColumn.avg())
//                               ^ error here: can't use avg() on Expression<Long>
    .single()

Thanks 🙏

[Chantal Loncle]

Hi @Stephcraft I'm fairly certain I was able to make the right assumptions about your tables since I got the same error as you did with version 0.56.0.

So I can confirm that your implementation above should work with upcoming version 0.57.0. Running tests on main, the aggregatedCountColumn will become type ExpressionWithColumnType<Long>, so invoking avg() will become possible to generate the expected SQL.