findAll order by "many to one" column raises exception

I have an entity, Student, defined in Student.groovy as:

@EqualsAndHashCode(includes = ['id'])
class Student {

    Long id
    String name
    String type

    University university     

    static mapping = {
         university column : 'UNIVERSITY_ID'
    } 

}

and a University entity, defined in University.groovy as:

class University {
     Long id
     String name

     static mapping = {
         id column : 'id', generator : 'assigned'
     }
}

I've been trying to switch from calling

Student.list(sort: ..., order: ...)

to calling:

Student.findAll("from Student s where type = :type ", [type : 'T'], [ sort : 'name' ])

This fails to order correctly by the name field. The previous version, using list worked fine.

I've also tried calling something like

Student.findAll(sort : 'name') { type == "T" }

which worked fine like this, but when trying to sort by the university.name

Student.findAll(sort : 'university.name') { type == 'T" }

it raised an error regarding the university.name field not being found.

Anybody have any idea on how to do this properly?

Thank you.

Solution

Use executeQuery instead of findAll - they should function the same, but I've found that executeQuery is for some reason a more direct caller of the HQL, and findAll fails or returns unexpected results in some cases.

So that first query would be

Student.executeQuery(
   'select s from Student s where s.type = :type order by s.name',
   [type : 'T'])

and ordering by university name would be

Student.executeQuery(
   'select s from Student s where s.type = :type order by s.university.name',
   [type : 'T'])

I like HQL and tend to use it a lot, but it couples you to Hibernate and relational databases - if you want to switch to a NoSQL database these queries will fail. Criteria queries, "where" queries and finders all use criteria queries internally, and those are converted to native query API calls by the GORM implementation.

The equivalent criteria queries would be

Student.withCriteria {
   eq 'type', 'T'
   order 'name', 'asc'
}

and

Student.withCriteria {
   eq 'type', 'T'
   university {
      order 'name', 'desc'
   }
}

Some unrelated notes:

You shouldn't use id in equals or hashCode calculations; if you have a persistent Student and a new non-persistent instance with the same name, type, and University, they should be considered equal, but since the non-persistent instance's id will be null they'll be considered different.

You don't need to specify the id property - Grails adds it and the version field to the bytecode via an AST transformation during compilation.

There's no need to map the column name of the university property to 'UNIVERSITY_ID' - that's what it would be anyway.

You can omit the redundant column setting in the id mapping.

Here's the Student class with cruft removed:

@EqualsAndHashCode(includes = ['name', 'type', 'university'])
class Student {
   String name
   String type
   University university
}

and University:

class University {
   String name
   static mapping = {
      id generator: 'assigned'
   }
}