How can I use sub-queries in Exposed? I've tried a...

# exposed

[Stephcraft]

How can I use sub-queries in Exposed? I've tried alot of different things with aliases, but I always get an error. This is the sort of SQL query I am trying to write in Exposed DSL:

SELECT *
FROM Profile
ORDER BY (SELECT SUM(kills) FROM PlayerRecord WHERE PlayerRecord.player_uuid = Profile.uuid)

This is what I've got so far:

// the column to use to sort the results
val sortingColumn = PlayerRecordTable.kills.sum() // I've also trying aliasing this, but get an error as well.

// create sub-query (alias suposedly mandatory here for Exposed to work properly with subQueries)
val aggregatedPlayerRecords = PlayerRecordTable
    .select(sortingColumn)
    .where {
        PlayerRecordTable.gameplaySessionID eq GameplayRecordTable.id
    }
    .alias("aggregatedPlayerRecords")

// get the column in question for the subQuery, this is not mandatory in SQL if the result is 1 row, 1 column
val aggregatedSortingColumn = aggregatedPlayerRecords[sortingColumn] // <- crashes here: java.lang.IllegalStateException: Field not found in original table fields

// if I add .alias("aggregatedSortingColumn"), then it crashes later on
// where the generated query only contains alias 'tokens' not the actual expression of the subQuery in the alias ...
// error: Unknown column 'aggregatedPlayerRecords.sortingColumn' in 'order clause'

val results = GameplayRecordTable.selectAll().orderBy(sortingColumn to SortOrder.DESC)

I'm getting same issues as these, but no clear solution is ever provided:
• https://stackoverflow.com/questions/78003559/orderby-subquery-field-in-kotlin-exposed
• https://stackoverflow.com/questions/72463300/query-with-2-joins-and-subquery-using-kotlin-exposed
• https://github.com/JetBrains/Exposed/issues/1294
Thank you 🙏

[Oleg Babichev]

Not sure, but it's quite probable that it's not possible to pass subquery into orderBy now...

I was not able to make exactly the same sql query, but probably workaround via join could be acceptable for now:

object Profile : UUIDTable("test_profile")

    object ProfileRecord : UUIDTable("test_profile_record") {
        val kills = integer("kills").default(0)
        val profile = uuid("profile_id")
    }

val sumAlias = ProfileRecord.kills.sum().alias("sum_alias")

            val subAlias = ProfileRecord
                .select(ProfileRecord.profile, sumAlias)
                .groupBy(ProfileRecord.profile)
                .alias("sub")

            val ids = Profile
                .leftJoin(subAlias, onColumn = { Profile.id }, otherColumn = { subAlias[ProfileRecord.profile] })
                .select(Profile.columns + subAlias[sumAlias])
                .orderBy(subAlias[sumAlias] to SortOrder.DESC)
                .map { it[Profile.id] to it[subAlias[sumAlias]] }

[Oleg Babichev]

Created issue for now https://youtrack.jetbrains.com/issue/EXPOSED-367/SubQuery-inside-OrderBy-clause

[Stephcraft]

Thank you, Thank you 🙏 i will try the work-around

[Stephcraft]

You saved me, thank you so much @Oleg Babichev !