Criteria Query for matching entities by sets of related entities

Question

I have 2 entities User and UserRole. Both are ManyToMany related to UserRole. For a given user I want to write a specification that will filter out UserRoles that have any UserRole.rolesRequiredForAssignment that aren't contained in User.userRoles

@Entity
public class UserRole {
  @Id
  private Integer roleId;

  @ManyToMany
  private Set<UserRole> rolesRequiredForAssignment = new HashSet<>();
}


@Entity
public class User {
  @Id
  private Integer userId;

  @ManyToMany
  private final Set<UserRole> userRoles = new HashSet<>();
}

This is what I started with, but is not correct because the User can have roles that aren't required.

public static Specification<UserRole> hasRequiredRole(UserRole userRole) {
  return (root, query, builder) -> {
    Path<Set<UserRole>> rolesRequiredForAssignment = root.get("rolesRequiredForAssignment");
    return builder.isMember(userRole, rolesRequiredForAssignment);
  };
}

public static Specification<UserRole> hasRequiredRoles(Collection<UserRole> userRoles) {
  return (root, query, builder) -> {
    List<Predicate> requiredRolePredicates = new ArrayList<>();
    for (UserRole userRole : userRoles) {
      requiredRolePredicates.add(hasRequiredRole(userRole).toPredicate(root, query, builder));
    }
    return builder.and(requiredRolePredicates.toArray(new Predicate[]{}));
  };
}

I'm not sure how to think about the approach here. It seems like I should left join from UserRole.rolesRequiredForAssignment to User.userRoles and then evaluate if any are null to return then boolean.

But, not sure how to implement this anyway, so any help?

*** Update

This is the PostgreSQL code that gives what I want. user_roles that are not "assignable" by a certain user.

SELECT ur.*
FROM user_roles ur
LEFT JOIN user_role_requirements urr ON ur.role_id = urr.target_role_id
LEFT JOIN user_user_role uur
    ON uur.role_id = urr.required_role_id 
        AND uur.user_id = 1
GROUP BY ur.role_id
HAVING bool_and(urr.target_role_id IS NULL) OR bool_and(uur.role_id IS NOT NULL)

**** Update I think I was able to solve this after much tinkering.

The breakthrough was because JPA forces unrelated entities to use CROSS JOIN and RIGHT JOIN is not supported, so had to throw out the idea that I could introduce a join with a condition that would create a null value.

Instead, just group by role and test if the number of required roles that joined correctly is equal to the number of required roles originally.

public static Specification<UserRole> userHasRequiredRoles(User user) {
  return (root, query, builder) -> {

    Subquery<UserRole> sq = query.subquery(UserRole.class);
    Root<UserRole> userRoleRoot = sq.from(UserRole.class);
    SetJoin<Object, Object> requiredRoles = userRoleRoot.joinSet(
        "rolesRequiredForAssignment");
    Root<User> userRoot = sq.from(User.class);
    SetJoin<Object, Object> userRoles = userRoot.joinSet("userRoles");
    Predicate userEqualsUser = builder.equal(userRoot, user);
    Predicate userRolesEqualRequiredRoles = builder.equal(userRoles, requiredRoles);

    Subquery<Long> sqCount = query.subquery(Long.class);
    Root<UserRole> userRoleRoot2 = sqCount.from(UserRole.class);
    SetJoin<Object, Object> rolesRequiredForAssignment1 = userRoleRoot2.joinSet(
        "rolesRequiredForAssignment");
    Subquery<Long> sqCountResult = sqCount.select(builder.count(rolesRequiredForAssignment1))
        .where(builder.equal(userRoleRoot2, userRoleRoot));

    Subquery<UserRole> sqAssignableRoles = sq.select(userRoleRoot)
        .where(builder.and(userEqualsUser, userRolesEqualRequiredRoles))
        .groupBy(userRoleRoot)
        .having(builder.equal(builder.count(requiredRoles), sqCountResult));

    return root.in(sqAssignableRoles);
  };
}

Important caveat. This does not return any UserRoles that don't have any requiredForAssignment Roles. I can fix that with a separate Specification and combine them. I'll update if I end up adding that.

Answer 1

I currently don't have the time to write it in criteria, but this should be the sql approach:

select distict urrrfa.roles_required_for_assignment_id
from user u
         left join user_user_role uur on u.id = uur.user_id
         left join user_role_roles_required_for_assignment urrrfa
                   on uur.user_role_id = urrrfa.user_role_id
         left join user_user_role uur2 on uur2.user_id = u.id and
                                          uur2.user_role_id = urrrfa.roles_required_for_assignment_id
where uur2.user_role_id is null and u.id = :uid

this should return all required roles that are not in the users roles list