How to sort projection by alias from SELECT clause in Spring Data JPA with pagination?

Question

I created this two entites to demonstrate my problem:

OwnerEntity.java:

@Entity
public class OwnerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Size(min = 1)
    @OneToMany(mappedBy = "ownerEntity", cascade = CascadeType.ALL)
    private Set<ChildEntity> childEntities = new HashSet<>();
}

ChildEntity.java:

@Entity
public class ChildEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @NotNull
    @ManyToOne(optional = false)
    private OwnerEntity ownerEntity;

    public ChildEntity() {
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public OwnerEntity getOwnerEntity() {
        return ownerEntity;
    }

    public void setOwnerEntity(OwnerEntity ownerEntity) {
        this.ownerEntity = ownerEntity;
    }
}

I want to write a query that will show information from OwnerEntity and one joined ChildEntity. I created a projection:

OwnerEntityProjection.java:

public interface OwnerEntityProjection {

    Long getId();

    String getName();

}

My JpaRepository:

public interface OwnerEntityRepository extends JpaRepository<OwnerEntity, Long> {

    @Query(" SELECT                                        " +
           "       ownerEntity.id AS id,                   " +
           "       childEntities.name AS name              " +
           " FROM OwnerEntity ownerEntity                  " +
           " JOIN ownerEntity.childEntities childEntities  ")
    // There must be also WHERE clause, but for demonstration it is omitted
    Slice<OwnerEntityProjection> findAllPaginated(Pageable pageRequest);

}

Now when i run this simple test:

@Test
public void findAllPaginatedTest() {
    Pageable pageRequest = new PageRequest(0, 3, Sort.Direction.ASC, "name");
    Slice<OwnerEntityProjection> OwnerEntityProjectionsPaginated =
            ownerEntityRepository.findAllPaginated(pageRequest);
}

I got following error:

org.hibernate.QueryException: could not resolve property: name of: com.example.domain.OwnerEntity

I also checked generated JQPL in logs:

... order by ownerEntity.name asc

As you can see Spring Data Jpa appended first entity alias from FROM clause. I found that if i change PageRequest to this:

new PageRequest(0, 3, Sort.Direction.ASC, "childEntities.name");

It works without errors, but i don't want to pass ordering property to repository with aliases that are somewhere in JPQL query. I want to pass property that is directly present in projection that repository method returns. If i write ORDER BY manually in JPQL query like this:

... ORDER BY name ASC

Then this query also run without any errors because i can reference aliases in SELECT clause from ORDER BY clause. Is there any way to tell Spring Data Jpa to perform ordering without appending aliases from FROM or JOIN clauses? Something like this:

new PageRequest(0, 3, Sort.Direction.ASC, "name") ===> ORDER BY name asc

Answer 1

This is a bug in Spring Data: the aliases are not detected correctly. It has also been reported here.

In QueryUtils in the applySorting method, only (outer) join aliases and function aliases with exactly one pair of parenthesis are detected. A simple alias for a property doesn't work currently.

One workaround for this is to use JpaSort.unsafe with the aliases in parenthesis when building the PageRequest, e.g.

PageRequest.of(0, 3, JpaSort.unsafe(Sort.Direction.ASC, "(name)"))

This is unsafe as the name implies, when sorting dynamically based on user input, so it should only be used for hard-coded sorting.

Answer 2

In the context of a Pageable controller's entry point, I adapted the answer from Dario Seidl to the following:

public static Pageable parenthesisEncapsulation(final Pageable pageable) {

    Sort sort = Sort.by(Collections.emptyList());
    for (final Sort.Order order : pageable.getSort()) {
        if (order.getProperty().matches("^\\(.*\\)$")) {
            sort = sort.and(JpaSort.unsafe(order.getDirection(), order.getProperty()));
        } else {
            sort = sort.and(Sort.by(order.getDirection(), order.getProperty()));
        }
    }
    return PageRequest
        .of(pageable.getPageNumber(), pageable.getPageSize(), sort);
}

I can give my controller sort instruction like this:

/myroute?sort=(unsafesortalias),desc&sort=safesortfield,asc