Stack Exchange
Why left join on CriteriaQuery doesn't filter results?
I have two entities, User and Roles each one with active boolean parameter and ManyToMany relationship.
This boolean parameter is used for logical delete.
Why does the result include inactive Roles when I run this query below?
 CriteriaBuilder builder = em.getCriteriaBuilder();
 CriteriaQuery<User> query = builder.createQuery(User.class);

 //Select
 Root<User> user = query.from(User.class);

 //Join
 SetJoin<User, Role> role = user.joinSet("roles", JoinType.LEFT);
 Predicate rolesActivePredicate = builder.isTrue(role.get("active"));
 role.on(rolesActivePredicate);

 query.multiselect(user).distinct(true);

 //Where
 Predicate usernamePredicate = builder.equal(user.get("username"), username);
 Predicate activePredicate = builder.isTrue(user.get("active"));
 query.where(usernamePredicate, activePredicate);

 TypedQuery<UnikUser> typedQuery = em.createQuery(query);
 return Optional.of(typedQuery.getSingleResult());


If you don't have strong reasons for suspecting the issue is caused by jpa 2.2, you should use the jpa tag instead, in order to attract more answers. For the same reason, you should add the criteria-api tag


Consider the following Users with @OneToMany relationship to Roles.

 User-1 : Active-Role-1, Active-Role-2, In-Active-Role-3
 User-2 : In-Active-Role-3
 User-3 : Active-Role-1, Active-Role-2


JPA first finds User records that satisfy your condition (have at-least one an active role)

 User-1
 User-3


This is where the role of your where conditions end. Once it has been decided which record id to fetch, JPA will fetch those User records in full. User-1 and User-3 with all their associated roles (including inactive). It might do join or another select but whenever it does, it will fetch all of its associated fields

In summary, Once it has decided to fetch an entity, it cannot do any filtering on its associated fields. In this case, you expected a filtered objects in user.getRoles() but it cannot.

If JPA had allowed that, it cannot do dirty checking, cascading or repeatable read. So it does not allow it



Gotcha! Is there and alternative to avoid this inactive results?

well. You know you have the right users. So when you create userDto, why don't you do the filtering? i.e when mapping user to userDto, you can create a custom mapper called  UserToUserWithActiveRolesDtoMapper and then do your filtering in memory. Or you have to write queries that select columns and use row mapper to create objects

I don't have reputation for it.
