function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Esteban Castro REsteban Castro R 

Opinion on For inside For the SOQL internal object

Hi Team,

I wonder if there's a best practice regarding the following scenario for  going on a for loop inside a for loop for a SOQL query on an object with a sub query. For example: 
List<Parent__c> lp = [
  SELECT Id, Name,
    (SELECT Id, Name FROM Child__r WHERE Name = :childFilter)
  FROM Parent__c
  WHERE Name = :parentFilter
];

for(Parent__c p : lp){
  System.debug('Parent__c Name: ' + p.Name);
​  for(Child__c c : lp.Child__r){
    System.debug('Child__c Name: ' + c.Name);
  }
}
Should I build the query on the child level and populate a Map<Id,Child__c> with back references to the parent object attributes, then do a for inside a for loop over map Id, then object? Thanks a lot.
Best Answer chosen by Esteban Castro R
jigarshahjigarshah
Esteban,

A sub query is leveraged generally in Apex when you are trying to access Child object records from the Parent object records. for e.g. accessing all Invoices along with their Line Item records which are not paid.

If you only need the parent id, then it makes sense to write a query on the Child object and access the Parent fields through the parent relationship fields.

You can also separate out the queries in case nested loops are causing a performance bottleneck in your code.

Ideally as a general programming best practice you should avoid nested loops unless needed. Also, this is subjective to your business case and the problem that you are trying to solve.

All Answers

jigarshahjigarshah
Esteban,

A sub query is leveraged generally in Apex when you are trying to access Child object records from the Parent object records. for e.g. accessing all Invoices along with their Line Item records which are not paid.

If you only need the parent id, then it makes sense to write a query on the Child object and access the Parent fields through the parent relationship fields.

You can also separate out the queries in case nested loops are causing a performance bottleneck in your code.

Ideally as a general programming best practice you should avoid nested loops unless needed. Also, this is subjective to your business case and the problem that you are trying to solve.
This was selected as the best answer
Esteban Castro REsteban Castro R
Thanks for your answer Jigarshah. Actually, it's for a trigger, I need to go over the parent elements and fetch several values from the parent, then have them available when processing all the children elements, which are already filtered on the child filter.
Regards.
jigarshahjigarshah
Esteban,
  • If you are using an After Trigger on the parent record, use the Trigger.newMap and Trigger.oldMap that provides a Map<ParentRecordId, ParentRecord>. This enables you to access a set of Parent Record Ids for querying child records as well as to respective field values on Parent records.
  • You can then query the respective child records and populate a Map<ParentId, List<Child Records>> to make your search efficient.
jigarshahjigarshah
Esteban,

Has your query been addressed? Do you still need clarification?
Esteban CastroEsteban Castro
It has, thank you so much Jigarshah.
jigarshahjigarshah
Can you please close this thread?