Home > Back-end >  Sort query using Pageable with fields that is not in Select clause
Sort query using Pageable with fields that is not in Select clause

Time:11-16

So i have this code in my repository (Example):

@Query("SELECT distinct s FROM Model1 ss LEFT JOIN ss.model2 s "
Page<Model2> methodName(long customerId, Pageable pageable);

In Model1 i have a field LocalDate lastDate. The code is executing ok if i am sorting by fields from Model2 (by using Pageable) but if i need to sort by model1 fields (for example by lastDate) i am getting error "could not resolve property". If i am adding this field in "Select" i am also getting error "cannot be cast to class Model2". What can i do for it? I dont want to add "Order by" clause and also i dont want to add this field "lastDate" to Model1 just. Is there other ways to solve this problem?

P.S The pageable object is specified using the following method signature:

public PageRequest(int page, int size, Direction direction, String... properties)

CodePudding user response:

You can't.

You are returning Model2, which has no Information about lastDate, so how would JPA know?

You can not add it to you SELECT statement either because the return Entity is of type Model2 which again does not understand lastDate.

Your Model2 needs to understand lastDate in order to be able to sort/page by it.

In JPA, in order to be able to sort by a specific field, this field must be part of the returned entity in the select statement. You can't sort with a property that is not returned

  • Related