I’m trying to fetch records with Criteria API specification with PageRequest. It works fine if I try to sort by the parent property of the entity. But when I try to sort with nested property, I’m getting below exception. When I set the sort field as “division”, records are being retrieved sorted by Division. But when I’ve set the sort field in Pagerequest to be “publicationSchedule.deadlineDate”, I’m getting the exception.

For example: The entity looks like:

public class Publication {

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

    @Column(name = "book_price")
    private String bookPrice;

    @Column(name = "division")
    private String division;

    @Column(columnDefinition = "JSONB", name = "publication_schedule")
    @Type(JsonBinaryType.class)
    PublicationSchedule publicationSchedule;

    }

And PublicationSchedule entity looks like this :

public class PublicationSchedule{

    @JsonSerialize(using = TimeStampSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date deadlineDate;

    @JsonSerialize(using = TimeStampSerializer.class)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date publicationDate;
}

When I try to sort with direct entity property division, am getting the records sorted with division. But when I try to sort with deadlineDate from publicationSchedule, I’m getting below exception with the same specification.

Specification<Publication> specification = Specification.where(null);
<<Specification code generation here>>
Page<Publication> searchResults =
                publicationRepository.findAll(specification, pageRequest);



 ERROR org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet].log(175) - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: Attribute 'com.elsevier.rpc.service.bff.entity.Publication#publicationSchedule(BASIC)' is not joinable] with root cause
org.hibernate.query.SemanticException: Attribute 'com.elsevier.rpc.service.bff.entity.Publication#publicationSchedule(BASIC)' is not joinable
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.buildSingularJoin(AbstractSqmFrom.java:762) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.buildJoin(AbstractSqmFrom.java:697) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.join(AbstractSqmFrom.java:410) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.hibernate.query.sqm.tree.domain.AbstractSqmFrom.join(AbstractSqmFrom.java:69) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
    at org.springframework.data.jpa.repository.query.QueryUtils.getOrCreateJoin(QueryUtils.java:903) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:799) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:769) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toExpressionRecursively(QueryUtils.java:765) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder(QueryUtils.java:754) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.query.QueryUtils.toOrders(QueryUtils.java:706) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:753) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:710) ~[spring-data-jpa-3.2.4.jar:3.2.4]
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:447) ~[spring-data-jpa-3.2.4.jar:3.2.4]