Order by count using Spring Data JpaRepository

java, jpa, spring-data

Solution

I've solved the puzzle using hints and inspirations from:

- Limiting resultset using @Query anotations by Koitoer

- How to order by count() in JPA by MicSim

- Exhaustive experiments on my own

The first and most important thing I've not been aware of about spring-data is that even using `@Query` custom methods one can still create paging queries by simply passing the `Pageable` object as parameter. This is something that could have been explicitely stated by spring-data documentation as it is definitely not obvious though very powerful feature.

Great, now the second problem - how do I actually sort the results by size of associated collection in JPA? I've managed to come to a following JPQL:

select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a

where AwithBCount is a class that the query results are actually mapped to:

public class AwithBCount{
    private Long bCount;
    private A a;

    public AwithBCount(Long bCount, A a){
        this.bCount = bCount;
        this.a = a;
    }
    //getters
}

Excited that I can now simply define my repository like the one below

public interface ARepository extends JpaRepository<A, Long> {
    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCount(Pageable pageable);
}

I hurried to try my solution out. Perfect - the page is returned but when I tried to sort by bCount I got disappointed. It turned out that since this is a ARepository (not AwithBCount repository) spring-data will try to look for a bCount property in A instead of AwithBCount. So finally I ended up with three custom methods:

public interface ARepository extends JpaRepository<A, Long> {
    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCount(Pageable pageable);

    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount asc",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCountOrderByCountAsc(Pageable pageable);

    @Query(
        value = "select new package.AwithBCount(count(b.id) as bCount,c) from A a join a.bes b group by a order by bCount desc",
        countQuery = "select count(a) from A a"
    )
    Page<AwithBCount> findAllWithBCountOrderByCountDesc(Pageable pageable);
}

...and some additional conditional logic on service level (which could be probably encapsulated with an abstract repository implementation). So, although not extremely elegant, that made the trick - this way (having more complex entities) I can sort by other properties, do the filtering and pagination.

Problem

I am using Spring Data JpaRepository and I find it extremely easy to use. I actually need all those features - paging, sorting, filtering. Unfortunately there is one little nasty thing that seems to force me to fall back to use of plain JPA. I need to order by a size of associated collection. For instance I have: ``` @Entity public class A{ @Id private long id; @OneToMany private List<B> bes; //boilerplate } ``` and I have to sort by `bes.size()` Is there a way to somehow customize the ordering still taking the advantage of pagination, filtering and other Spring Data great features?

Original source

Related problems