SOQL with LIMIT statement returns incomplete results in VF and aura contexts

Question

I have an SOQL with LIMIT statement. The field list contains 164 fields of different types. Some of them are lookups, others are LongText, Text, Number.

The SOQL looks like this:

SELECT <fields> FROM <object__c>  WHERE Field1__c = :value  ORDER BY Lookup__r.Field2__c, Id LIMIT 200 OFFSET 0

When it runs in VF or Aura contexts, the number of rows returned is 62, although there are deffinetely more rows to return.

When I run this in a SOAP-call context or as anonymous apex, it returns all 200 rows I expect.

It also returns the complete result set of 200 rows when I replace the large field list with a list of 1-2 fields.

It also works correctly without OFFSET statement.

Is there an explanation of that behavior? And a way to workaround this..

Answer 1

The VF controller needs to implement the queryMore feature

for (MyObject__c obj: [SELECT ... FROM MyObject__c]) {
  // do something like collect into a list
}

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

When the payload is "large", SFDC will break up the query results into chunks, with successive chunks available from the queryMore URL. SOAP API uses queryMore but unless you use the for loop construction in Apex, you'll get the results you observed because you are fetching heavy payloads (many fields, some text area (long).