Using all() to get a collection of entities returns an instance of an anonymous SizedIterable object.
When eager loading references from this object, with() first iterates over the object then preloads relations for each retrieved entity, which correctly generates 2 SQL statements:
val x: SizedIterable<School> = School.all().with(School::region)
// SELECT SCHOOL.ID, SCHOOL."name", SCHOOL.REGION_ID, SCHOOL.SECONDARY_REGION_ID FROM SCHOOL
// SELECT REGION.ID, REGION."name" FROM REGION WHERE REGION.ID = 1
If this eager loading is followed by iteration (using toList() or forEach() e.g.), the first SQL statement is executed again because the anonymous object delegates to the source iterator, AbstractQuery.iterator and its results (from the first execution) are discarded after being used to load references.
The retrieved references, however, are cached in each Entity.referenceCache, so the second SQL is not executed again:
val x: List<School> = School.all().with(School::region).toList()
// SELECT SCHOOL.ID, SCHOOL."name", SCHOOL.REGION_ID, SCHOOL.SECONDARY_REGION_ID FROM SCHOOL
// SELECT REGION.ID, REGION."name" FROM REGION WHERE REGION.ID = 1
// SELECT SCHOOL.ID, SCHOOL."name", SCHOOL.REGION_ID, SCHOOL.SECONDARY_REGION_ID FROM SCHOOL
This occurs because with() was refactored in PR #1585 to return the same invoking type, rather than a List. So the source iterator of the returned SizedIterable executes a query when called.
This fix stores the result of the first query in the SizedIterable instance and ensures the right iterator is invoked if a result has been cached.
Using
all()to get a collection of entities returns an instance of an anonymousSizedIterableobject. When eager loading references from this object,with()first iterates over the object then preloads relations for each retrieved entity, which correctly generates 2 SQL statements:If this eager loading is followed by iteration (using
toList()orforEach()e.g.), the first SQL statement is executed again because the anonymous object delegates to the source iterator,AbstractQuery.iteratorand its results (from the first execution) are discarded after being used to load references. The retrieved references, however, are cached in eachEntity.referenceCache, so the second SQL is not executed again:This occurs because
with()was refactored in PR #1585 to return the same invoking type, rather than a List. So the source iterator of the returnedSizedIterableexecutes a query when called.This fix stores the result of the first query in the
SizedIterableinstance and ensures the right iterator is invoked if a result has been cached.