'Spring native query with GROUP BY and ORDER BY also pagination

What if I have my native query (with PostgreSQL) in a more "complex" way, which I need to GROUP the data in order to apply an ORDER BY clause? Well, doing so with native query (as explained here) the ORDER BY attributes are placed at the end of the query, which raises a SQL syntax error (of course) cause the ORDER BY should stay BEFORE my pagination clauses.

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

I already tried putting #pageable at the end, but I get the same error message.

Moving on, Spring generates my query like: 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

and I need like: 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}

I do know the {#pageable} attribute should stay there, just so my page values can be placed in the right variables.

Anyway, any help would be very appreciated.

javaspringkotlinjpahibernate-native-query


Solution 1:[1]

In the one of my project I have a native query and just put the pageable inside.

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

and just call your method with the needed pageRequest.

Pageable pageRequest = PageRequest.of(0, 10, Sort.of(Order.desc("usedByProjectsCount"))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Eugene Maksymets

Related Questions