Spring data jpa sort by nested object collection size

Question

I am using a spring boot application and there exists the following 2 entities.

Person.java

@Entity
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "PERSON")
public class Person extends BaseEntity {
    @NotNull
    @Enumerated(EnumType.STRING)
    private StatusType status;

    @JsonIgnore
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "PERSON_ADDRESS",
            joinColumns = @JoinColumn(
                    name = "person_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(
                    name = "address_id", referencedColumnName = "id"))
    private Collection<Info> addresses;
}

Address.java

@Entity
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "ADDRESS")
public class Address extends BaseEntity {
    @NotNull
    private String address;

    @ManyToMany(mappedBy = "addresses")
    private Collection<Person> persons;
}

I am using a JpaRepository as follows.

@Repository
public interface PersonRepository extends JpaRepository<Person, Long> {
}

And I use the following statement to implement the pagination and sorting.

Page<Person> allPersons = personRepository.findAll(pageable);

where pageable is an instance of org.springframework.data.domain.Pageable.

I am able to sort using the other columns in Person. But I want to sort based on the addresses collection, based on the number of Address record for each Person entity.

In short, I wanted to sort the Persons based on the collection size of Collection. The sorting order (ASC or DESC) comes from the front end.

Any idea how the Pageable object should look like to implement this? Also without returning any duplicate Person record if more than one Address exist for a Person.

Spring Data JPa has a lot of very effective features but it's not omnipotent. You have to create a unique query... or a Specification because you can use it together with Pageable. — Selindek, Sep 13 '18 at 09:13

score 6 · Accepted Answer · edited Sep 17 '18 at 07:41

6

Here is the solution according to Order by count using Spring Data JpaRepository.

In my experiment, I tried to define Repository as follows, but there is problem with that; as we are defining the query on PersonRepository for sorting based on addressCount spring data looks into Person.

@Repository
public interface PersonRepository extends JpaRepository<Person, Long> {

    @Query(
        value = "select p from Person p join p.addresses ad group by p",
        countQuery = "select count(p) from Person p"
    )
    Page <Person> findAllWithAddressCount(Pageable pageable);
}

So as a workaround I tried to shift the sorting logic into query definition itself and I end up with 2 versions for ASC and DESC mode:

@Repository
public interface PersonRepository extends JpaRepository <Person, Long> {

    @Query(
        value = "select p from Person p join p.addresses ad group by p Order By addressCount asc",
        countQuery = "select count(p) from Person p"
    )
    Page<Person> findAllOrderByAddressCountAsc(Pageable pageable);

    @Query(
        value = "select p from Person p join p.addresses ad group by p Order By addressCount desc",
        countQuery = "select count(p) from Person p"
    )
    Page<Person> findAllOrderByAddressCountDesc(Pageable pageable);
}

Hope this could help.

edited Sep 17 '18 at 07:41

Philip John

5,275
10
43
68

answered Sep 13 '18 at 09:20

MohammadReza Alagheband

2,110
13
19

And what will be the Pageable object sort field be like? – Philip John Sep 13 '18 at 09:47
Also if there are only 6 persons with 3 of them having 2 addresses and the remaining persons one address each, then if we use this countQuery, I will get 9 person records right? With 3 of them duplicate – Philip John Sep 13 '18 at 09:48
@PhilipJohni i experimented it and changed my proposed solution,please check – MohammadReza Alagheband Sep 13 '18 at 10:38
Hmm I had seen this solution in another stackoverflow post and wanted to know if there was any alternative way. Maybe you can add also the link to the original answer as a reference to your answer. – Philip John Sep 14 '18 at 10:58
@PhilipJohn reference given – MohammadReza Alagheband Sep 15 '18 at 05:22
1

What is `addressCount` ? – Jorge Guillermo Negrete Nov 23 '21 at 05:12

Spring data jpa sort by nested object collection size

1 Answers1