java - How to rewrite subquery in ORDER BY clause in JPA CriteriaQuery
问题描述
I'm trying to write an SQL query using CriteriaQuery, but I'm having a hard time doing so. This query basically gets a list of shipments and sorts them by their authorization date. This authorization date is represented as the date attribute of the first record in the status transition messages table with an initial status of 3 and a final status of 4. This is my query:
SELECT s.id
FROM shipment s
ORDER BY (SELECT min(stm.date)
FROM status_transition_message stm
WHERE stm.initial_status = 1 AND stm.final_status = 3 AND stm.shipment_id = s.id) desc;
I've tried multiple different solutions, but none have worked so far.
My current iteration is as follows:
private void sortByAuthDate(Root<ShipmentTbl> root, CriteriaQuery<?> query, CriteriaBuilder builder, ListSort sort) {
Subquery<Timestamp> authDateQuery = query.subquery(Timestamp.class);
Root<StatusTransitionMessageTbl> stmRoot = authDateQuery.from(StatusTransitionMessageTbl.class);
Predicate shipmentId = builder.equal(stmRoot.<ShipmentTbl>get("shipment").<String>get("id"), root.<String>get("id"));
Predicate initialStatus = builder.equal(stmRoot.<Integer>get("initialStatus"), 3);
Predicate finalStatus = builder.equal(stmRoot.<Integer>get("finalStatus"), 4);
// returns the authorization date for each queried shipment
authDateQuery.select(builder.least(stmRoot.<Timestamp>get("date")))
.where(builder.and(shipmentId, initialStatus, finalStatus));
Expression<Timestamp> authDate = authDateQuery.getSelection();
Order o = sort.getSortDirection() == ListSort.SortDirection.ASC ? builder.asc(authDate) : builder.desc(authDate);
query.multiselect(authDate).orderBy(o);
}
The problem with this solution is that the SQL query generated by the CriteriaQuery does not support subqueries in the ORDER BY clause, causing a parsing exception.
解决方案
My CriteriaQuery-fu is not good enough to help you with that part, but you could rewrite your SQL query to this:
SELECT s.id
FROM shipment s
LEFT JOIN status_transition_message stm
ON stm.initial_status = 1 AND stm.final_status = 3 AND stm.shipment_id = s.id
GROUP BY s.id
ORDER BY min(stm.date) DESC;
To me, this quite likely seems to be a faster solution anyway than running a correlated subquery in the ORDER BY
clause, especially on RDBMS with less sophisticated optimisers.
推荐阅读
- sql-server - 有没有办法在数据库级别关闭外键约束?
- r - Shiny中的隔离变量
- c# - 离开 Selenium C# 页面不会显示“你真的想离开”提示 - 为什么?
- python - 寻找斐波那契一致子数组的总和
- python - 查询在临时端口上运行的 Web 服务器时连接被拒绝
- javascript - 搜索名称不区分大小写的错误
- reactjs - 无法在 vscode 上调试 google SIGN IN
- css - 无法调整 vuetify v-data-table 中的页脚填充
- java - Vaadin 14 - 如何实现一个带有复选框列的网格,允许对多行进行操作?
- html - 如何给一个中间有一个静态单词的单元格提供多个值