title: Ordering on property of child object
body: <p>Running Grails 2.3.9 and having trouble with a query.  Say I have 2 domain objects:</p>
  
  <pre><code>class Box {
    String name
  }
  </code></pre>
  
  <p>and</p>
  
  <pre><code>class Skittle {
    String name
    Box box
  }
  </code></pre>
  
  <p>Skittles are in boxes, but boxes don't have a reference to skittles.  There are lots of skittles, each box that has skittles probably has hundreds of skittles.  And there are thousands of boxes.</p>
  
  <p><strong>I want a distinct list of all boxes that have skittles sorted by <code>box.name</code>.</strong></p>
  
  <p>I don't care if I use Hibernate or criteria, but neither is working for me.  When I try criteria and projections, I had something like:</p>
  
  <pre><code>def c = Skittle.createCriteria()
  List results = c.list {
    projections {
      distinct "box"
    }
    box {
      order "name"
    }
  }
  </code></pre>
  
  <p>Interestingly, this worked against a MySQL database, but did not work on Oracle.  My second attempt was with HQL:</p>
  
  <pre><code>List results = Skittle.executeQuery("SELECT DISTINCT s.box FROM Skittle s ORDER BY s.box.name")
  </code></pre>
  
  <p>This also worked in MySQL, but again failed in Oracle (this time with a nasty error code <code>ORA-01791: not a SELECTed expression</code></p>
  
  <p>Checking Hibernate logging, I found that it's creating the criteria using 2 joins to <code>Box</code>:</p>
  
  <pre><code>select distinct 
  box1_.id as id22_, 
  box1_.version as version22_, 
  box1_.name as name22_
  from skittle skittle0_ inner join box box1_ on skittle0_.box_id=box1_.id, box box2_ 
  where skittle0_.box_id=box2_.id 
  order by box2_.name
  </code></pre>
  
  <p>Now the error makes sense, I can't order by <code>box2._name</code> when I'm not selecting it.  But based on my Hibernate query, I shouldn't be joining 2 copies of the <code>box</code> table.</p>
  
  <p>(I tried aliasing <code>s.box as b</code> and ordering on <code>b.name</code> hoping that would trick it into only doing one join, but that crashed spectacularly)</p>