Grails, why is join fetch not recommended for any single-ended association?
I saw in the grails doc something about fetch connection:
"This works well for uniform associations, but you need to be careful with one-to-manys. Queries will work as you would expect by the time you add a limit on the number of results you want. At this point, you are likely to get fewer results. than you'd expect. The reason for this is very technical, but ultimately the problem comes from GORM using left outer join. "
I don't understand why a left outer join can cause a problem in any single-ended association like one-to-many.
Can you give me an example?
if i take the example given by joshua
Class Person {
String name
static hasMany = [numbers:PhoneNumber]
static mapping = {
nubmers fetch : 'join'
}
}
Class PhoneNumber{
static belongsTo = [owner : Person]
}
//for testing
def person = Person.get(1)
result in:
select name,... from
person left outer join phone_number
on person.id = phone_number.owner_id
where person.id=1;
Can you give me a query (in the gorm) that can show me the problem?
thank
source to share
If I understand your question correctly, then it is easiest to explain with a simple example. Let's say we have a Person class with many PhoneNumber classes associated with it. So a LEFT JOIN to retrieve that data will result in an SQL output like this:
PersonId, Name, PhoneNumberId, TelNumber
-----------------------------------------
1, Joe, 1, 123-123-1234
1, Joe, 2, 222-222-2222
1, Joe, 3, 333-333-3333
2, Jane, 4, 000-000-000
This will be the four records returned from the data source, but only two instances of the Person domain with matching phone numbers. However, if you limit this request to a maximum of two records, you will actually only get one instance of the Person domain (Joe in this example) and its first two phone numbers.
Hope it helps to understand the underlying problem.
PS This is a completely theoretical example: the actual query results from GORM will have different column names and for example because of Hibernate.
Update
An example GORM-based query is simple enough to demonstrate:
def people = Person.list([max: 2])
You might expect the above to give you two instances, but in reality, due to the left join, you will only get one instance. Enabling SQL logging will show you the actual query being executed and you will notice it with a LEFT join.
source to share