GORM Querying where filter is not a direct attribute of a class.

Question

I am trying to figure out the most efficient / effective way of performing a certain type of query using Grails GORM.

This is the scenario where I want to query all of the children / linked items in a many to one relationship. This is a one way relationship whereby the many side used the id of the thing it is linked to.

One example in my system is a relationship between an Academic Year and the Semesters of that Academic Year. In the semester table the id of the academic year to which the semester belongs is stored in the table. The academic year however does not have anything it its table to link to the semesters which belong to it. This is done to keep the number of semesters flexible. What I hoped to do is use something like HQL to retrieve all the semesters which have the same academic year owning them. In regular SQL I could filter the rows using the academic year id column of the semester table. In HQL it is not as straightforward. The academic year id is not available as a property of the domain class. Instead Hibernate just uses it to load the actual academic year object.

Should something like "select from Semester as s where s.year.id = ?" work in this case? In this case the property of the semester domain class which holds the academic year is called year. The query is taking the year property and the id of that year and using it to filter the semesters.

This is just one example but the system I am developing contains more than one similar arrangement where it would be desirable to load a group of domain objects which all have the same linked domain object.

There may be a more efficient / effective way of doing this. One example would be to make the actual Academic Year id value available from the domain class. This would however mean the same column is mapped to more than one property of the domain class. This may well not be necessary but it is another possible way of solving this kind of problem.

I have some Hibernate experience but some problems get complicated in Hibernate when you want to do more unusual things.

Answer 1

This looks like basic 1:M relationship, which is handled by belongsTo/hasMany mappings. It doesn't make parent table keep any additional data. Just have domain objects:

class AcademicYear {
    static hasMany = [semesters: Semester]
}

class Semester {
    static belongsTo = AcademicYear
}

And Semester magically has "academicYear" propery which you can reference. And everything, just do it like:

AcademicYear y = AcademicYear.findByYear(2010)
Semester s = Semester.get(1)
y.addToSemesters(s)
y.semesters.each{ println it }
String year = s.academicYear.name
def a = s.academicYear.id

Try it in "grails console" first and enjoy.