JPA CriteriaQuery按最新子项属性排序 [英] JPA CriteriaQuery Order By Latest Child Property
问题描述
我有2个实体:Project和ProjectStatus。
I've got 2 entities: Project and ProjectStatus.
项目实体:
@Entity
public class Project {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Long id;
@OneToMany(mappedBy = "project")
private List<ProjectStatus> projectStatusses;
}
项目状态实体:
@Entity
public class ProjectStatus {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Long id;
@ManyToOne
private Project project;
@Enumerated(EnumType.STRING)
private ProjectStatusType statusType;
}
我想按照最新状态订购项目 CriteriaQuery.orderBy
。
I would like to order projects by their latest status type with CriteriaQuery.orderBy
.
我想出了以下内容:
CriteriaQuery<Project> criteriaQuery = criteriaBuilder.createQuery(Project.class);
Root<Project> root = criteriaQuery.from(Project.class);
Join<Project, ProjectStatus> join = root.join("projectStatusses", JoinType.LEFT);
criteriaQuery.orderBy(criteriaBuilder.asc(join.get("statusType")));
我希望上面的查询只考虑最新的项目状态,但我不知道如何要做到这一点。
如何实现这一目标?
I want the above query to only take the latest project status into account, but I do not know how to do that. How can I achieve this?
更新:
实现此目的的SQL是:
Update: The sql to achieve this is:
SELECT proj.*, stat.statustype
FROM project proj
LEFT JOIN projectStatus stat ON proj.id = stat.project_id
WHERE stat.id = (SELECT MAX(id) FROM projectstatus WHERE project_id = proj.id)
ORDER BY stat.statustype
推荐答案
我不是回答我自己的问题的粉丝,但我找到了解决方案。希望它能帮助其他人。
I am not a fan of answering my own question, but I found the solution. Hopefully it will help somebody else.
我的错是在 criteriaQuery.orderBy
方法中寻找解决方案。在 criteriaQuery.where
方法中添加子查询可以解决问题。
My fault was to look for a solution in the criteriaQuery.orderBy
method. Adding a subquery in the criteriaQuery.where
method did the trick.
解决方案:
CriteriaQuery<Project> criteriaQuery = criteriaBuilder.createQuery(Project.class);
Root<Project> root = criteriaQuery.from(Project.class);
Join<Project, ProjectStatus> join = root.join("projectStatusses", JoinType.LEFT);
//Create a subquery to get latest ProjectStatus for project
Subquery sq = criteriaBuilder.createQuery().subquery(Long.class);
Root<T> from = sq.from(Project.class);
Path<ProjectStatus> path = root.join("projectStatusses");
//Get latest status
sq.select(criteriaBuilder.max(path.<Long>get("id")));
//For each project
sq.where(criteriaBuilder.equal(from.<Long>get("id"), root.<Long>get("id")));
Predicate latestStatusCondition = criteriaBuilder.and(criteriaBuilder.equal(join.get("id"), sq));
criteriaQuery.orderBy(criteriaBuilder.asc(join.get("statusType")));
criteriaQuery.where(latestStatusCondition);
这篇关于JPA CriteriaQuery按最新子项属性排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!