GORM Hibernate query

Question

Hi,

I have the following Grails domain objects

class ProductType {
    String name
    static hasMany = [attributes: Attribute]
}

class Attribute {

    Boolean mandatory = false
    Integer seq

    static belongsTo = [productType: ProductType]
}

I would like to get all ProductTypes and their mandatory Attributes. Furthermore, I'd like the selected Attributes to be eagerly loaded and sorted by the seq property. I've tried all kinds of HQL and Criteria queries, but can't seem to figure it out.

Answer 1

This query will return all ProductTypes that have mandatory attributes, with those attributes eagerly loaded:

def types = ProductType.executeQuery("""
   select distinct type from ProductType type
   left join fetch type.attributes attribute
   where attribute.mandatory=true""")

The attributes are in mapped Sets, so there's no ordering, but it's easy enough to collect and sort them:

def sortedAttributes = types.collect { it.attributes }.flatten().sort { it.seq }

Answer 2

Alternatively, you can get a sorted list of the many side by implementing a Comparable Interface in the many-side domain, and injecting SortedSet in the one-side (instead of the default Set).

class ProductType {
    String name
    SortedSet attributes
    static hasMany = [attributes: Attribute]
}

class Attribute implements Comparable {

    Boolean mandatory = false
    Integer seq

    static belongsTo = [productType: ProductType]

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