Provide an option for a simple wrapper count query

[edudar-chwy]

I have many complex native queries where Spring Data can't figure out count queries out of the box. Think of anything involving SELECT DISTINCT. My queries return correct results, but the total count is incorrect when using pagination. The total result is 300 records, but the count returns 650 due to duplicated records without DISTINCT, which the count query creator strips out.

An obvious solution is to provide a custom count query. It works. But it also makes code hardly maintainable because I have to duplicate page-sized queries and just make them double: value and countQuery. I suggest a simple wrapper option that will take a value and create a count query like

SELECT COUNT(dt.*) FROM (<value_query>) AS dt

It won't solve all issues for everyone, but at least it will allow users to not repeat themselves for 100x of queries.

[mp911de]

Paging @schauder. I am not sure how much trouble we create when using subqueries as Spring Data JPA is used with all sorts of database systems, even those that pretend to support SQL, although their backend is a NoSQL database. Using top-level count queries is less invasive.

Our own query rewriter is based on Rexex and has limitations when it comes to more complex queries. We recommend using the JSqlParser-based rewriter (see #3707) or manually-declared queries as alternatives.

[quaff]

I suggest a simple wrapper option that will take a value and create a count query like

Or introduce simpleCount attribute, then you can write @Query(value = "SELECT DISTINCT ... ", nativeQuery = true, simpleCount = true), it will use SELECT COUNT(T.*) FROM (SELECT DISTINCT ...) AS T as count query.

[mp911de]

We're exploring a different approach that lets folks select their QueryEnhancer via #3527. It is a much more capable approach because folks can select query enhancers for particular queries and can control how to derive a count query from a given native query.