I need some clarification as to why the the two scenarios below give different results. I am retrieving a record (IO Campaign, with all its children from Inventory Reservation). These are both custom objects in our organization. There are actually 561 inventory Reservations associated with the IO Campaign I am retrieving, so that is the value I am expecting in the list, invresItems.

1. The first scenario, I use one query to get data from a parent child relationship.:

public IO_Campaign__c io_campaign { get; private set;}
public List<Inventory_Reservation__c> invresItems { get; private set; }

io_campaign = [SELECT Id, Name,
(SELECT Id, Name FROM Inventory_Reservations__r ORDER BY Name) FROM IO_Campaign__c
where id = :id limit 1];
invresItems = io_campaign.Inventory_Reservations__r;
System.debug('invresItems.size()=' + invresItems.size());

 

When I debug this, the output for invresItems.size() is 499


2. The second scenario:

public IO_Campaign__c io_campaign { get; private set;}
public List<Inventory_Reservation__c> invresItems { get; private set; }

io_campaign = [SELECT Id, Name FROM IO_Campaign__c where id = :id limit 1];
invresItems = [SELECT Id, Name FROM Inventory_Reservation__c ORDER BY Name where IO_Campaign__r.Id = :io_campaign.Id];
System.debug('invresItems.size()=' + invresItems.size());

When I debug this, the output for invresItems.size() is 561. This is the expected output.

Why is the first scenario truncating the list at 499? I believe it has to do with the way Salesforce returns data in the API where you have to use the querymore function to retrieve the rest of the rows. I don't believe I can use that in Apex, so what is the correct approach. Am I missing something?

Also, I found an article What does the Invalid Query Locator error mean? and it explained that APEX might create a query locator when you use an inner query and the query locator will be used with a querymore when you try to retrieve the inner query data.

I tried to change the code to the below and it still only gave me the 499 records.

public IO_Campaign__c io_campaign { get; private set;}
public List<IO_Campaign__c > lst_io_campaign { get; private set;}
public List<Inventory_Reservation__c> invresItems { get; private set; }

invresItems = new List<Inventory_Reservation__c>();


lst_io_campaign = [SELECT Id, Name,
(SELECT Id, Name FROM Inventory_Reservations__r ORDER BY Name) FROM IO_Campaign__c
where id = :id limit 1];

for(IO_Campaign__c io : lst_io_campaign) // queryMore() will be used to retrieve child cases
            invresItems.addAll(io.Inventory_Reservations__r);       


io_campaign = lst_io_campaign[0];
System.debug('invresItems.size()=' + invresItems.size());


When I debug this, the output for invresItems.size() is 499


So it seems as if the inner query is only retrieving the 500 (the one IO Campaign record and the 499 Inventory Reservation records), but the For Loop should add the rest by using the query locator and the querymore function.  This isn't happening.

Any help will be greatly appreciated.