如果我以更“復雜”的方式進行本機查詢（使用 PostgreSQL），我需要對資料進行 GROUP 以應用 ORDER BY 子句怎么辦？好吧，使用本機查詢（如解釋here）這樣做，ORDER BY 屬性被放置在查詢的末尾，這會引發 SQL 語法錯誤（當然）導致 ORDER BY 應該保留在我的分頁子句之前。
@Query(
" SELECT * FROM ("
"SELECT CAST(t.id AS VARCHAR) AS id, t.name, CAST(COUNT(p.id) AS SMALLINT) AS usedByProjectsCount "
"FROM tag t "
"LEFT JOIN project_tag pt ON pt.tag_id = t.id "
"LEFT JOIN project p ON p.id = pt.project_id "
"GROUP BY t.id, t.name) AS t "
"ORDER BY #pageable "
"LIMIT :limit OFFSET :offset ",
countQuery = " SELECT COUNT(*) FROM ("
"SELECT CAST(t.id AS VARCHAR) AS id, t.name, CAST(COUNT(p.id) AS SMALLINT) AS usedByProjectsCount "
"FROM tag t "
"LEFT JOIN project_tag pt ON pt.tag_id = t.id "
"LEFT JOIN project p ON p.id = pt.project_id "
"GROUP BY t.id, t.name) AS t ",
nativeQuery = true
)
我已經試過把它放在#pageable最后，但我得到了同樣的錯誤資訊。
繼續前進，Spring 生成我的查詢，例如：SELECT * FROM (SELECT CAST(t.id AS VARCHAR) AS id, t.name, CAST(COUNT(p.id) AS SMALLINT) AS usedByProjectsCount FROM tag t LEFT JOIN project_tag pt ON pt.tag_id = t.id LEFT JOIN project p ON p.id = pt.project_id GROUP BY t.id, t.name) AS t ORDER BY {#pageable} LIMIT ? OFFSET ? , t.usedByProjectsCount desc
我需要像：SELECT * FROM (SELECT CAST(t.id AS VARCHAR) AS id, t.name, CAST(COUNT(p.id) AS SMALLINT) AS usedByProjectsCount FROM tag t LEFT JOIN project_tag pt ON pt.tag_id = t.id LEFT JOIN project p ON p.id = pt.project_id GROUP BY t.id, t.name) AS t ORDER BY t.usedByProjectsCount desc LIMIT ? OFFSET ? {#pageable}
我確實知道該{#pageable}屬性應該保留在那里，以便我的頁面值可以放置在正確的變數中。
無論如何，任何幫助將不勝感激。
uj5u.com熱心網友回復：
在我的一個專案中，我有一個本機查詢，只需將可分頁放入其中。
@Query(
value = "SELECT * "
" FROM (SELECT CAST(t.id AS VARCHAR) AS id, "
" t.name, "
" CAST(COUNT(p.id) AS SMALLINT) AS usedByProjectsCount "
" FROM tag t "
" LEFT JOIN project_tag pt ON pt.tag_id = t.id "
" LEFT JOIN project p ON p.id = pt.project_id "
" GROUP BY t.id, t.name) AS t ",
countQuery = " SELECT COUNT(*) "
" FROM (SELECT 1 "
" FROM tag t "
" LEFT JOIN project_tag pt ON pt.tag_id = t.id "
" LEFT JOIN project p ON p.id = pt.project_id "
" GROUP BY t.id, t.name) AS t ",
nativeQuery = true)
Page<CustomProjection> customQuery(Pageable pageable)
只需使用所需的 pageRequest 呼叫您的方法。
Pageable pageRequest = PageRequest.of(0, 10, Sort.of(Order.desc("usedByProjectsCount"))
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/473247.html