Duplicate results withCriteria
If you enforce eager fetching in your model mappings it will most likely result in duplicate entries returned via withCriteria query. The duplicates are caused by Hibernate internally joining your root and collection tables. The workaround is pretty simple, just add Criteria.DISTINCTROOTENTITY to the withCriteria closure.
def objs = Model.withCriteria {
eq("field", value)
setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
}It is a good practice to avoid eager fetching on your models - don't use it unless you have to.
p.s. as Ben pointed out uniqueResult will also work if a single result is expected.
p.s.s. as Ben also pointed out: It also looks like you can accomplish what you want by just doing createCriteria().list {} rather than withCriteria since the list method sets DISTINCTROOTENTITY.
Written by Andrei Taraschuk
Related protips
4 Responses
You can abstract away the resultTransformer by specifying uniqueResult: true:
def objs = Model.withCriteria(uniqueResult: true) { ... }
@bendoerr not sure if that applies since I need to return a collection of models and not a single one. That said, this will be a good solution when a single result is expected.
@andreitr I jumped the gun. Taking a look at the src you are right. It also looks like you can accomplish what you want by just doing createCriteria().list {} rather than withCriteria since the list method sets DISTINCT_ROOT_ENTITY. Good tip though for when you are working with withCriteria.
@tom awesome, thanks. Will take a look at the list method.
