How to do this without 'distinct': Select Distinct(x) From x Left Join y Where y.attr = true

Question

I'm stuck in a query, I'm using Spring Data JPA, JpaRepository and JpaSpecificationExecutor. So I have to use Predicates from CriteriaBuilder.

At the moment I simply do:

Specifications<MyEntity> spec = Specifications.where(MySpec.isTrue());
List<MyEntity> myEntities = myRepository.findAll(spec);

where MySpec.isTrue() is:

public static Specification<MyEntity> isTrue() {
    return new Specification<MyEntity>() {
        @Override
        public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            SetJoin<MyEntity, JoinEntity> j = root.join(MyEntity.joinEntities, JoinType.LEFT);  
            return cb.isTrue(j.get(JoinEntity_.attr));
        }
    };
}

So this will of course result in this SQL:

SELECT e.* FROM MyEntity e
  LEFT OUTER JOIN JoinEntity j ON j.myEntityId = e.id
  WHERE j.attr = true

But I'm only interested in the unique set of MyEntitys. So in JPQL I'd write:

SELECT DISTINCT(e) FROM MyEntity e
  LEFT JOIN e.joinEntities j
  WHERE j.attr = true

Right now my solution is:

List<MyEntity> myEntities = myRepository.findAll(spec);
Set<MyEntity> entitiesSet = new HashSet<MyEntity>(myEntities);

There must be a nicer way ;)

How does it work with CriteriaBuilder (and JpaSpecificationExecutor)?

---

The solution(s):

The first idea was a simple distinct:

public static Specification<MyEntity> isTrue() {
    return new Specification<MyEntity>() {
        @Override
        public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            SetJoin<MyEntity, JoinEntity> j = root.join(MyEntity.joinEntities, JoinType.LEFT);
            query.distinct(true); // <<-- HERE  
            return cb.isTrue(j.get(JoinEntity_.attr));
        }
    };
}

This works but kinda destroys the sense of those small Specification pieces. So I came up with a solution using a subquery. It may take some additional time, but for me it doesn't matter at the moment:

public static Specification<MyEntity> isTrue() {
    return new Specification<MyEntity>() {
        @Override
        public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Subquery<JoinEntity> subquery = query.subquery(JoinEntity.class);
            Root<JoinEntity> subRoot = subquery.from(JoinEntity.class);
            subquery.select(subRoot);
            subquery.where(cb.isTrue(subRoot.get(JoinEntity_.attr)));
            subquery.groupBy(subRoot.get(JoinEntity_.myEntity));

            return cb.exists(subquery);
        }
    };
}

Answer 1

You're looking for CriteriaQuery.distinct(true)