How to support sorting and filtering for a field which is not mapped with another entity using spring boot

Question

I have an entity Person which has fields id,name,email

Another entity Employee which has fields id,name,personid (stores the string id of person but not mapped with person table)

Now I want to get paginated list which contains person name, employee id, employee name along with searching and sorting based on person name and employee name dynamically. i.e., User can sort by person name or employee name and filter by person name or employee name or both

In query format we can write,

select emp.id,emp.name,p.name from employee emp join person p on
emp.personid=p.id

This query returns expected output but to achieve searching and sorting, i'm unable to use projections as personid in Employee entity is not mapped with id of Person entity.

Criteria builder shouldn't be used and entities cannot be changed.

It would be greatly appreciated if someone could provide a best and optimal solution to get the required paginated result which supports sorting and filtering.

Answer 1

You could modify existing query with order by clause and pass Pageable, e.g. here is the example of sorting by employee name:

@Query("select emp.id,emp.name,p.name from employee emp join person p on emp.personid=p.id order by emp.name")
Page<YourDto> findAllOrderByEmployyNamePersonName(Pageable pageable);

For filtering by employee name just add where clause:

@Query("select emp.id,emp.name,p.name from employee emp join person p on emp.personid=p.id where emp.name = :empName")
Page<YourDto> findByEmployeeName(String empName, Pageable pageable);