Spring data doesnt pick correct alias to sort with pageable result

user_1449185 0
Viewed 288

Assume that i have below method in EmployeeRepository that extended JpaRepository:

@Query(value =
        "    select new com.my.EmployeeDTO(t.id, " +
        "       t.fullName, " +
        "       t.nationalCode, " +
        "       (select a.unit.code from UnitEmployee a where a.employee.id = t.id and a.isDefault = true)) " +
        "  from Employee t ",
countQuery =
        "select count(t.id) " +
        "  from Employee t ")
Page<EmployeeDTO> find(Pageable page);

and call it in service like this:

public Page<EmployeeDTO> findEmployee(EmployeeExample employeeExample) {
    EmployeeDTO employeeDTO = employeeExample.getExample();

    return employeeRepository.find(
            PageRequest.of(employeeExample.getPage(),
                    employeeExample.getSize(),
                    employeeExample.getSortDirection() == SortDirection.DESC ? Sort.Direction.DESC : Sort.Direction.ASC,
                    "t.id"
            ));
}

The problem is that spring data or maybe hibernate use a as alias and add order by a.id desc and picked it from sub query in select.

How can i specify correct alias?

spring-data spring-data-jpa hibernate spring
1 Answers

As it is stated in the JPA 2.2 specification (see 4.6.16 Subqueries section):

Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of this specification.

So, your query is just not valid JPQL. But you can try to rewrite your query in the following way:

@Query(value =
        "    select new com.my.EmployeeDTO(t.id, " +
        "       t.fullName, " +
        "       t.nationalCode, " +
        "       a.unit.code) " +
        "  from Employee t, UnitEmployee a  where a.employee.id = t.id and a.isDefault = true",
countQuery = "select count(t.id) from Employee t ")
Page<EmployeeDTO> find(Pageable page);

or try to use hibernate @Formula annotation.

user_62771040
Related