0

Hello) I need to create a trigger that will record the number of reviews and the average rating from custom object (Review__c) in the corresponding fields on other custom object (Job_Applications__c). The trigger works, but throws an exception when testing to insert a large number of records (LimitException: Too many SOQL queries: 101). I understand this is because of my query in a loop. How can I pull SOQL-query out of the loop?

trigger ReviewSetRatingTrigger on Review__c (after insert, after update) {

List<Job_Application__c> updList = new List<Job_Application__c>();

List<Review__c> newList = new List<Review__c>([
    SELECT Id, Rate__c, Job_Application__r.Id, Job_Application__r.Reviews_Count__c, Job_Application__r.Average_Rate__c
    FROM Review__c
    WHERE Id IN : Trigger.new
]);

for(Review__c rev : newList) {
    List<Review__c> tempList = new List<Review__c>([ //Find reviews count and their summ
        SELECT Rate__c
        FROM Review__c 
        WHERE Job_Application__r.Id =: rev.Job_Application__r.Id
    ]);
    Integer revCount = 0;
    Decimal rateSum = 0;
    for(Review__c upd : tempList) {
        revCount++;
        rateSum += upd.Rate__c;
    }

    if(rev.Job_Application__r.Average_Rate__c == null) {
        rev.Job_Application__r.Average_Rate__c = rev.Rate__c;
        rev.Job_Application__r.Reviews_Count__c = 1;
    } else {
        rev.Job_Application__r.Average_Rate__c = rateSum / revCount;
        rev.Job_Application__r.Reviews_Count__c = (Trigger.isInsert)? rev.Job_Application__r.Reviews_Count__c + 1 : tempList.size();
    }    
    updList.add(rev.Job_Application__r); 
    System.debug(rev.Job_Application__r);
} 

update updList;

}

0
0

Instead of querying inside for loop add all the Job_Application__r.Id in a set of Id and then fetch all the Review__c records where Job_Application__r.Id is IN set of Id

Below is the code example:

set<Id>  jobApplicationIds=new set<Id> ();
for(Review__c rev : newList) {
jobApplicationIds.add(rev.Job_Application__r.Id);
}
List<Review__c> tempList = new List<Review__c>([ //Find reviews count and their summ
        SELECT Rate__c
        FROM Review__c 
        WHERE Job_Application__r.Id IN : jobApplicationIds
    ]);
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.