JPA criteria query count

Question

I've a problem with counting entities with CriteriaBuilder. First of, i've two entities - Category and Item. Category is tree of objects which can have one parent, many subcategories and items. This is my actual data in mysql:

Category 0
  Category 0 0
    10 items
  Category 0 1
    10 items
  Category 0 2
    10 items
  Category 0 3
    10 items
Category 1
  Category 1 0
    10 items
  Category 1 1
    10 items
  Category 1 2
    10 items
  Category 1 3
    10 items

Category Entity:

@ManyToOne
private Category category;

@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
private List<Category> subcategories = new ArrayList<>();

@OneToMany(cascade = CascadeType.ALL)
private List<ItemForSale> items = new ArrayList<>();

public List<Category> getAllCategories() {
    List<Category> categories = new ArrayList<>();
    categories.add(this);
    getSubcategories().forEach((c) -> {
        categories.addAll(c.getAllCategories());
    });
    return categories;
}

My counting query which SHOULD count items depending of the criteria

public int getItemsForSaleCount(Category category) {
    CriteriaBuilder cm = em.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cm.createQuery(Long.class);
    Root<ItemForSale> root = cq.from(ItemForSale.class);
    cq.select(cm.count(cq.from(ItemForSale.class)));
    List<Category> categories = category.getAllCategories();
    if(categories.size() > 1) {
        List<Predicate> predicates = new ArrayList<>();
        categories.forEach((c) -> {
            predicates.add(cm.equal(root.get("category"), c));
        });
        cq.where(cm.or(predicates.toArray(new Predicate[predicates.size()])));
    } else {
        cq.where(cm.equal(root.get("category"), category));
    }
    Query query = em.createQuery(cq);
    return Math.toIntExact((Long) query.getSingleResult());
}

Basically what is happening is that when i request Category 0 for example query result should be 40 but is 40 * 80 = 3200(correct result * total amount of items), same when i request subcategories, result is 10 * 80 = 800

EDIT: I solved it, when i replace cq.from(ItemForSale.class)) with root it's working, could someone explain me the difference?

Answer 1

Well, the Javadoc for AbstractQuery.from answers your question:

Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.

Since you called from twice, and defined the where clause to involve only one of the roots, the query returns the 40 rows from the 'filtered' root cross-joined with the 80 results from the 'unfiltered' root.