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?

Ladislav Ksir · Accepted Answer · 2022-05-31T11:07:04.193

2

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())

edited May 31 '22 at 11:07

answered May 31 '22 at 09:53

Ladislav Ksir

106
4

this type of query returns `ResultRow` but `Int` is required – Andrey May 31 '22 at 10:38
You are right, I missed last part - to get the value out of the result row. Try the updated answer please. – Ladislav Ksir May 31 '22 at 10:59
thanks, I only replaced `maxByOrNull()` with `firstOrNull()` – Andrey May 31 '22 at 11:13
Agreed, that is much better for extracting one value. – Ladislav Ksir May 31 '22 at 11:19

Get the max value using kotlin-exposed

1 Answers1