Sort On Child List total count using JpaRepository Spring-data-jpa

Question

I have a requirement of paging and sorting an entity.

@Entity
@Table(name = "CATEGORY", catalog = "")
public class CategoryEntity {
 private CategoryEntity categoryByParentCategoryId;
 private Set<CategoryEntity> categoriesByCategoryId;


@ManyToOne(fetch = FetchType.LAZY,optional = false, cascade = CascadeType.PERSIST)
@JoinColumn(name = "PARENT_CATEGORY_ID", referencedColumnName = "CATEGORY_ID")
public CategoryEntity getCategoryByParentCategoryId() {
    return categoryByParentCategoryId;
}

public void setCategoryByParentCategoryId(CategoryEntity categoryByParentCategoryId) {
    this.categoryByParentCategoryId = categoryByParentCategoryId;
}

@OneToMany(mappedBy = "categoryByParentCategoryId", cascade = CascadeType.PERSIST)
public Set<CategoryEntity> getCategoriesByCategoryId() {
    return categoriesByCategoryId;
}

public void setCategoriesByCategoryId(Set<CategoryEntity> categoriesByCategoryId) {
    this.categoriesByCategoryId = categoriesByCategoryId;
}

From this link and other stack overflow answers, I found out that I can use sorting and paging using Paging Request like

Pageable size = new PageRequest(page, paginationDTO.getSize(),Sort.Direction.ASC, "id");

My problem is I have a self join parent and child relationship as shown above in the model and I need to sort the parent based on the count of child as shown below.

Here the Number of SubCategories is the size of categoriesByCategoryId. What do I need to pass in the PageRequest in the place of id to sort on basis of size of list of child.

PS. The model has more fields but for the question to be short I posted only the relevant fields

score 7 · Accepted Answer · answered Nov 12 '18 at 12:19

After going through this answer I was able to achieve the requirement by using a custom query, the method in JPARepository looked like

@Query(
        value = "select c from CategoryEntity c " +
                " WHERE LOWER(c.categoryNameEn) LIKE LOWER(CONCAT('%',?2, '%')) AND activeInd = ?1 " +
                "AND c.categoryByParentCategoryId is null" +
                " Order By c.categoriesByCategoryId.size desc",
        countQuery = "select count(c) from CategoryEntity c " +
                " WHERE LOWER(c.categoryNameEn) LIKE LOWER(CONCAT('%',?2, '%')) AND activeInd = ?1" +
                " AND c.categoryByParentCategoryId is null"
)
Page<CategoryEntity> findAllActiveCategoriesByCategoriesByCategoryIdCountDesc(String activeInd, String categoryNameEn, Pageable pageable);

The Count query is needed for pagination details.

Sort On Child List total count using JpaRepository Spring-data-jpa

1 Answers1