How to do filtering + pagination?

[jnfeinstein]

Please let me know if there is a better place to address usage questions.

I am trying to take an application beyond basic boilerplate by adding both multi-tenancy and pagination/sorting. Tenancy is achieved using a many-to-one relation i.e.

data class Employee(UUID id);
data class Grant(UUID id, UUID grantedEmployee, UUID targetEmployee);

The query for allowed employees would consequently be:

SELECT employees.* from employees
INNER JOIN grants ON employees.id = grants.target_employee_id
WHERE grants.granted_employee_id = ?;

Thus far I have been unable to do so using spring-data-r2dbc. This is what I have tried:

1. Using a custom query via @Query w/ SPEL. I was able to get pagination working, but there is no way to achieve dynamic sorting because there is no way to generate SQL for the requested sort.
2. QueryDSL, which apparently is not supported.
3. Using the fluent API, from which I cannot figure out how to achieve joins but does allow pagination and sorting.

It would be trivial to rewrite the query as:

SELECT employees.* from employees
WHERE employees.id IN (
  SELECT grants.target_employee_id from grants WHERE grants.granted_employee_id = ?
);

which could potentially be mapped to a Criteria of raw SQL, but I could not find a supporting facility.

What is the correct pattern to achieve this? The documentation seems to be lacking when it comes to JOINs.

[mp911de]

Dynamic sorting through @Query isn't supported because we don't want to get ourselves into query rewriting business. You can use DatabaseClient with your query and new EntityRowMapper<>(typeToRead,converter) (where converter is R2dbcConverter).

We do not support JOINs yet because we don't support relationships yet.