Get the max value using kotlin-exposed

Question

I want to simulate this query using kotlin exposed framework:

SELECT max(episodes.season_number) FROM episodes WHERE episodes.parent_id = 944947

I tried the next one query:

fun countSeasonsForParentId(id: Int) = transaction {
   EpisodeTable.slice(EpisodeTable.season)
       .select { EpisodeTable.parentId eq id }
       .map { it[EpisodeTable.season] }.maxOrNull()
}

But this query just results in:

SELECT episodes.season_number FROM episodes WHERE episodes.parent_id = 944947

and the max value is selected in the code.

How to perform finding max value on the side of the database and map it to Int?

Answer 1

I tried similar query and I believe it fits your need.

The query is:

        Reservations
            .slice(Reservations.date.max())
            .select { Reservations.note eq "it" }
            .maxByOrNull { Reservations.date }

And it generates SQL query:

SELECT MAX(reservations."date") FROM reservations WHERE reservations.note = 'it'

Your query can be used as:

    EpisodeTable
   .slice(EpisodeTable.season.max())
   .select { EpisodeTable.parentId eq id }
   .maxByOrNull { EpisodeTable.season }
   ?.get(EpisodeTable.season.max())

Alternative, for extracting one value seems more appropriate:

   EpisodeTable
   .slice(EpisodeTable.season.max())
   .select { EpisodeTable.parentId eq id }
   .firstOrNull()
   ?.get(EpisodeTable.season.max())