1

I have the following relation and I need to get consumers which have at least one purchase (as a subquery, because this is a part of a bigger query).

@Entity
@Table(name = "consumers")
public class Consumer extends User {

@JsonIgnore
    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "consumer_id")
    private List<Purchase> purchases;
}

and the query

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Consumer> criteriaQuery = criteriaBuilder.createQuery(Consumer.class);
Root<Consumer> root = criteriaQuery.from(Consumer.class);`
Join<Consumer, Purchase> purchases = root.join(Consumer_.purchases, JoinType.LEFT);
                    sub.select(criteriaBuilder.count(purchases.get(Purchase_.id)));
                    sub.where(criteriaBuilder.equal(root.get(Consumer_.id), purchases.get(Purchase_.consumer).get(Consumer_.id)));
                    predicates.add(criteriaBuilder.greaterThanOrEqualTo(sub, 0L));
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wiktor Kowalski
  • 11
  • 1
  • and the "problem" is? You can easily look at the SQL generated with that via your JPA providers log, and see if it is what you expected. –  Oct 24 '18 at 10:29
  • Basically, that subquery doesn't work like intended to do. After that first predicate I would like to add another predicates and in the end just something like this: `criteriaQuery.select(root).where(predicates.toArray(new Predicate[predicates.size()])).distinct(true); return entityManager.createQuery(criteriaQuery).getResultList();` – Wiktor Kowalski Oct 24 '18 at 12:50
  • if something "doesnt work as intended" then kindly post the SQL that it generates so you can see WHAT part of it doesnt work –  Oct 24 '18 at 13:37

0 Answers0