JPA Join on multiple columns

Question

I have entities User, Organization and GrantedRole. Their relation is that a GrantedRole defines the role a User has in an Organization. All three entities inherit a field 'id' (annotated with @Id) from their superclass. Organization has no references to GrantedRole or User, those relations are unidirectional.

@Entity
@Table(name = "role_assignments", uniqueConstraints = @UniqueConstraint(columnNames = {
    "user_id", "organization_id" }))
public class GrantedRole extends DomainEntity implements GrantedAuthority {

    public static enum Role {
        ROLE_MODM_USER, ROLE_MODM_ORGADMIN, ROLE_MODM_ADMIN
    }

    private User user;
    private Role role;
    private Organization organization;

    public static enum Role {
        ROLE_MODM_USER, ROLE_MODM_ORGADMIN, ROLE_MODM_ADMIN
    }

    @ManyToOne
    @NotNull
    public User getUser() {
        return user;
    }

    @NotNull
    @Enumerated(EnumType.STRING)
    public Role getRole() {
        return role;
    }

    @ManyToOne
    public Organization getOrganization() {
        return organization;
    }

    // Some setters
}

@Entity
@Table(name = "modmUsers")
public class User extends DomainEntity implements UserDetails {
    // Some other fields

    private Set<GrantedRole> roles = new HashSet<GrantedRole>();
    private Set<Organization> organizations = new HashSet<Organization>();

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "user")
    public Set<GrantedRole> getRoles() {
        return roles;
    }

    @ManyToMany(fetch = FetchType.EAGER)
    public Set<Organization> getOrganizations() {
        return organizations;
    }

    // Some setters
}

Now I want to use JPA Criteria to find the Organizations in which a User has the role ROLE_MODM_ORGADMIN. But the generated query joins on a single column, and that leads to repetition of the rows from GrantedRole, because User.id is not unique within GrantedRole, nor is Organization.id. The combination of both is unique.

My code for finding the Organizations:

public List<Organization> findOrganizationsByOrgAdminUser(String 
    CriteriaBuilder cb = entityManager.
    CriteriaQuery<Organization> query = cb.createQuery(Organization.
    Root<User> root = query.from(User.class);
    SetJoin<User, Organization> joinOrg = root.joinSet("organizations");
    SetJoin<User, GrantedRole> joinRoles = root.joinSet("roles");

    Predicate p1 = cb.equal(root.get("id"), userId);
    Predicate p2 = cb.equal(joinRoles.get("role"), Role.ROLE_MODM_ORGADMIN);

    query.select(joinOrg);
    query.where(cb.and(p1, p2));

    return entityManager.createQuery(query).getResultList();
}

The generated query is:

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
WHERE
    users.id=? 
    and roles.role=?

The query I would want is:

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
        AND orgs.id=roles.organization_id //how to do this???
WHERE
    users.id=? 
    and roles.role=?

Answer 1

Ok, so my colleague helped me out and led me to this query:

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
WHERE
    users.id=? 
    AND roles.role=?
    AND orgs.id=roles.organization_id

Which is a simple extension of my code:

public List<Organization> findOrganizationsByOrgAdminUser(String 
    CriteriaBuilder cb = entityManager.
    CriteriaQuery<Organization> query = cb.createQuery(Organization.
    Root<User> root = query.from(User.class);
    SetJoin<User, Organization> joinOrg = root.joinSet("organizations");
    SetJoin<User, GrantedRole> joinRoles = root.joinSet("roles");

    Predicate p1 = cb.equal(root.get("id"), userId);
    Predicate p2 = cb.equal(joinRoles.get("role"), Role.ROLE_MODM_ORGADMIN);
    Predicate p3 = cb.equal(joinOrg.get("id"), joinRoles.get("organization"));

    query.select(joinOrg);
    query.where(cb.and(p1, p2, p3));

    return entityManager.createQuery(query).getResultList();
}