Spring Boot JPA hierarchical category products

Question

Here is my category table.

@Getter
@Setter
@NoArgsConstructor
@Entity
public class Category {

    @Id
    @GeneratedValue
    private long id;

    private String name;

    private String imageFileName;

    private int depth;

    @ManyToOne
    private Category parentCategory;

    @OneToMany(mappedBy = "parentCategory")
    private List<Category> childCategories;

    @OneToMany(mappedBy = "category")
    private List<Product> products;

    private boolean deleted;

}

Here is a solution for only 0 and 1 depth categories.

public List<Product> getByCategoryId(long categoryId) {
    Category category = categoryService.getById(categoryId);
    if (category.getDepth() == 0) {
        return productRepository.findByCategoryParentCategoryAndDeletedFalse(category);
    }
    return productRepository.findByCategoryAndDeletedFalse(category);
}

I want to get all products by category id. For example if i want to get products by category that depth is 10, i have to write so long method. I don't want to write method for every depth. How can i do simplifier?

score 0 · Answer 1 · answered Jan 21 '22 at 09:40

0

There is probably a clever way to do this with a query but here is something that could work, JPA should fetch the embedded entities for you without you having to do any extra queries.

Basic idea is to just recursively crawl until certain depth is reached.

public List<Product> getByCategoryId(long categoryId, int depth) {
    Category category = categoryService.getById(categoryId);

    Set<Product> result = new HashSet<>();

    searchUntilDepth(result, category,depth);

    return new ArrayList<>(result);
}

private void searchUntilDepth(Set<Product> foundProducts, Category cat, int depth) {
    foundProducts.addAll(cat.getProducts());

    if (cat.getDepth() >= depth) {
        return;
    }

    for (Category child : cat.getChildCategories()) {
        searchUntilDepth(foundProducts, child, depth);
    }
}

answered Jan 21 '22 at 09:40

Igor Flakiewicz

694
4
15

Thanks for your answer but i think this is not a good solution because if there is category that depth is 20, probably product get query will work more than 100 times so it will be slow. So if there is so many category, it is not a good solution. Also i want to add pagination later to this query. With this solution, i think it's not possible. – Erdem TAN Jan 21 '22 at 10:47
What sql flavour are you using? This may work for you: https://stackoverflow.com/questions/47341764/self-referencing-table-sql-query Then all you need is put this as native query in your repository. – Igor Flakiewicz Jan 21 '22 at 11:05
It looks good, probably this works. But i did a different solution. Thank you for your help. – Erdem TAN Jan 23 '22 at 07:42

score 0 · Accepted Answer · answered Jan 23 '22 at 07:47

I added to max depth check to my code. Max depth is 4 for me. Depth can be 1 or 2 or 3 or 4. My solution is like that. It looks working good for now.

@Query("select p from Product p " +
            "left join Category c1 on p.category.id = c1.id and c1.deleted = false " +
            "left join Category c2 on c1.parentCategory.id = c2.id and c2.deleted = false " +
            "left join Category c3 on c2.parentCategory.id = c3.id and c3.deleted = false " +
            "left join Category c4 on c3.parentCategory.id = c4.id and c4.deleted = false " +
            "where p.deleted = false and " +
            "(c1.id = :#{#category.id} or c2.id = :#{#category.id} or c3.id = :#{#category.id} or c4.id = :#{#category.id})")
List<Product> getByCategoryAndDeletedFalse(@Param("category") Category category);

Spring Boot JPA hierarchical category products

2 Answers2