Supporting keyset pagination via row-wise multi-column comparisons

[nhoughto]

We are looking at trying to build on micronaut-data and extending the AOT codegen behaviours to reduce a bunch of our boilerplate around custom pagination, ordering, filtering etc in our project. We expect to do this via tweaking the compile time AOT data-processor via ServiceLoaders.

Seems like we should be able to do this with the extension points that already exist, this was more a question on the projects position on this kind of thing? Is there a plan to support this kind of thing in the future via a more out of the box approach?

[dstepanov]

Depends on what do you want to do, maybe you can describe it in more detail.
You can do a lot with a simple method interceptor https://docs.micronaut.io/latest/guide/#aroundAdvice and position it before or after the data interceptor.

[dstepanov]

BTW Why do you need row-wise for the keyset pagination? Can you just implement it without? What is the benefit of having row-wise syntax?

[nhoughto]

maybe im missing something, but I thought multi column keyset pagination requires it? i need to specify a cursor with col1=a,col2=1234 and i want to specify to only return rows > both those columns from an ordered set. Can you do that without row-wise comparisons? 🤔

WHERE col1>a AND col2>1234 isnt the same as WHERE (col1, col2) > (a,1234)

[nhoughto]

Looking at the code more, it seems like what I want is similar to Pageable/Slice but quite different, those things make assumptions about the mechanics of how the Page is retrieved, it basically assumes OFFSET .. LIMIT .. and requires pageIndex and pageSize, where as keyset pagination will have a LIMIT but no OFFSET, and will have a WHERE (col1, col2) > (cursorValue1, cursorValue2) instead.

So it seems like a proper implementation for this would be quite invasive? As you would likely want to redefine the current Pagination as NumberedPagination or something and abstract the method of paginating with pageIndex+pageSize being one method and keyset being another, otherwise i'd effectively duplicate Pageable support everywhere and call it KeySetPageable which doesn't feel great?

[dstepanov]

I haven't seen row-wise being used so much so I think it's just a syntax sugar and it can be written in a different way. I thing ‘KeySetPageable’ is a way to go, if I'm correct you would have to define which column is actually the key, right? And this kind of pagination doesn't support sorting for that you would have to choose a different key-column.

[nhoughto]

I fixed this without changing the data-processor portion by using RepositoryOperations.findStream() directly and subclassing a few bits. So can close this i suppose.