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
PandaXPandaX 

Efficient SOQL for record classification

Hi awesome developers!

I need to "classify" an incoming Record A based on several related fields on records B,C,D,.. etc. Therefore I need a trigger that is triggering (:D) on this new record and check more then 5 lookup relationships for the content of specific fields in the related records B,C,D ...
Following https://developer.salesforce.com/page/Apex_Code_Best_Practices #2 I should not put the query for the related records in the foreach loop on record "A" Also I need to bulkify this, so it could handle the max load of 200 records entering the trigger at once.
What would be the most efficient way to query those related records for each incoming record and classify "A" according to the values in the related records?

 

If you could help me out here or point me in the right direction that would be really great!

Thanks,

Markus

Abdul KhatriAbdul Khatri
Your description is very generic, some specifics will definitely help provide the solution. Also we don't know what trigger context you are looking for like after insert, after update, before insert etc.

As per my understand by taking example here. Let say the major Object A, and there is a lookup fileds for the Object B, C and D as you mentioned. What my understanding that you are looking for is this
  
trigger SampleTrigger on ObjectA (before insert) {

	String sWhereClause = '';
    String sSOQL = '';
    
    List<Id> idBList = new List<Id>();
    List<Id> idCList = new List<Id>();
    List<Id> idBList = new List<Id>(); 
    
    List<ObjectA> objectAList = new List<ObjectA>();
    
    for(ObjectA A : Trigger.New){
        
        if(A.ObjectB__c != null)
            idBList.add(A.ObjectB__c);
        
        if(A.ObjectC__c != null)
            idBList.add(A.ObjectC__c);
        
        if(A.ObjectD__c != null)
            idBList.add(A.ObjectD__c);              
        
    }

	if(!idBList.isEmpty())
        sWhereClause = ' ObjectB__c = :idBList';
    
    if(!idCList.isEmpty()) {
        
        sWhereClause == '' ? ' ObjectC__c = :idCList' : 'OR ObjectC__c = :idCList';
    }
    
    if(!idDList.isEmpty()) {
        
        sWhereClause == '' ? ' ObjectD__c = :idDList' : 'OR ObjectD__c = :idDList';
    }    
        
	if(sWhereClause != '')
    {
        sSOQL = 'SELECT Id WHERE Id = :Trigger.new AND Where ' + sWhereClause;
        objectAList objAList = Database.query(sSOQL);
    }
    
    if(objAList.isEmpty()) return; 
    
    //More Code Here

}

Let me if this help as this is just to give you an idea.

 
PandaXPandaX
WIll try that approach! As I understand it this would be a viable way to get all the related records together and then do logic within the "context" of record A and the related ones. Will try if I can do it this way and come back to you :-) Thanks!
Abdul KhatriAbdul Khatri
How it came out?
PandaXPandaX
For the best answer check here: 
https://salesforce.stackexchange.com/questions/251498/efficient-soql-for-record-classification