Custom query with sort parameter [DATAJDBC-589]

[spring-projects-issues]

kremerkp opened DATAJDBC-589 and commented

I try to add an dynamicly "order by" clause by passing a parameter inside a custom query. Limit and offset are working fine as parameters, only the order by is not recognized

Example:

// code public interface CustomerRepository extends PagingAndSortingRepository<Customer, Long> 

     @Query("select * from person where firstname = :name" + " order by :order")
           List<Customer> findByFirstNameCustomQuery(@Param("name") String name, 
Pageable page, @Param("order") String order);

The query that I normaly use is more complex, so that I wont be able to use query creation from method names.

Is there a workaround or will it be "fixed/added" in a future release?

I also added a small GitHub Repo do demonstrate the problem;

https://github.com/kremerkp/spring-data-jdbc-pageable-h2

 

---

Reference URL: https://stackoverflow.com/questions/63285038/spring-data-customized-query-with-order-parmeter-nor-working

[spring-projects-issues]

Mark Paluch commented

Using @Query with Sort or Pageable is not supported. The reason is that the provided SQL statement would require to be parsed and rewritten so that Spring Data JDBC could augment it with pagination information (such as LIMIT/OFFSET) and the ORDER BY clause.

Spring Data JPA has partial support for dynamic sorting and we've learned from there that parsing SQL comes with quite some complexity and it's easy to break the parser using e.g. joins or subselects.

Therefore we don't plan to support @Query with Sort or Pageable

[spring-projects-issues]

Jens Schauder commented

As Mark explained above, currently we would need to parse the SQL statement which we don't want to get into.
Once other changes are in place it should be possible to have the user only specify the where clause and possibly separately an order by clause so that we can do the necessary tweaking without parsing SQL.

This will take a while though.

[NickYadance]

@spring-projects-issues kremerkp is not issuing adding dynamic Sort and Pageable to custom query like JPA. It's that the :order parameter won't work in this query.

@Query("select * from person where firstname = :name" + " order by :order")

stackoverflow Here is a good explanation about the root cause. It's not about Spring Data Jdbc, but still, user can get confused about the result.

[hadjiski]

Wow, was just about to submit a new issue and found this one.
I understand fully the problem of introducing the pagination+sorting with a Pageable object and that it will be a nightmare to parse any possible complex SQL, but here we are talking about something much simpler.

@spring-projects-issues, just make it possible to define :placeholders as is, other than column values substitutes, which are escaped by quotes and whatsoever. Right now it is broken only due to the fact that the :order is substituted to a quoted column name, thus wrong SQL syntax. Here a recent stackoverflow issue, describing the same.

[schauder]

:order is not "escaped by quotes and whatsoever". It is replaced by a bind parameter, which cannot be used for column names and therefore is unsuitable for ordering and at least for some databases for pagination.

Dynamically identifying :placeholders to be replaced by literal values vs those to be replaced by bind variables would require exactly the kind of parsing we shy away from so far.

But using a special kind of placeholder for this, plus maybe a configurable prefix could be an interesting idea.

Something like:

@Query(
    value = "select x from t order by &&order" 
    literalReplacementPrefix = "&&"
)

@mp911de What do you think?

[hadjiski]

:order is not "escaped by quotes and whatsoever". It is replaced by a bind parameter, which cannot be used for column names and therefore is unsuitable for ordering and at least for some databases for pagination.

Interesting, in my case after enabling TRACE for the mariadb client, I clearly could see that the SQL (ORDER BY :sortColumn LIMIT :pageSize OFFSET :offset) is properly constructed with a quoted value for the string, whereas the numbers are left unquoted, which just satisfies the result -> ... ORDER BY 'some_column' LIMIT 25 OFFSET 0

Anyway it is clear that it is a hack to be used like this and I would appreciate to see your suggestion working

[schauder]

That would be done by the JDBC client. If you enable logging for JdbcTemplate I'm pretty confident it just passes ? and bind parameters. Not sure if your JDBC client does the replacement client side or if it just does that for logging.

[hadjiski]

Yes, u r right, just double checked, indeed the jdbc keeps it all ?, the rest is taken from the client.
I look forward to see the aka. literalReplacementPrefix in action, probably in org.springframework.jdbc.core.namedparam.NamedParameterUtils#parseSqlStatement

[hadjiski]

@schauder, just a question, because we are in a kind of Patt situation. We would like to proceed with native spring data jdbc as we progressed pretty much with it purely, but need above placeholder feature now.
How long would it take to include it in a lets say 2.4.14, which we will pick by next spring boot 2.7.14.
We are currently also looking into JDBI, micronaut data, but would preferably of course stay on spring data jdbc native.

Thanks

[schauder]

This won't be included in 2.4.x if at all it would go into 3.2. And honestly that most certainly won't happen since we have already a lot of stuff planned for 3.2

[kap199297]

@schauder, is there any update for this issue?

Our team is facing the issue in sorting (by name) along with dynamic column comparing (like current_price > offer_price or mrp, etc ).
DB table: Product (product_id, name, current_price, mrp, initial_price, offer_price, launch_date)

We tried multiple ways to implement the same.

1. Tried a custom query in the repository but @query does not support pagination (Issue @Query support Slice and Page #1760 )
2. Tried using the Criteria which supports pageable but facing an issue in comparing column Introduce support to compare two columns in Criteria #1335
3. Finally we were able to achieve via row SQL with JdbcTemplate but needed a row mapper for different objects.

Please let me know if any other way is available for sorting along with dynamic column mapping.
Thanks.