CODEWITHSUNDEEP
spring-dataspring-webfluxspring-data-r2dbcr2dbc-postgresql
Etibar - a tea bar
Etibar - a tea bar

Reputation: 1962

R2DBC Querying From Multiple Tables With Sorting and Pagination

I am trying to create endpoint using spring webflux. The result of endpoint to query from several tables, and most of values in where clause are optional. (example query is provided in the bottom). Sorting column and direction is provided by user, the same with pagination.
I tried to use native query along with Sort,Pageable. However, Sort and Pageable (org.springframework.data.domain) got ignored. Later used fully native query without Sort, Pageable, however couldn't inject ASC and DESC by parameter to query. Is there any other options I can do?

SELECT u.id, u.catalog_id, u.amount , u.currency , u.created_date , cc.customer_id , c.country
FROM user u join catalog c ON u.catalog_id = c.id 
JOIN catalog_contract cc ON cc.id  = c.catalog_contract_id 
where 
(:user_country_id is null OR u.user_country_id = :user_country_id)
AND
(:customer_id is null or cc.customer_id = :customer_id)
ORDER BY :column :direction
OFFSET :offset LIMIT :limit

Upvotes: 2

Views: 1629

Answers (1)

engilyin
engilyin

Reputation: 1130

Since you are using parameter binding and your parameters are obviously strings it would be treated as strings and wrapped with single quotas. So you get

ORDER BY 'your-column' 'ASC'

instead of

ORDER BY your-column ASC

As a work around you can build SQL manually concatenating the Order parameters instead of binding.

Also you can check the appropriated issue: https://github.com/spring-projects/spring-data-r2dbc/issues/805

Upvotes: 0

Related Questions