title: JPA Join on multiple columns
body: <p>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.</p>
  
  <pre><code>@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&lt;GrantedRole&gt; roles = new HashSet&lt;GrantedRole&gt;();
      private Set&lt;Organization&gt; organizations = new HashSet&lt;Organization&gt;();
  
      @OneToMany(fetch = FetchType.EAGER, mappedBy = "user")
      public Set&lt;GrantedRole&gt; getRoles() {
          return roles;
      }
  
      @ManyToMany(fetch = FetchType.EAGER)
      public Set&lt;Organization&gt; getOrganizations() {
          return organizations;
      }
  
      // Some setters
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>My code for finding the Organizations:</p>
  
  <pre><code>public List&lt;Organization&gt; findOrganizationsByOrgAdminUser(String 
      CriteriaBuilder cb = entityManager.
      CriteriaQuery&lt;Organization&gt; query = cb.createQuery(Organization.
      Root&lt;User&gt; root = query.from(User.class);
      SetJoin&lt;User, Organization&gt; joinOrg = root.joinSet("organizations");
      SetJoin&lt;User, GrantedRole&gt; 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();
  }
  </code></pre>
  
  <p>The generated query is:</p>
  
  <pre><code>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=?
  </code></pre>
  
  <p>The query I would want is:</p>
  
  <pre><code>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=?
  </code></pre>