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
Wouter FrankhuizenWouter Frankhuizen 

Comparing multiple field values from one Object with another Object

Hi,
 

This is a question which I really can't find an answer to. In a trigger before insert or update, I like to compare 4 field values from the object named Calculation__c with 4 field values of Driver_Course__c.

The fields on Calculation__c are named: 

  • Course__c
  • Cup__c
  • Tour__c
  • Driver__c
Those 4 fields i like to compare with the following fields of the object Driver_Course__c:
  • Course__c
  • Course__r.Cup__c
  • Course__r.Cup__r.Tour__c
  • Driver__c
With nested for loops it's easy, but I don't want to run into any governor limits, which I will regarding my test class, which gives me the error: CPU Limits exceeds.

I know how to get the data prepared for matching, but the matching itself I really dont know what to do. After removing and adding stuff this is what i got:
  
public without sharing class AutoFillCalculations {
    public static void FillOut(List<Calculation__c> calcList) {

        // Id nodig van Course
        Set<Id> courseIdSet = new Set<Id>();
        // Id nodig van Cup
        Set<Id> cupIdSet = new Set<Id>();
        //Id nodig van Tour
        Set<Id> tourIdSet = new Set<Id>();
        // Id nodig van Driver
        Set<Id> driverIdSet = new Set<Id>();
        Map<Id, Driver_Course__c> driverCourseById = new Map<Id, Driver_Course__c>();


        // Add the Ids to the sets
        for(Calculation__c calc : calcList){
            courseIdSet.add(calc.Course__c);
            cupIdSet.add(calc.Cup__c);
            tourIdSet.add(calc.Tour__c);
            driverIdSet.add(calc.Driver__c);
        }

        // Query  ids met junction object Driver Course

        for(Driver_Course__c driverCourse : [SELECT Id, Driver__c, Course__c, Course__r.Cup__c, Course__r.Cup__r.Tour__c 
                                               FROM Driver_Course__c 
                                              WHERE Course__c IN :courseIdSet
                                                AND Course__r.Cup__c IN :cupIdSet
                                                AND Course__r.Cup__r.Tour__c IN :tourIdSet
                                                AND Driver__c IN :driverIdSet]){
            driverCourseIdByTourId.put(driverCourse.Id, driverCourse); // is this smart?

        }
        
        for(Calculation__c calc : calcList){

            //??????

        }

        


    }
}


Any help would be much appreciated. 
 

Thanks

Wouter FrankhuizenWouter Frankhuizen
This is the nested for loop code which gives a CPU Limit Exceeds error in my test class. How to optimize it and get rid of those nested for loops? 
public without sharing class AutoFillCalculations {
    public static void FillOut(List<Calculation__c> calcList) {

        // Id nodig van Course
        Set<Id> courseIdSet = new Set<Id>();
        // Id nodig van Cup
        Set<Id> cupIdSet = new Set<Id>();
        //Id nodig van Tour
        Set<Id> tourIdSet = new Set<Id>();
        // Id nodig van Driver
        Set<Id> driverIdSet = new Set<Id>();
        Map<Id, Driver_Course__c> driverCourseById = new Map<Id, Driver_Course__c>();


        // Add the Ids to the sets
        for(Calculation__c calc : calcList){
            courseIdSet.add(calc.Course__c);
            cupIdSet.add(calc.Cup__c);
            tourIdSet.add(calc.Tour__c);
            driverIdSet.add(calc.Driver__c);
 
        }

        // Query  ids met junction object Driver Course

        for(Driver_Course__c driverCourse : [SELECT Id, Driver__c, Course__c, Course__r.Cup__c, Course__r.Cup__r.Tour__c 
                                               FROM Driver_Course__c 
                                              WHERE Course__c IN :courseIdSet
                                                AND Course__r.Cup__c IN :cupIdSet
                                                AND Course__r.Cup__r.Tour__c IN :tourIdSet
                                                AND Driver__c IN :driverIdSet]){
            
            for(Calculation__c calc : calcList){

                If(driverCourse.Course__c == calc.Course__c &&
                driverCourse.Course__r.Cup__c == calc.Cup__c &&
                driverCourse.Course__r.Cup__r.Tour__c == calc.Tour__c &&
                driverCourse.Driver__c == calc.Driver__c){

                    calc.Driver_Course__c = driverCourse.Id;
                    //calcUpdateList.add(calc);
                }

            }

        }
    }
}

 
Alain CabonAlain Cabon
Salesforce offers few options for the profiling and solutions with SOQL requests.

1) indexing of the fields by defining the fields as External Ids for example (simple option but useless here).
2) asynchronous treatment instead of synchronous treatment (longer but also limited @Future annotation  https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_annotation_future.htm  ).
3) Multiple smaller calls using scopes, by ranges of ids and a batch treatment (launched automatically every hour for example or directly manually)
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm

4) Query Plan: 
SELECT Id, Driver__c, Course__c, Course__r.Cup__c, Course__r.Cup__r.Tour__c 
                                               FROM Driver_Course__c 
                                              WHERE Course__c IN :courseIdSet
                                                AND Course__r.Cup__c IN :cupIdSet
                                                AND Course__r.Cup__r.Tour__c IN :tourIdSet
                                                AND Driver__c IN :driverIdSet]

The query above lasts too much time but you use only Ids here so they are all indexed by default (option n°1).

You can show the query plan of your SOQL reqest with the developer console.  

Developer Console Query Plan Tool FAQ
http://​​​​​​​https://help.salesforce.com/articleView?id=000334796&type=1&mode=1 (http://https://help.salesforce.com/articleView?id=000334796&type=1&mode=1)

Enable and Use the Query Plan Tool
  1. In the Developer Console, click Help | Preferences
  2. Set 'Enable Query Plan' to TRUE.
Once enabled in the Developer Console, you can access the Query Plan Tool in the 'Query Editor' tab of the console.
To use the Query Plan Tool, simply enter a query in the Query Editor and press Query Plan to displays all query operations and the cost of each for the SOQL provided.
 
Alain CabonAlain Cabon
Developer Console Query Plan Tool FAQ

https://help.salesforce.com/articleView?id=000334796&type=1&mode=1 (https://help.salesforce.com/articleView?id=000334796&type=1&mode=1)