sorting problem with multiple joins

Question

I have a problem getting sorting to work with multiple joined tables. For example:

class Account {

  static hasMany = [subscriptions: Subscription]

  static mapping = {
    subscriptions fetch: 'join'
    subscriptions sort: 'magazine'
  }

}

class Subscription {

  static belongsTo = [account:Account, magazine: Magazine]

  static maping = {
    magazine fetch: 'join'
    magazine sort: 'name'
  }

}

class Magazine {
  String name  
  static mapping = {
    sort name: 'desc'
  }
}

When someAccount.subscriptions is called generated query orders by magazine.id. Is there any way to get order by magazine.name? I tried changing to subscriptions sort: 'magazine.name' but get an error there is no such property.

Following: http://grails.org/doc/latest/guide/single.html#5.5.3 Default Sort Order I tried to move sort to association level by removing sort from Account and keeping sort in Subscription only but that ended up removing "order by" from resulting query completely. Any ideas? Thanks.

Answer 1

Try implementing a compareTo method in your Magazine class:

class Magazine implements Comparable {
  String name  

  int compareTo(obj) {
      name.compareTo(obj.name)
  }
}

Then one in Subscription

class Subscription implements Comparable {

    static belongsTo = [account:Account, magazine:Magazine]
    static constraints = {
    }
    int compareTo(obj) {
       return magazine.compareTo(obj.magazine)
    }
}

Then make the subscriptions a SortedSet in Account:

class Account {
    SortedSet subscriptions
    static hasMany = [subscriptions:Subscription]
}