I have 3 entities. Category, Subcategory and Product
@Entity
@JsonInclude(value = JsonInclude.Include.NON_EMPTY)
public class Category {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String title;
    private String picture;
    @OneToMany(mappedBy = "category", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Subcategory> subcategories;
    //getters and setters...
    @JsonProperty("productCount")
    private int getProductCount() {
        //Counting products
        int productCount = 0;
         //!!!!!
         //My problem starts here!
        for (final Subcategory subcategory : subcategories) {
            productCount += subcategory.getProducts().size();
        }
        return productCount;
    }
}
@Entity
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class Subcategory {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String title;
    @ManyToOne
    @JoinColumn(name = "parent_category_id", nullable = false)
    @JsonIgnore
    private Category category;
    @OneToMany(mappedBy = "subcategory", cascade = CascadeType.ALL)
    private List<Product> products;
    //getters and setters
}
@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String title;
    private String unit;
    private String icon;
    @ManyToOne
    @JoinColumn(name = "subcategory_id", nullable = false)
    @JsonIgnore
    private Subcategory subcategory;
    //getters and setters
}
They are related to each other, but when I want to count the number of products in each category, about 120 queries are executed (depending on the number of subcategories and products)
I was able to reduce it to around 60 queries by adding @EntityGraph to my category repository:
@EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"subcategories"})
However 60 query is still too much. I can't add subcategories.products to this entity graph annotation because that causes org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
I can suppress this exception by changing the data type of products to Set from List but that creates a Cartesian product and makes the performance worse (it returns around 18,000 rows).
How can I fix this issue without creating a Cartesian product?
 
    