JPA标准:按子字段排序会出错

Question

我有3个实体客户、流程和文档。

一个客户有许多流程，一个流程有许多文档。

我想按文档的updateDate对客户进行排序。

我的实体如下所示；

客户-

@Entity
public class Customer {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Process> processes = new ArrayList<>();
// getter, setter etc.
}

过程-

@Entity
public class Process {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String type;
@ManyToOne(fetch = FetchType.LAZY)
private Customer customer;
@OneToMany(mappedBy = "process", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Document> documents = new ArrayList<>();
//getter, setter etc.
}

文件-

@Entity
public class Document {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String note;
private LocalDateTime updateDate;
@ManyToOne(fetch = FetchType.LAZY)
private Process process;
}

我尝试了以下规格-

public static Specification<Customer> orderByDocumentUploadDate() {
return (root, query, criteriaBuilder) -> {
ListJoin<Customer, Process> processJoin = root.join(Customer_.processes);
ListJoin<Process, Document> documentJoin = processJoin.join(Process_.documents);
query.orderBy(criteriaBuilder.desc(documentJoin.get(Document_.updateDate)));
query.distinct(true);
return null;
};
}

它给出以下错误-

错误：对于SELECT DISTINCT，ORDER BY表达式必须出现在SELECT中列出

生成的SQL-

select distinct customer0_.id   as id1_0_,
customer0_.name as name2_0_
from customer customer0_
inner join
process processes1_ on customer0_.id = processes1_.customer_id
inner join
document documents2_ on processes1_.id = documents2_.process_id
order by documents2_.update_date desc
limit ?

我也尝试过分组，比如下面的

public static Specification<Customer> orderByDocumentUploadDate() {
return (root, query, criteriaBuilder) -> {
ListJoin<Customer, Process> processJoin = root.join(Customer_.processes);
ListJoin<Process, Document> documentJoin = processJoin.join(Process_.documents);
query.orderBy(criteriaBuilder.desc(documentJoin.get(Document_.updateDate)));
query.groupBy(root.get(Customer_.id));
return null;
};
}

然后它给出了一个不同的错误-

错误：列"documents2_.update_date；必须出现在GROUP BY中子句或用于聚合函数

生成的SQL-

select
customer0_.id as id1_0_,
customer0_.name as name2_0_ 
from
customer customer0_ 
inner join
process processes1_ 
on customer0_.id=processes1_.customer_id 
inner join
document documents2_ 
on processes1_.id=documents2_.process_id 
group by
customer0_.id 
order by
documents2_.update_date desc limit ?

我可以通过以下sql来完成；max()在下面的sql-中解决了它

select  customer.* from customer
inner join process p on customer.id = p.customer_id
inner join document d on p.id = d.process_id
group by customer.id
order by max(d.update_date);

但是我不能用criteriaAPI来做同样的事情。

你有什么建议吗？

Answer 1

这是一个概念上的误解。

• 首先，您必须了解内部联接是如何工作的。在这种情况下，这一部分是可以的：[将process表与基于document.process_id = process.id的document表连接起来]

• 其次，您需要根据文档的更新日期对客户进行排序

很遗憾，您在此处使用了group by。GROUP BY只返回其为grouped by的列。在这种情况下，它将只返回customer_id。

您可以对分组数据使用count()、sum()等聚合函数。

当您尝试访问update_date时，它会抛出以下错误：

ERROR: column "documents2_.update_date" must appear in the GROUP BY clause or be used in an aggregate function

---

现在，我们如何摆脱这种情况？

• 所以首先我们需要做join来获取客户id。在获取客户id后，我们应该根据客户id对数据进行分组，然后使用max((来获取每组的max_date(如果需要，则为最小值(

SELECT 
customer_id,
max(date) AS max_date
FROM    
document 
JOIN process ON process.id = document.process_id
GROUP BY customer_id

它将返回一个临时表，如下所示：

customer_id	max_date
1	2020-10-24
2	2021-03-15
3	2020-09-24
4	2020-03-15