Grails createCriteria order by condition on hasMany

Question

I'm try to order my createCriteria query by a specific entry of a hasMany. My domains look like:

class Asset {

  String name
  static hasMany = [
    supporters: SupportRole
  ]
}

class SupportRole {
  Asset asset
  Person person
  String role
}

class Person {
  String name
}

What I want to do is order my query of Assets by the Person class where role='Primary'. How do you do a join based on a condition? I came up with the following query but it doesn't work correctly:

assets = Asset.createCriteria.list() {
  supporters {
    like('role', 'Primary')
    person {
      order('name', sortOrder)
    }
  }
}

But this query doesn't discern the "role" attribute of the join table, it just eliminates Assets that don't have a 'Primary' SupportRole. I need it to be able to deal with nulls (e.g., there is no "Primary" SupportRole for the given Asset).

Thanks a million!

Answer 1

The criteria can be easily converted to HQL as below where do not have to deal with projections and aliases.

def assets = Asset.executeQuery("select a from Asset as a \
                                 inner join a.supporters as s \
                                 inner join s.person as p \
                                 where s.role = 'Primary' \ 
                                 order by p.name = :sortOrder",
                                 [sortOrder: 'desc'])