Selecting from Multiple Tables in Spring JPA with Pageable and Sorting

Question

I saw the Selecting from Multiple Tables in Spring Data already had the solution for multiple tables. I would like to know if it is possible to write custom query that has tables with pageable and sorting feature at the same time in Spring JPA/DATA.

SELECT s.service_id, s.name, us.rating_id 
FROM services s, 
   ratings r, 
   user_services us
where 
   us.service_id = s.service_id and
   us.rating_id = r.rating_id and
   us.user_id= ?
;

Thanks for you help in advance.

Answer 1

Sorting feature is under question, but pagination is possible to use.

Assume that we have:

@Entity
public class Service {

    @Id
    private Long id;

    private String name;

    //...
}

@Entity
public class UserService {

    @Id
    private Long id;

    @ManyToOne        
    User user;

    @ManyToOne        
    Service service;   

    @ManyToOne        
    Rating rating;   

    //...
}

Then we create a projection:

public interface ServiceRating {
    Long getServiceId();
    String getServiceName();
    Long getRatingId();
}

And then create a query method supported pagination:

public interface UserServiceRepo extends CrudRepository<UserService, Long> {
    @Query("select s.id as serviceId, s.name as serviceName, us.rating.id as ratingId from UserService us join us.service s where us.user.id = ?1")
    Page<ServiceRating> getServiceRating(Long userId, Pageable pageable);
}

(Since this query does not contain grouping it's not necessary to use an additional countQuery (see the parameter of @Query)).

Test:

Page<ServiceRating> pages = userServiceRepo.getServiceRating(1L, new PageRequest(0, 10));
assertThat(pages.getContent()).hasSize(10));

UPDATE

Sorting also working perfectly. Just create a Sort object, specify direction and filed name (from the projection):

Sort sort = new Sort(Sort.Direction.ASC, "serviceName");
userServiceRepo.getServiceRating(1L, new PageRequest(0, 10, sort));