title: JPA: How do I sort on a Set field in my entity?
body: <p>I’m using Spring 3.2.11.RELEASE, Hibernate 4.3.6.Final, and JPA 2.1.  I have the following entity with the following field …</p>
  
  <pre><code>@Entity
  @Table(name = "user")
  public class User implements Serializable, Comparable&lt;User&gt;
  {
      …
      @ManyToMany
      @JoinTable(name = "user_organization", joinColumns = { @JoinColumn(name = "USER_ID") }, inverseJoinColumns = { @JoinColumn(name = "ORGANIZATION_ID") })
      @LazyCollection(LazyCollectionOption.FALSE)
      @SortNatural
      private SortedSet&lt;Organization&gt; organizations;
  </code></pre>
  
  <p>Above, the sort on organizations is done by the organization’s name field. When I run a JPA query to retrieve User objects, I would like to sort based on an ordered list of the organizations with which they are associated.  I have tried this …</p>
  
  <pre><code>final CriteriaBuilder builder = m_entityManager.getCriteriaBuilder();
  final CriteriaQuery&lt;User&gt; criteria = builder.createQuery(User.class);
  …
  final SetJoin&lt;User, Organization&gt; orgsJoin = orderByRoot.join(User_.organizations, JoinType.LEFT);
  orderByExpr = orgsJoin.get(Organization_.name);
  criteria.orderBy(builder.asc(orderByExpr);
  </code></pre>
  
  <p>But it doesn’t work for cases where a user is associated with multiple organizations — sometimes a user with an alphabetically lower organization in his list is returned ahead of the correct users.  Without writing a whole bunch of native SQL, how could I solve this problem with JPA/CriteriaBuilder?</p>
  
  <p><b>Edit:</b> Here's an example of what I want.  I am looking for distinct users.</p>
  
  <ul>
  <li>User A has organizations, "AAA Building" and "ZZZ Building"<br/></li>
  <li>User B has organizations of only "MMM Building"<br/></li>
  <li>User C has organizations "CCC Building" and "VVV Buidling"</li>
  <li>User D has organizations "AAA Building" and "MMM Buidling"</li>
  </ul>
  
  <p>I would want User D, User A, User C, and User B because the concatenation of user A's organizations are alphabetically lower than the concatenation of User C's organizations, which are in turn alphabetically lower than User B's organizations.</p>