Spring Data JPA loop trough parents

Admin 1
Viewed 365
    @Entity
    public class Product {
        @Id
        @Column(unique = true)
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
        private String title;
        @OneToOne
        private Brand brand;
        @OneToOne(fetch = FetchType.EAGER)
        @OnDelete(action = OnDeleteAction.CASCADE)
        private Category category;
}
    
    @Entity
    public class Category {
        @Id
        @Column(unique = true)
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
        @NotBlank(message = "Please enter category name!")
        @Length(max = 50, message = "Maximum size exceeded!")
        private String name;
        @OneToOne
        private Category parent;
}

Is possible to create recursive query that will loop trough all category parents of product?

Let's say I'm in category For Woman, it will loop trough all categories parents and if one of it matches For Woman it will return products of it..

enter image description here

Basically, what i want is something like this(query of it)..

  List<ProductOption> productOptions = new ArrayList<>();

        Category mainCategory = categoryRepository.findById(id);

            for(Category category : categoryRepository.findAll()){
                Category cat = category;
                while(cat!=null){
                    if(cat==mainCategory){
                        productOptions.addAll(productOptionRepository.findAllByProduct_CategoryId(category.getId()));
                    }
                    cat=cat.getParent();
                }
             }
spring-data-jpa spring-boot spring mysql
1 Answers

Maybe not the most proper solution, but works as it should

      @Query(value = "SELECT * FROM product_option p WHERE  p.sale_closed is false AND p.product_id 
                      IN(SELECT id from product where category_id 
                      IN(WITH RECURSIVE cte (id) AS (SELECT id FROM category WHERE id =:parentId 
                      UNION SELECT t1.id FROM category t1 
                      INNER JOIN cte t2 ON t1.parent_id = t2.id) SELECT * FROM cte))",nativeQuery=true)
      Page<ProductOption> findByCategory(Integer parentId,Pageable pageable);
Admin1
Related