英文:
How to support sorting and filtering for a field which is not mapped with another entity using spring boot
问题
我有一个实体 Person，它有字段 id，name，email。
另一个实体 Employee 有字段 id，name，personid（存储了 person 的字符串 id，但与 person 表没有映射）。
现在我想要获取一个分页列表，其中包含 person 的姓名，employee 的 id，employee 的姓名，并且可以根据 person 的姓名和 employee 的姓名进行动态搜索和排序。
也就是说，用户可以按照 person 的姓名或者 employee 的姓名进行排序，并且可以按照 person 的姓名或者 employee 的姓名或者两者兼而有之进行筛选。
在查询格式中，我们可以写成：
select emp.id, emp.name, p.name from employee emp join person p on
emp.personid=p.id
这个查询返回了预期的输出，但是为了实现搜索和排序，我无法使用投影，因为 Employee 实体中的 personid 与 Person 实体的 id 没有映射。
不能使用 Criteria Builder 并且不能更改实体。
如果有人能提供一个最佳和最优的解决方案来获取所需的分页结果，并支持排序和过滤，将不胜感激。
英文:
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.
答案1
得分: 1
您可以使用 order by 子句修改现有的查询并传递 Pageable，例如这是按雇员姓名排序的示例：
@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> findAllOrderByEmployeeNamePersonName(Pageable pageable);
要按雇员姓名进行筛选，只需添加 where 子句：
@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);
英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论