title: Self-join with Hibernate Criteria
body: <p>Can't figure out a way to add order by for a Criteria on this kind of pseudo search index table:</p>
  
  <pre><code>----------------------------------
  | id | person_id | field | value |
  ----------------------------------
  | 1  | 1         | fname  | John |
  | 2  | 1         | lname  | Smith|
  | 3  | 1         | zip    | 3000 |
  | 4  | 2         | fname  | John |
  | 5  | 2         | lname  | Doe  |
  | 6  | 2         | zip    | 3000 |
  ----------------------------------
  </code></pre>
  
  <p>The search_index table is an index for person entity, which can have any number of any kind of attributes (this is a simplified example of a real life problem of course). One "person" can have fname set, another may not and so on. Then a client app sends a generic search request, f.i. ((fname=John OR fname=Karl) AND lname=Smith and zip=3000) - this kind of stuff. So the system is fairly generic - list of searchable fields in search_index and any kind of boolean search query. Same goes for orderby - can be any of this searchable fields. </p>
  
  <p>In SQL I would do something like:</p>
  
  <pre><code>SELECT si1.person_id from search_index si1
  LEFT OUTER JOIN search_index si2 ON si1.id = si2.id
  WHERE si2.field='lname'
  AND si1.person_id in (select person_id from search_index where field = 'zip' and answer = '3000')
  ORDER BY si2.value
  </code></pre>
  
  <p>to order found results by "lname". </p>
  
  <p>It's probably possible with HQL but the search criterias are built dynamically from a search string and Hibernate Criteria API makes it a lot easier compare to dynamic HQL construction. </p>