```SQLite doesn't support ORDER BY in GROUP_CONCAT...

# exposed

[Stephcraft]

SQLite doesn't support ORDER BY in GROUP_CONCAT function., dialect: SQLite.

How can I get around this error?

ChatGPT recommended:

SELECT group_concat(value)
FROM (SELECT value FROM your_table ORDER BY value ASC)

But I'm not sure how to convert it into subqueries in Exposed.

I've tried:
From

val namesColumn = ProfileTable.name.groupConcat(separator = ", ", orderBy = ProfileTable.name to SortOrder.ASC).alias("names")

To

val names = ProfileTable
    .select(ProfileTable.name)
    .orderBy(ProfileTable.name to SortOrder.ASC)
    .alias("names_unordered")

val namesColumn = names[ProfileTable.name].groupConcat(separator = ", ").alias("names")

But this is yet another subquery that I must plug into the SELECT of a bigger query.

The query before my changes:
SQL

SELECT *
FROM GameplayRecordTable
INNER JOIN (
    SELECT id, round, GROUP_CONCAT(
        ProfileTable.name ORDER BY ProfileTable.name ASC
        SEPARATOR ', '
    ) AS names
        *
    FROM PlayerRecordTable
    INNER JOIN GameplayRecordTable ON PlayerRecordTable.some_column = GameplayRecordTable.some_column
    INNER JOIN ProfileTable ON GameplayRecordTable.some_column = ProfileTable.some_column
    GROUP BY GameplayRecordTable.id
    ORDER BY GameplayRecordTable.round DESC, GameplayRecordTable.end ASC
) AS gameplay_record_names
ON GameplayRecordTable.id = gameplay_record_names.id
        *
WHERE GameplayRecordTable.map = 'some_map_name'
    AND GameplayRecordTable.players = 'some_squad_capacity'
    AND GameplayRecordTable.end > 'some_start_of_period'
    AND GameplayRecordTable.hacked = 0
        *
GROUP BY GameplayRecordTable.map, gameplay_record_names.names
ORDER BY GameplayRecordTable.round DESC, GameplayRecordTable.end ASC
LIMIT 10

Exposed

val namesColumn = ProfileTable.name.groupConcat(separator = ", ", orderBy = ProfileTable.name to SortOrder.ASC).alias("names")

val gameplayRecordNames = (PlayerRecordTable innerJoin GameplayRecordTable innerJoin ProfileTable)
    .select(GameplayRecordTable.id, GameplayRecordTable.round, namesColumn)
    .groupBy(GameplayRecordTable.id)
    .orderBy(GameplayRecordTable.round to SortOrder.DESC, GameplayRecordTable.end to SortOrder.ASC)
    .alias("gameplay_record_names")

GameplayRecord.wrapRows(
    GameplayRecordTable
        .innerJoin(gameplayRecordNames, onColumn = { GameplayRecordTable.id }, otherColumn = { gameplayRecordNames[GameplayRecordTable.id] })
        .selectAll() //.select(GameplayRecordTable.columns + gameplayRecordNames[namesColumn])
        .where {
            val isMap = (GameplayRecordTable.map eq map.name)
            val isSquadType = if(squadType?.capacity == null) null else (GameplayRecordTable.players eq squadType.capacity)
            val isPeriodCategory = if(period?.startOfPeriod == null) null else (GameplayRecordTable.end greater period.startOfPeriod)
            val isNotHacked = (GameplayRecordTable.hacked eq false)

            listOfNotNull(
                isMap,
                isSquadType,
                isPeriodCategory,
                isNotHacked
            ).reduce { a, b -> a and b }
        }
        .groupBy(GameplayRecordTable.map, gameplayRecordNames[namesColumn])
        .orderBy(GameplayRecordTable.round to SortOrder.DESC, GameplayRecordTable.end to SortOrder.ASC)
        .limit(limit)
).toList()

Generated SQLite query (with the workaround attempt):

Statement(s): SELECT GameplayRecord.id, GameplayRecord."start", GameplayRecord."end", GameplayRecord.duration, GameplayRecord.hacked, GameplayRecord.players, GameplayRecord.round, GameplayRecord."map", gameplay_record_names.id, gameplay_record_names.round FROM GameplayRecord INNER JOIN (SELECT GameplayRecord.id, GameplayRecord.round, GROUP_CONCAT(names_unordered."name" SEPARATOR ', ') names FROM PlayerRecord INNER JOIN GameplayRecord ON GameplayRecord.id = PlayerRecord.gameplay_session_id INNER JOIN Profile ON Profile.uuid = PlayerRecord.player_uuid GROUP BY GameplayRecord.id ORDER BY GameplayRecord.round DESC, GameplayRecord."end" ASC) gameplay_record_names ON GameplayRecord.id = gameplay_record_names.id WHERE (GameplayRecord."map" = ?) AND (GameplayRecord.hacked = ?) GROUP BY GameplayRecord."map", gameplay_record_names.names ORDER BY GameplayRecord.round DESC, GameplayRecord."end" ASC LIMIT 1

As you can see it only uses the alias, it does not plug in the actual subquery: GROUP_CONCAT(names_unordered."name" SEPARATOR ', ')

Thanks alot 🙏

[Stephcraft]

Also the syntax is wrong for SQLite:

GROUP_CONCAT(Profile."name" SEPARATOR ', ')

But should be:

GROUP_CONCAT(Profile."name", ', ')

[Chantal Loncle]

Hi @Stephcraft The generated SQL for groupConcat() is inaccurate on a few counts. As you've mentioned, it's using the wrong separator syntax. Also ORDER BY has been supported by the SQLite function since version 3.44.0. A fix with these changes will be pushed soon (EXPOSED-569).

Regarding the first SQL you mentioned as a workaround:
Exposed does not currently support selecting from derived tables (meaning a subquery in the FROM clause).
I tried to compare the next set of SQL that you provided above, but I'm still not sure where exactly you meant when you said "it does not plug in the actual subquery" and what exact subquery you meant either (the one for the derived table?).
I'm not sure how accurate the results would be, but I've also seen that using window functions may be an alternative.

If you're using an SQLite version that actually supports the syntax you initially wanted, the easiest workaround would be to just implement a correct version of groupConcat(). Then you could swap this out when the fix is released:

fun <T : String?> Expression<T>.groupConcatTemp(
    separator: String? = null,
    vararg orderBy: Pair<Expression<*>, SortOrder> = emptyArray()
): GroupConcatTemp<T> = GroupConcatTemp(this, separator, orderBy = orderBy)

class GroupConcatTemp<T : String?>(
    val expr: Expression<T>,
    val separator: String?,
    vararg val orderBy: Pair<Expression<*>, SortOrder>
) : Function<String>(TextColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            +"GROUP_CONCAT("
            +expr
            separator?.let { +", '$it'" }
            if (orderBy.isNotEmpty()) {
                +" ORDER BY "
                orderBy.appendTo { (expression, sortOrder) ->
                    append((expression as? ExpressionAlias<*>)?.alias ?: expression)
                    +" ${sortOrder.code}"
                }
            }
            +")"
        }
    }
}

[Stephcraft]

Hey @Chantal Loncle I appreciate your punctual answer. I've seen it then, but only implemented the solution just now. It works! 🙌 Thanks a lot for showing me this trick and providing me with the functioning solution. I'll definitely be using this method if I come around any blockers similar to this one moving forward. Your help has been really helpful, thanks alot, and your punctuality is very much appreciated, have a nice day!

[Chantal Loncle]

Hey, glad it works and happy to help 👍 Fix will be included in next end-of-month release.