pagination and total count

[bayang]

Hi,

is it possible to retrieve the total count of items matching a query and in the same request get one page by offsets ?

I'd like to plug something like spring data but at the moment I have to make two queries, one for the count() and one for the data

I was thinking maybe of something like count() over(), does it exists in exposed ? https://dba.stackexchange.com/questions/48676/what-is-the-difference-between-count-and-count-over

[AlexeySoshin]

If this is something Spring Data able to do (get both total count and the page results), I would be happy to take a look at that.

But from what I remember while using Spring, it was issuing two queries.

[bayang]

I mentioned spring data as an example of what I would like to do.

I actually also use 2 queries, I believe you about spring and I'm not surprised they also have to issue 2 queries. While doing some research I stumbled upon this count() over() thing I didn't knew before so I was wondering if it could be possible to use it in exposed.

My actual code is there for reference : https://github.com/bayang/jelu/blob/main/src/main/kotlin/io/github/bayang/jelu/dao/BookRepository.kt#L134

[Tapac]

I think you can try to declare your own CountOver expression and check what it will return:

class CountOver(val partitionBy: Expression<*>? = null, val orderBy: Pair<Expression<*>, SortOrder>? = null) : Function<Long>(LongColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        +"COUNT (*) OVER ("
        partitionBy?.let {
            append("PARTITION BY ", it)
        }
        orderBy?.let { (exp, order) ->
            append("ORDER BY ", exp, " ", order.code)
        }
        +")"
    }
}

val count = Count(UserTable.id)
val countOver = CountOver(UserTable.id)
UserTable.slice(count, countOver).selectAll().groupBy(UserTable.email).map {
   it[count] to it[countOver]
}

It's just an example and I didn't run the code, so maybe you'll need to adjust it.

[Intex32]

What is the outcome of this suggestion? Is it possible to get both total count and current page's rows in one query with the OVER clause? @bayang In my project I'm issuing two queries because I found no satisfying alternative.

[bayang]

No, I didn't manage to do it in one query. I'm still using two queries at the moment. But I'm not very proficient at sql so somebody else might have succeeded.

[bog-walk]

@bayang Window functions have been available since version 0.42.0, so it's possible to retrieve both total matching count and pagination results as per the SO suggestions:

object ItemTable : IntIdTable("item_table") {
    val name = varchar("item_name", 32)
}

transaction {
    val items = listOf("A1", "B1", "A2", "A3", "C1", "D2", "A4", "A5")
    ItemTable.batchInsert(items) {
        this[ItemTable.name] = it
    }

    val totalCount = ItemTable.id.count().over().alias("total_count")
    ItemTable
        .slice(ItemTable.name, totalCount)
        .select { ItemTable.name like "A%" }
        .orderBy(ItemTable.id)
        .limit(6, 2)
        .map {
            it[ItemTable.name] to it[totalCount]
        }
// generates SQL (SQL Server)
// SELECT item_table.item_name, COUNT(item_table.id) OVER() total_count FROM item_table WHERE item_table.item_name LIKE 'A%' ORDER BY item_table.id ASC OFFSET 2 ROWS FETCH NEXT 6 ROWS ONLY

// generates output
// (A3, 5), (A4, 5), (A5, 5)
}

If you run into any issues/limitations when using window functions, please consider reporting them on YouTrack.