Salesforce, from time to time, uses some not-so-clear wording and terminology. A perfect example is "Aggregate" queries. You might think that an "Aggregate" query is one that uses Aggregate functions like COUNT()
or MIN()
along with GROUP BY
.
That's completely logical (heck, you even get an AggregateResult[]
from a query that uses Aggregate functions), but incorrect (or, at least, incomplete).
In Salesforce's world, the "Aggregate" query in the error that you're getting is a subquery, something like a left outer join (parent-child subquery) or a semijoin (subquery in the WHERE
clause).
Unfortunately, there isn't any official documentation that I can find that explains exactly why this happens.
Probably the best documentation I can find that is related to this issue is in two pages Understanding Query Results and SOQL for loops. From the first page (emphasis mine):
Subquery results are like regular query results in that you might need to use queryMore() to retrieve all the records if there are many children. For example, if you issue a query on accounts that includes a subquery, your client application must handle results from the subquery as well:
- Perform the query on Account.
- Iterate over the account QueryResult with queryMore().
- For each account object, retrieve the contacts QueryResult.
- Iterate over the child contacts, using queryMore() on each contact's QueryResult.
From the second page:
You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop
. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set. For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.
for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts)
FROM Account WHERE Id IN ('<ID value>')]) {
List<Contact> contactList = acct.Contacts; // Causes an error
Integer count = acct.Contacts.size(); // Causes an error
}
So the error likely has something to do with chunking the retrieval of records returned by a query and queryMore(). Saying anything beyond that would probably be speculation.
Yours is the first situation that I've seen where this error has popped up having 200+ child records spread across more than one child SObject.
I can't give a satisfactory answer to your 2nd, 3rd, and 4th issues. The workaround for this error is to use a for()
loop, as suggested. That would probably look like this in your case:
while (it.hasNext())
{
SObject l = it.next();
for(SObject child :l.getSObjects('Doors__r'))
{
// add child records to a collection, or perform
// some other computation/logic, one-by-one in here.
}
}