eager-loading queries with GORM/Hibernate

Question

Hi,

My Grails app has the following domain objects

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

class Attribute {       
    String name
    static belongsTo = [productType: ProductType]
}

My DB has 7 ProductTypes and each of those has 3 Attributes. If I execute the query:

def results = ProductType.withCriteria {
    fetchMode("attributes", org.hibernate.FetchMode.EAGER)
}

I expect 7 instances of ProductType to be returned, but in fact I get 21 (7 x 3). I understand that if I were to execute an equivalent SQL query to the above, the result set would have 21 rows

prod1 | attr1
prod1 | attr2
prod1 | attr3
..... | .....
..... | .....
prod7 | attr1
prod7 | attr2
prod7 | attr3
-------------
Total 21

But I thought that when I retrieve these results via Hibernate/GORM I should get something more like:

prod1 | attr1, attr2, attr3    
..... | ...................
..... | ...................
prod7 | attr1, attr2, attr3
---------------------------
Total 7

Incidentally, if I remove the eager-loading from the query above, I get 7 ProductTypes as expected. What am I missing?

Answer 1

Hello,

you should read this faq: Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?

When you specify eager loading, the resultset contains, as you noticed, 7*3 rows but in fact you only have 7 productTypes objects in memory (& 2 extra references for each).
To do what you want, you can add (be aware that the underlying sql query did not change):

SetResultTransformer(new DistinctRootEntityResultTransformer())

def results = ProductType.withCriteria {
    fetchMode("attributes", org.hibernate.FetchMode.EAGER)
    SetResultTransformer(new DistinctRootEntityResultTransformer())
}