oracle - Ordering on property of child object -

running grails 2.3.9 , having trouble query. have 2 domain objects:

class box {   string name } 

and

class skittle {   string name   box box } 

skittles in boxes, boxes don't have reference skittles. there lots of skittles, each box has skittles has hundreds of skittles. , there thousands of boxes.

i want distinct list of boxes have skittles sorted box.name.

i don't care if use hibernate or criteria, neither working me. when try criteria , projections, had like:

def c = skittle.createcriteria() list results = c.list {   projections {     distinct "box"   }   box {     order "name"   } } 

interestingly, worked against mysql database, did not work on oracle. second attempt hql:

list results = skittle.executequery("select distinct s.box skittle s order s.box.name") 

this worked in mysql, again failed in oracle (this time nasty error code ora-01791: not selected expression

checking hibernate logging, found it's creating criteria using 2 joins box:

select distinct  box1_.id id22_,  box1_.version version22_,  box1_.name name22_ skittle skittle0_ inner join box box1_ on skittle0_.box_id=box1_.id, box box2_  skittle0_.box_id=box2_.id  order box2_.name 

now error makes sense, can't order box2._name when i'm not selecting it. based on hibernate query, shouldn't joining 2 copies of box table.

(i tried aliasing s.box b , ordering on b.name hoping trick doing 1 join, crashed spectacularly)

you can use following query this

def boxes = box.executequery($/         select b         box b         exists(             select 1             skittle s             s.box = b         )         order b.name asc /$)