CODEWITHSUNDEEP
grails
Anonymous Human
Anonymous Human

Reputation: 1948

Grails: Eager fetching of one-to-manies

While reading about the GORM Gotchas I am a bit confused about eager fetching of one-to-manies. Whereas it is stated at the aforementioned link:

consider this query:

Author.list(max: 2, fetch: [ books: 'join' ])

In all likelihood, this will return only one Author instance. That’s probably not the behaviour you expect or want. So what’s happening?

Under the hood Hibernate is using a left outer join to fetch the books for each author. That means you get duplicate Author instances: one for each book the author is associated with. If you don’t have the max option there, you won’t see those duplicates because GORM removes them. But the trouble is the max option is applied to the result before the duplicates are removed. So in the example above, Hibernate only returns two results, both of which are likely to have the same author. GORM then removes the duplicate and you end up with a single Author instance.

The bolded portion of the above quote is where my confusion begins.If it is a left outer join shouldn't we end up getting books that are solely by every author(books only by one author) and not books that are by more than one author (the intersection of the sets which we don't get in a left outer join)? Or is this implying something about the treatment of books that maybe by more than one author by the [ books: 'join'] query modifier.

I'd be much obliged for any clarification.

Upvotes: 1

Views: 325

Answers (1)

Joshua Moore
Joshua Moore

Reputation: 24776

The confusion comes from your not understanding the SQL that is being executed using a LEFT JOIN to fetch the list of books. What the comment is trying to point out is that by using a LEFT JOIN the MAX gives you unexpected results. Consider the following

author
id, name
1, Bob
2, Joe

book
id, name
1, First book
2, Second book
3, Third book
4, Fourth book

author_books
author_id, book_id
1, 1
1, 2
1, 3
2, 4

The query uses a LEFT JOIN when fetching the Author and it's Book association so the data being returned from the database looks like this:

author.id, author.name, book.id, book.name
1, Bob, 1, First book
1, Bob, 2, Second book

This will result in a single author being returned but the collection of books will only include two of the three associated books. This is due to the MAX restriction being applied to the SQL statement itself and limiting the maximum number of rows returned.

The reason why this is pointed out is because you might expect that you'd get a maximum of two Authors in your list regardless of the number of associated books, but because of the fetch mode that's not the case because of the actual SQL that is being executed.

Hope that helps.

Upvotes: 2

Related Questions