Spring Boot JPA hierarchical category products-CodePudding

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?

CodePudding user response：

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);
    }
}

CodePudding user response：

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);