Preface: this post is part of the Bulkify Your Code series.
Best advice I’ll ever give you: never ever have a SOQL query inside a loop!
Having a SOQL query inside a loop is a major breach of the unwritten code of Apex developers. It’s like wearing socks and sandals at the same time. Don’t ever wear socks with sandals, trust me on this one!
Why can’t we have SOQL queries inside loops?
Simple. The #1 most common governor limit restricts you to 100 SOQL queries. Loops, especially those iterating over Trigger.new, will easily run 200+ queries and push you across the limit.
How do we avoid using SOQL queries inside loops?
Combine your queries into one! There’s always a way to do this for any trigger you write!
Let’s look at this chapter’s trigger, which can potentially run 200 SOQL queries, and combine them.
How to combine SOQL queries:
Now we apply the above steps to this chapter’s trigger:
trigger FixCreator on Case (before insert) { // Step 1: Create a set of all values to querySet<String> allEmails = new Set<String>();for (Case newCase : Trigger.new) { if (newCase.SuppliedEmail != null) {allEmails.add(newCase.SuppliedEmail);} } // Step 2: Query for all the records in Step 1 List<User> potentialUsers = [SELECT Id, Email FROM UserWHERE Email IN :allEmails];// Step 3: Associate each record with the correct query result for (Case newCase : Trigger.new) { // We'll cover this in the next post! } }
Now that we’ve combined our queries into one, we no longer have to worry about hitting the 100 SOQL query governor limit.
We have a new problem now however. Before combined queries, it was easy associating the correct Case creator to each Case because we simply did a brand new SOQL query and took a single result. Now however with combined queries, we have to choose the correct Case creator for each Case out of our combined list of many potential creators!
We’ll solve this dilemma and close the book on this chapter in the next post!
Next post: Use Maps to navigate across Lists!
Hi david
I wrote the following code in a method.
public static void Updateproductprice(CPK_Price_Floor__c[] prfloor){
Set targetprice = new Set();
Set floorprice = new Set();
Set product = new Set();
for (CPK_Price_Floor__c newprice: prfloor){
if (newprice.CPK_Product__c != null){
product.add(newprice.CPK_Product__c);
targetprice.add(newprice.CPK_Target_Price__c);
floorprice.add(newprice.CPK_Floor_Price__c);
}
}
List prodmaster = [select ID, CPK_Target_Price__c,CPK_Floor_Price__c from Product2 where Id IN: product];
for (Product2 prodrecord :prodmaster){
prodrecord.CPK_Target_Price__c = decimal.valueOf(targetprice);
prodrecord.CPK_Floor_Price__c = decimal.valueof(floorprice);
}
update prodmaster;
}
Issue happens on the following 2 lines
prodrecord.CPK_Target_Price__c = decimal.valueOf(targetprice);
prodrecord.CPK_Floor_Price__c = decimal.valueof(floorprice);
}
I get the message:
Method does not exist or incorrect signature: void valueOf(Set) from the type Decimal
I tried (Decimal)(Targetproce); Integer.valueof (Targetprice) gets rid of the compilation error but gives me an execution error.
What am I doing wrong?
Hi David,
Thanks a lot for the great posts. It’s easy to understand…
Thanks!!
Hey David!
First of all nice explanation about bulkify.
I have one question to ask Why you’ve created Set in the Step-1 and not List. Is it to prevent duplicate email values?
If not then What if we use List over Set in Step-1?
Thanks!
You’re exactly right – using Sets you don’t need to worry about dupes. No point over-engineering!
In the first piece of the code
// Step 1: Create a set of all values to query
Set allEmails = new Set();
for (Case newCase : Trigger.new) {
if (newCase.SuppliedEmail != null) {
allEmails.add(newCase.SuppliedEmail);
}
}
.. does this handle duplicate emails? What happens when you add duplicate keys to the Set? Would it error out?
It will just simply not add that value to the set(i.e if it is a duplicate)
Doubt cleared after reading the next post. Its like the missing piece to the puzzle. It all fits so well ,.. Haha
Thanks again.
Sandeep
Woohooo!!!!!! Great job learning Sandeep!
Hi David,
Thanks again for all your help.
This might be a stupid doubt, but I asking anyway
Could you please explain this part in detail” it was easy associating the correct Case creator to each Case because we simply did a brand new SOQL query and took a single result”.
In such a case how would the SOQL query be as opposed to the combined one?
To make this doubt more clear, I am not clearly able to understand when you say queries have to be combined into one.
Does this mean every Trigger has to have only one master SOQL query as opposed to many small soql queries.
Thanks,
Sandeep
Hi David
I’m new to Apex and I have found your site very helpful but currently I am finding myself in a serious bind with SOQL left outer join ..
I have two custom objects with a Parent-child relationship, name of the objects.. Applicant__c (Parent), Application_Review__c (Child).
SELECT Applicant__c.Id, Applicant__c.Name, (SELECT Application_Review__c.Subject__c, Application_Review__c.Final_Score__c FROM Applicant__c.Application_Review__c ) FROM Applicant__c
The FROM statement of the inner query throws a “Didn’t understand relationship” error, even if i write it like this Applicant__r.Application_Review__c.
What’s more confusing is that if I run this query : SELECT Account.Name, (SELECT Contact.FirstName, Contact.LastName FROM Account.Contacts) FROM Account. It runs fine and I got the idea from it.
the end result basically is to have a VF page which displays a list of applicants and have their final score of a particular subject entered. but right now I can’t even get the query working.
NB: the result scores will be updated or inserted on the Application_Review__c object.
Thanks for taking your and going through my problem.
Hahaha, I know the feeling!
You can find the exact syntax by referencing the picture here:
https://www.sfdc99.com/2013/06/24/example-how-to-write-a-cross-object-soql-query-part-2/
It’s probably Application_Review__r.
You can also use a tool called Workbench as well!
https://workbench.developerforce.com/login.php
David
Hi David
Sorry for such a late reply, I had a looong easter weekend. I did go through the link you sent which was very helpful, it turns out that the child relationship name is named application_assessments__r which was the original name of the application_review__c object before it got changed.
but now the issue is trying to display the inner select field on the VF page table i keep getting the following error.
Save error: Could not resolve the entity from value binding ‘{!lstApp.Applicant_Assessments__r.Review_3_Total_Score__c}’. can only be used with SObjects, or objects that are Visualforce field component resolvable.
Name displays pretty fine but not the score,
One last question, one the requirements is to display picklist which will contain subject how do i dynamically populate the selectOption using the same query as above or the best way to approach it? I have seen examples on how to do it but those mainly focus on just the getting the picklist values none of the queries are anything like mine.
in a query like mine what’s the best way of doing that, please note that the values will be queried using the inner select.
I will continue to try and find a solution to my problems while I wait for your reply
Thanks :-)
lstApp.Applicant_Assessments__r refers to a list of records, while you are trying to access just one of them!
Two options:
1. Change to lstApp.Applicant_Assessments__r[0].Review_3_Total_Score__c for just the first record OR
2. Use apex:repeat or a similar table to have a row for each record!
You can use apex:repeat to do the selectOption thing as well =)
Go get em!!!
David
So I tried your suggestion, worked with displaying…. but boy did I struggle with the save method but I just finished it and everything works like a dream. thanks to you and the info you shared with us on this site. :-)
BE WARNED: I WILL BE BOTHERING YOU AGAIN IN THE NEAR FUTURE LOL!!!
ON TO THE NEXT VF PAGE NOW!!!
woohoo great job!!!
your next post clarifies my question. In the next post (https://www.sfdc99.com/2014/01/25/use-maps-navigate-across-lists/) you say “Your big query happens outside of any loops”
^_^
Hey David,
You say “Always do this before entering the loop!”. I’m caught up on the word ‘before’ in that sentence. Could it have read “before or after” instead of “before”? Additional context here https://gust.box.com/s/b7es13wr00ab8aalj8ul
hii,David Liu sir dis is ajay kumar,i have some doubts in triggers,in which senario i wil use before and after,in before trigger also you used in one example trigger.new,please explain me or mail me entire trigger notes……..
Hey Ajay,
Have you checked out this post yet? I bet it’ll help!
https://www.sfdc99.com/2014/01/25/use-vs-triggers/
David
Hey David,
How would one go about this if they wanted to specify two filter criteria in their SOQL query? My understanding is that sets only contain one element. Could you use maps in place of sets?
This post goes pretty crazy with multiple filter criteria – let me know if this answers your question!
https://www.sfdc99.com/2013/06/07/example-how-to-write-a-simple-soql-query/
Sets can have infinite elements, as long as each one is unique! IE if you have a set of email addresses, each email address can only appear once!
Sorry, I should have been more clear.
Let’s say I want to follow the same process described but with two filter criteria (for example, if I want my SOQL query to be based on email AND last name). I first have to create a set from the records that are being inserted, but a set won’t contain paired information, will it?
How can I go about running the more advanced SOQL query using information from the inserted records?
>< sorry for my poor use of HTML
The html should read that I want to follow the process in this post: https://www.sfdc99.com/2014/01/25/use-maps-navigate-across-lists/
The same process you use to “bulkify your code,” only this time we’re collecting two pieces of information from the new records, and using them in an SOQL query.
Try something like this!
List<Contact> contacts = [SELECT Id FROM Contact WHERE Email IN :emailSet AND LastName IN :nameSet];
As usual another terrific and well explained post. This blog is well on its way to becoming the defacto beginners guide to learning how to write SFDC APEX. Thank you David.
My pleasure Andrew, loving it!
Thanks for the tutorials, they are awesome for a salesforce-but-not-programming newbie such as myself. Do you have a timeframe of when certain chapters will be done? I guess I got a little bit spoiled since I started chapter 1 this morning and now I’m already ready for the next part of this chapter!
You’re making great progress Malachi!!!
I try very hard to get 1 – 2 posts out a week, too slow for a speedster like you unfortunately!!!
Here are two tasks for you in the meantime =)
1. Write a trigger that automatically changes a Lead status to “In Progress” when the first task is created on it
2. Figure out how to use Maps to finish chapter 5!
Good luck =)
David
Hi David,
how do I know that it is the ‘first task’ on the lead…
Tried to think about it and come up with something like below
List tasks = [Select count() from Task where CreatedDate = TODAY]
I am sure this is incorrect and there should be a simpler answer.
Thank you for your help, Sinan
Order it by CreatedDate =)
Then take the first one in the list ie tasks.get(0);
Hey Devid !!
I am became your fan after reading your posts on this site…. gr8 technique to explain the topics… waiting for next page.
Thanks alot
My pleasure Anurag!