Grails broken list() on domain object, fetchMode eager
I am using Grails 1.3.7 and found a very confusing issue. You could possibly run into this problem, maybe you have and that’s why you’re here! A colleague of mine setup a domain class that had
fetchMode = [aClass: 'eager']
This is almost always wrong, as GORM when using ‘eager’ or the synonym ‘join’ for this value will do a very dumb thing. It will use an outer join from parent to children, then limit on that cartesian product! So what you say? Well if you do this:
SomeDomainClass.list(max: 10)
You will see some odd results. Basically say SomeDomainClass has many AnotherDomainClass and that is set up with eager/join fetch. Well since the query works this way you could get very odd behavior. If the parent class has ten children expect to only get the one parent back from list(), not ten instances of the parent! Indeed, that sucks.
So you are saying “Ouch, this one hurts!” There probably should be a giant red stop sign in the documentation that makes you stop, and then be drilled on the pitfall(s) this encompasses, followed by a multiple choice quiz, or something.
For completeness here is the SQL GORM/Hibernate generates for the ‘eager’ case:
select ... from test_case_execution_context this_ left outer join test_case_execution_context_test_case_execution_step_status stepstatus2_ on this_.id=stepstatus2_.test_case_execution_context_step_status_id limit ?, ?
You see how that is not what you wanted, right?
Luckily the fix is pretty easy, though you might not like it for any odd reasons. Use the property lazy: false!
static mapping = {
aMember lazy: false
}
BooYahhh! Now you will get the results you are expecting.
With the lazy set to false you get results like this:
select ... from test_case_execution_context this_ limit ?, ?
Oh but where are the child domain results? Well, they get mapped in with the JDBC connection open, right away, in separate selects. Yes you are going to be making a bunch of db calls, but it’s all up front using one JDBC connection. This is the way it works folks. On the bright side now offset and max work!