Write query with HAVING clause in Kotlin with Exposed

I have written an SQL query in which I use the HAVING clause.

However, I have not found practical examples of the use of HAVING in the Exhibited documentation.

My query should return the following:

Disputes for an order that has the most recent status of 'CAPTURED' or 'EXPIRED'

SELECT pc.*
FROM "pedido" p
INNER JOIN pedido_contestacao pc ON t.id = pc.pedido_id
WHERE p.number = '1234'
GROUP BY pc.id
HAVING (
   SELECT status
   FROM contestacao_event ce
   WHERE ce.pedido_contestacao_id = pc.id
   ORDER BY ce.created_at DESC
   limit 1
   ) IN ('CAPTURED', 'EXPIRED')

My biggest difficulty is writing the HAVING sub query. I saw that there is a function called wrapAsExpression, but I can't use it in this scenario. My query so far looks like this:

val contestacaoEventTable = ContestacaoEventTable.alias("det")
val pedidoContestacaoTable = PedidoContestacaoTable.alias("tdt")

val subQuery = contestacaoEventTable
    .slice(contestacaoEventTable[ContestacaoEventTable.status])
    .selectAll()
    .andWhere {
        ContestacaoEventTable.id eq pedidoContestacaoTable[PedidoContestacaoTable.id]
    }.orderBy(ContestacaoEventTable.createdAt to SortOrder.DESC)
    .limit(1)
    .alias("statusQuery")

val status = contestacaoEventTable[ContestacaoEventTable.status]
PedidoTable
    .innerJoin(PedidoContestacaoTable)
    .slice(PedidoContestacaoTable.columns)
    .selectAll()
    .groupBy(PedidoContestacaoTable.id)
    .andWhere { PedidoTable.number eq '1234' }
.having {
        // wrapAsExpression(subQuery.slice(status).selectAll()).inList(listOf("CAPTURED", "EXPIRED"))
}.map {
    println(it[PedidoContestacaoTable.id])
}

I don't know what else to do to make it work.

Solution

sorry for the delay! You are the man! Saved my development!

I made some adaptations to your suggestion.

My solution was as follows:

fun <T : Any> wrapAsExpressionWithColumnType(query: Query, columnType: IColumnType) =
    object : ExpressionWithColumnType<T?>() {
        private val expression = wrapAsExpression<T>(query)
        override fun toSQL(queryBuilder: QueryBuilder) = expression.toSQL(queryBuilder)
        override val columnType: IColumnType = columnType
    }

And the final code for the query:

transaction {
    val contestacaoEventTable = ContestacaoEventTable.alias("det")
    val pedidoContestacaoTable = PedidoContestacaoTable.alias("tdt")

    val contestacaoEventTransacaoId = contestacaoEventTable[ContestacaoEventTable.contestacaoTransacaoId]
    val contestacaoEventCreatedAt = contestacaoEventTable[ContestacaoEventTable.createdAt]
    val contestacaoEventStatus = contestacaoEventTable[ContestacaoEventTable.status]

    val pedidoContestacaoId = pedidoContestacaoTable[PedidoContestacaoTable.id]
    val pedidoContestacaoTransacaoId = pedidoContestacaoTable[PedidoContestacaoTable.transacaoId]

    val subQuery = contestacaoEventTable
        .slice(contestacaoEventTable[ContestacaoEventTable.status])
        .selectAll()
        .andWhere { contestacaoEventTransacaoId eq pedidoContestacaoId }
        .orderBy(contestacaoEventCreatedAt to SortOrder.DESC)
        .limit(1)
        .alias("subQuery")
    PedidoTable
        .innerJoin(pedidoContestacaoTable, { PedidoTable.id }, { pedidoContestacaoTransacaoId })
        .slice(pedidoContestacaoId)
        .selectAll()
        .groupBy(pedidoContestacaoId)
        .andWhere { PedidoTable.number eq '1234' }
        .having {
            wrapAsExpressionWithColumnType<String>(subQuery.query, contestacaoEventStatus.columnType)
                .inList(listOf(PedidoStatus.CAPTURED.name, PedidoStatus.EXPIRED.name))
        }.toList().size
}

Thanks a lot for the help . Hope I can help other people.