I have entity in spring boot project
@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "products", indexes = {
@Index(name = "idx_product_name", columnList = "name"),
@Index(name = "idx_product_article", columnList = "name")
})
public class ProductEntity {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
@Column(name = "id", nullable = false)
private UUID id;
private String name;
private String article;
private double price;
private String description;
@ManyToOne
@JoinColumn(name = "category_id")
private CategoryEntity category; //<------
@OneToMany(mappedBy = "product",
cascade = CascadeType.ALL,
orphanRemoval = true)
private List<ProductImageEntity> images;
private String main_image_url;
}
in which i have foreign field category of CategoryEntity entity with
@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "categories", indexes = {
@Index(name = "idx_categories_name", columnList = "name")
})
public class CategoryEntity {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
@Column(name = "id", nullable = false)
private UUID id;
private String name;
@ManyToOne
@JoinColumn(name = "parent_id")
private CategoryEntity parent;//<------
}
and as you can notice category entity is hierarchical for example:
Category1
Category2
Category3
…so on
and products list can be like this:
Category1
Category2
Category3
Product1
Product2
Product3
Category4
Product4
Product5
Product6
Question how to make query with jpql by category
I can do like this :
select t from ProductEntity t where category.id =:category
where I fill category parametre with value of the category lowest hierarhical level (for example Category4 or Category3)
but how can I get products with category with higher hierarhical level Category2 or Category1?
in my logic if i filter by Category1 then all products belonging to Category1 must retrieve from db
explanation or code example