@Query support Slice and Page about spring-data-relational HOT 4 OPEN

Pagination support for string-based queries requires either a provided count-query or would require SQL parsing. Either one isn't great, especially for more complex queries.

We aren't exactly keen for having a parser to rewrite queries so most likely, we would require a user-provided count-query.

from spring-data-relational.

My 100% page sqls are working well by rewrite count queries. Are there scenarios that can't be solved by this?

from spring-data-relational.

From a quick look at your proposal I see problems with:

• Nested selects especially with WITH clause.
• Selects that contain String expressions that look like parts ofSQL statements
• Same for column names or aliases
• Selects that use GROUP BY
• Different dialects requiring different variants of LIMIT clauses
• Analytic functions utilizing ORDER BY
• Statements already containing a LIMIT clause.

Again that are just the problems I found with a quick glance.
Painful experience with Spring Data JPA demonstrates that it is difficult to parse SQL sufficiently to do the required substitutions.

I wonder if we can come up with a way where the user provides the correct spots to inject the count and limit clauses.

from spring-data-relational.

For the time being, would we be able to wrap the query with an outer query like SELECT * FROM (<the inner select>) LIMIT … OFFSET … and require a provided count query?

from spring-data-relational.