Ordering on property of child object

Question

Running Grails 2.3.9 and having trouble with a query. Say I have 2 domain objects:

class Box {
  String name
}

and

class Skittle {
  String name
  Box box
}

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.

I want a distinct list of all boxes that have skittles sorted by box.name.

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:

def c = Skittle.createCriteria()
List results = c.list {
  projections {
    distinct "box"
  }
  box {
    order "name"
  }
}

Interestingly, this worked against a MySQL database, but did not work on Oracle. My second attempt was with HQL:

List results = Skittle.executeQuery("SELECT DISTINCT s.box FROM Skittle s ORDER BY s.box.name")

This also worked in MySQL, but again failed in Oracle (this time with a nasty error code ORA-01791: not a SELECTed expression

Checking Hibernate logging, I found that it's creating the criteria using 2 joins to Box:

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

Now the error makes sense, I can't order by box2._name when I'm not selecting it. But based on my Hibernate query, I shouldn't be joining 2 copies of the box table.

(I tried aliasing s.box as b and ordering on b.name hoping that would trick it into only doing one join, but that crashed spectacularly)

Answer 1

You can use the following query for this

def boxes = Box.executeQuery($/
        SELECT b
        FROM Box b
        WHERE EXISTS(
            SELECT 1
            FROM Skittle s
            WHERE s.box = b
        )
        ORDER BY b.name ASC
/$)