带有GROUP BY和ORDER BY分页的Spring本地查询

Question

如果我的本地查询(与PostgreSQL)在一个更"复杂"的"为了应用order BY子句，我需要对数据进行哪种分组?如果使用本机查询(如这里所述)，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}属性应该留在那里，这样我的页面值就可以放在正确的变量中。

无论如何，任何帮助都会非常感激。

Answer 1

在我的一个项目中，我有一个本地查询，只是把可分页的。

@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"))