Spring data jpa sort by nested object collection size

user_4256410 0
Viewed 6350

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 spring-boot spring java
1 Answers

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.

user_66362130
Related