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
Huy NguyenHuy Nguyen 

SOQL limit

Hi expert,

I have trigger below and it got soql exception. I just join project few days so I do not understand the logic of the flow. Can any one advise to improve the code. I see SOQL in for loop:
     if (Trigger.isInsert) {
                
                LineItemFormController.isInsertTrigger = true;
                for(Line_Item__c item :Trigger.New){
                if ((lineItemRecordType != '') && (item.Cloned_Line_Item__c == null) && (item.RecordTypeId == lineItemRecordType)) {
                    lineItemIds.add(item.Id);
                    if (lineItemMaps.get(item.Id) == null) {
                        lineItemMaps.put(item.Id, new List<Line_Item__c>());
                    }
                    }
                }
                
                List<Line_Item__c> oldAuditLineItems = [SELECT Id,Line_Item__c, Active__c, Line_Item_Status__c
                                                    FROM Line_Item__c
                                                    WHERE RecordTypeId =:auditRecordType
                                                    AND Active__c = true
                                                    //US5105
                                                    AND Line_Item_Status__c != 'Audited'
                                                    AND Line_Item__c IN :lineItemIds
                                                   ];
                for (Line_Item__c auditItem : oldAuditLineItems) {
                    lineItemMaps.get(auditItem.Line_Item__c).add(auditItem);
                }
                System.Debug('nghiatran: Insert ' + isUpdate);
                System.Debug('vinhvinh: Come to Insert Audit trigger');
                for(Line_Item__c item :Trigger.New){
                    Line_Item_Type__c lineItemType  = [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name FROM Line_Item_Type__c where id =: item.Line_Item_Type__c limit 1];
                    if ((item.Cloned_Line_Item__c == null) && (item.RecordTypeId == lineItemRecordType)) {
                        // US6137 insert start
                        system.debug('nghiatran lineItemType.Line_Item_Type_Aggregated_Category__c ' + lineItemType.Line_Item_Type_Aggregated_Category__c);
                        if(lineItemType.Line_Item_Type_Aggregated_Category__c == 'Electoral Expenditure' ||
                            lineItemType.Line_Item_Type_Aggregated_Category__c == 'Small Donations Made' ||
                            lineItemType.Line_Item_Type_Aggregated_Category__c == 'Small Donations Received')
                            return;
                        // US6137 insert end
                       if (lineItemMaps.get(item.Id).size() == 0 ) {
                           system.debug('nghiatran access insert after ' + lineItemType.Line_Item_Type_Aggregated_Category__c);
                            Line_Item__c temp = item.Clone(false,true);
                            temp.Line_Item__c = item.Id;
                            temp.Line_Item_Type__c = item.Line_Item_Type__c;
                            temp.RecordTypeId = auditRecordType;
                            temp.Active__c = true;
                            temp.Line_Item_Status__c = 'Not Audited';
                            newAuditLineItems.add(temp);
                        }
                    }
                }
            }
Best Answer chosen by Huy Nguyen
PuneetsfdcPuneetsfdc
yes its giving an error as we are querying on Line_Item_Type__c using the Id of Line_Item__c

for that we need to have a set/list of Id of Line_Item_Type__c which we will get from Line_Item__c
created a set<Id> storing the Id values of Line_Item_Type__c and using that set<Id> to query Line_Item_Type__c

just update the above code with below code and see if it works. 

/* ADD THIS CODE */
Set<Id>  lineItemTypeIds = new Set<Id>();
for(Line_Item__c item : trigger.new) {
	lineItemIds.add(item.Line_Item_Type__c);
}

Map<String, Line_Item_Type__c> newLineItemMap = new Map<Id, Line_Item_Type__c>();
for(Line_Item_Type__c lineItem : [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name FROM Line_Item_Type__c where id IN: lineItemIds]) {
    newLineItemMap.put(lineItem.Line_Item_Type_Aggregated_Category__c, lineItem);
}

 

All Answers

sandeep sankhlasandeep sankhla
Hi Huy,

As a best practise we should never use SOQL inside for loop..

Please check your code

 for(Line_Item__c item :Trigger.New){
                    Line_Item_Type__c lineItemType  = [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name FROM Line_Item_Type__c where id =: item.Line_Item_Type__c limit 1];

In this if Trigger.New will have 200 records then it will hit the SOQL limit and cause the exception..

You can make use of map and set to optimize your code..

P.S. If my answer helps you to solve your problem please mark it as best answer. It will help other to find best answer.

Thanks,
Sandeep
Salesforce Certified Developer 
Huy NguyenHuy Nguyen
Hi Sandeep,

Yes . I know it is a problem Could you advise me how to fix that code
PuneetsfdcPuneetsfdc
try below code, I have commented the SOQL inside for and created a map that will be used to check Line_Item_Type_Aggregated_Category__c 

look at the code the lines I have added and commented


if (Trigger.isInsert) {
                
                LineItemFormController.isInsertTrigger = true;
                for(Line_Item__c item :Trigger.New){
                if ((lineItemRecordType != '') && (item.Cloned_Line_Item__c == null) && (item.RecordTypeId == lineItemRecordType)) {
                    lineItemIds.add(item.Id);
                    if (lineItemMaps.get(item.Id) == null) {
                        lineItemMaps.put(item.Id, new List<Line_Item__c>());
                    }
                    }
                }
                
                List<Line_Item__c> oldAuditLineItems = [SELECT Id,Line_Item__c, Active__c, Line_Item_Status__c
                                                    FROM Line_Item__c
                                                    WHERE RecordTypeId =:auditRecordType
                                                    AND Active__c = true
                                                    //US5105
                                                    AND Line_Item_Status__c != 'Audited'
                                                    AND Line_Item__c IN :lineItemIds
                                                   ];
                for (Line_Item__c auditItem : oldAuditLineItems) {
                    lineItemMaps.get(auditItem.Line_Item__c).add(auditItem);
                }
                System.Debug('nghiatran: Insert ' + isUpdate);
                System.Debug('vinhvinh: Come to Insert Audit trigger');
                
                /* ADD THIS CODE */
                Map<String, Line_Item_Type__c> newLineItemMap = new Map<Id, Line_Item_Type__c>();
                for(Line_Item_Type__c lineItem : [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name FROM                                                                     Line_Item_Type__c where id IN: trigger.new]) {
                    newLineItemMap.put(lineItem.Line_Item_Type_Aggregated_Category__c, lineItem);
                }
                
                for(Line_Item__c item :Trigger.New){

                    /*Line_Item_Type__c lineItemType  = [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name                                                                                FROM Line_Item_Type__c where id =: item.Line_Item_Type__c limit 1];*/
                   
                       if ((item.Cloned_Line_Item__c == null) && (item.RecordTypeId == lineItemRecordType)) {
                       
                     // US6137 insert start
                    /*    system.debug('nghiatran lineItemType.Line_Item_Type_Aggregated_Category__c ' +                                                                                                                                                                          lineItemType.Line_Item_Type_Aggregated_Category__c);*/
                    /*  if(lineItemType.Line_Item_Type_Aggregated_Category__c == 'Electoral Expenditure' ||
                            lineItemType.Line_Item_Type_Aggregated_Category__c == 'Small Donations Made' ||
                            lineItemType.Line_Item_Type_Aggregated_Category__c == 'Small Donations Received')
                            return;*/
                   
                      if( newLineItemMap.containsKey('Electoral Expenditure') || newLineItemMap.containsKey('Small Donations Made') ||
                            newLineItemMap.containsKey('Small Donations Received') )
                            return;
                       
                       // US6137 insert end
                       if (lineItemMaps.get(item.Id).size() == 0 ) {
                        /*   system.debug('nghiatran access insert after ' + lineItemType.Line_Item_Type_Aggregated_Category__c);*/
                            Line_Item__c temp = item.Clone(false,true);
                            temp.Line_Item__c = item.Id;
                            temp.Line_Item_Type__c = item.Line_Item_Type__c;
                            temp.RecordTypeId = auditRecordType;
                            temp.Active__c = true;
                            temp.Line_Item_Status__c = 'Not Audited';
                            newAuditLineItems.add(temp);
                        }
                    }
                }
            }





 If my answer helps you to solve your problem please mark it as best answer. It will help other to find best answer.

 
Huy NguyenHuy Nguyen
Hi Puneetsfdc

It thrown error in this line. can you check ? Map<String, Line_Item_Type__c> newLineItemMap = new Map<Id, Line_Item_Type__c>();
PuneetsfdcPuneetsfdc
Change it to 

Map<String, Line_Item_Type__c> newLineItemMap = new Map<String, Line_Item_Type__c>();
 
Huy NguyenHuy Nguyen
Hi ,

I got error in where clause. Please help to check
Huy NguyenHuy Nguyen
Sorry my bad I got the error: Invalid bind expression type of Line_Item__c for Id field of SObject Line_Item_Type__c
PuneetsfdcPuneetsfdc
yes its giving an error as we are querying on Line_Item_Type__c using the Id of Line_Item__c

for that we need to have a set/list of Id of Line_Item_Type__c which we will get from Line_Item__c
created a set<Id> storing the Id values of Line_Item_Type__c and using that set<Id> to query Line_Item_Type__c

just update the above code with below code and see if it works. 

/* ADD THIS CODE */
Set<Id>  lineItemTypeIds = new Set<Id>();
for(Line_Item__c item : trigger.new) {
	lineItemIds.add(item.Line_Item_Type__c);
}

Map<String, Line_Item_Type__c> newLineItemMap = new Map<Id, Line_Item_Type__c>();
for(Line_Item_Type__c lineItem : [SELECT Id,Line_Item_Category_Text__c,Line_Item_Type_Aggregated_Category__c,Name FROM Line_Item_Type__c where id IN: lineItemIds]) {
    newLineItemMap.put(lineItem.Line_Item_Type_Aggregated_Category__c, lineItem);
}

 
This was selected as the best answer
Huy NguyenHuy Nguyen
Thank a lot