Apex Code Development You need to sign in to do that
Don't have an account?
Chris760 Nested "For" Loops
Since programming in APEX, I've learned to use maps (as opposed to nested FOR loops) on pretty much everything. But there's one scenario where I've never been able to figure out a "map" based solution and I wanted to get feedback from some of you more experience gurus about how you would approcah the following problem and see if I'm going about this situation in totally the wrong way.
So the problem occurs when I'm dealing with multiple *dynamic* filters in a given query. Here's a real life example I'm dealing with right now...
I'm currently working on an insurance companies system where every time a doctor authorizes a patient to receive treatment but *doesn't* fax in the patents records along with the authorization, I need to query the database to look for past medical records for that patient where the patient ID (on the faxed authorization) matches the patient ID in the database, and the doctor ID on the authorization, matches the doctor ID in the database, and the number of times medicated per day, is a number that's greater than or equal to the number of times medicated on the patient record in the database.
In other words, unlike most situations where you'd just say "[select ID, Patient__c, Doctor__c, Times_Treated_Daily__c from Records__c where Patient__c IN: patientsWithRecords.keySet()]", the filters in this case are multiple, and different for every record being queried. So (and PLEASE correct me if I'm wrong) if I wrote "select Id, Patient__c, Doctor__c, Times_Treated_Daily__c from Records__c where Patient__c IN: patientsWithRecords.keySet() and Doctor__c IN: doctorsWithRecords.keySet() and Times_Treated_Daily__c IN: (or >= ?) timesTreatedDaily.keySet()" (essentially doing a FOR loop to extract my filters into individual keysets ahead of time), the resulting query would be useless because a patient could have multiple matching doctors, a doctor can have multiple matching patients, and the times treated could be grater or less than anyone in the keyset. So simply throwing the 3 filters that I need to query against (Patient, Doctor, and Times Treated Daily) into 3 maps/keysets and querying against it, won't necessarily return records that I could bind with because the "IN:" part of the query means that records could easily "mix and match" with a variety of doctors, patients, and times treated daily.
As a result, I'll make the 3 keySets, but then I'll still iterate through the records to do the final matching since I feel like there's no way to know if record X is actually the patient, doctor and times treated daily for the specific record I'm querying for. Am I going about this in a totally wrong way and there's actually a way simpler way to do this? Or do maps automatically somehow compensate for this when you map multiple variables? Or is there a way to write your SOQL in order to account for multiple variables like that? Or are nested FOR loops basically the only way?
I'm so curious to know how other people would tackle this problem. Any ideas or experiences with it would be hugely apprecited. Thanks!
In general, loops inside of loops, especially nested more than once, can be catastrophic for your performance. For example, let's say you have three loops nested together, and they each count from 1 to 100.
for(Integer a = 1; a <= 100; a++) { for(Integer b = 1; b <= 100; b++) { for(Integer c = 1; c <= 100; c++) { System.debug(a * b * c); } } }This code seems like it couldn't be that bad, but when you do the math, you find that this code requires 1,000,000 lines of script execution on the debug statement. This simply isn't practical. With the new governor limits, this code might work assuming the innermost loop only had a light work load (less than 3 simple statements with no function calls). Furthermore, given the same simple scenario where there are 100 possible filters in the A, B, and C filters in a query, this is very likely to exceed governor limits.
In order to avoid this, we need an accurate query that doesn't return data we're never going to use. Of course, as you've found, it's not easy to write this sort of query in "static soql", because you can't control the entire statement. What you're actually looking for is "dynamic soql", which trades compile-time checks for run-time flexibility. To achieve this, you can use a simple string and join everything together. Here's an approach that I use in my own code:
Set<String> filters = new Set<String>(); for(... somerecord:...) { filters.add(String.format('(Patient__c = \'\'{0}\'\' AND Doctor__c = \'\'{1}\'\' AND Times_Treated_Daily__c >= {2})', new String[] { someRecord.Patient__c, somerecord.Doctor__c, String.valueOf(somerecord.Times_Treated_Daily__c })); }Then, you can build your query:
String query = string.format('select ... from record__c where {0}', new string[] { string.join(new list<string>(filters), ' OR ' )});Execute the query with Database.query.
What happens in this code is that we generate individual clauses:
And we string them together with ORs, and put them into a query:
SELECT ... FROM Record__c WHERE (Patient__c = '12345' AND Doctor__c = '98765' AND Times_Treated_Daily__c > 5) OR (Patient__c = '23456' AND Doctor__c = '87654' AND Times_Treated_Daily__c > 3)This greatly increases the complexity of the SOQL, but greatly reduces the number of records that would be returned, since each record should be a relevant match (but you may need to consider that there may be different Times Treated Daily values), thus effectively stripping you down to a single loop with a couple of if statements.
You query the data using the database.query method:
As another note, you'll still want to consider using maps to locate your data, as this is far more efficient than normal for loops:
This will make it easier to find the records than simply looping through all of them each pass.
All Answers
In general, loops inside of loops, especially nested more than once, can be catastrophic for your performance. For example, let's say you have three loops nested together, and they each count from 1 to 100.
for(Integer a = 1; a <= 100; a++) { for(Integer b = 1; b <= 100; b++) { for(Integer c = 1; c <= 100; c++) { System.debug(a * b * c); } } }This code seems like it couldn't be that bad, but when you do the math, you find that this code requires 1,000,000 lines of script execution on the debug statement. This simply isn't practical. With the new governor limits, this code might work assuming the innermost loop only had a light work load (less than 3 simple statements with no function calls). Furthermore, given the same simple scenario where there are 100 possible filters in the A, B, and C filters in a query, this is very likely to exceed governor limits.
In order to avoid this, we need an accurate query that doesn't return data we're never going to use. Of course, as you've found, it's not easy to write this sort of query in "static soql", because you can't control the entire statement. What you're actually looking for is "dynamic soql", which trades compile-time checks for run-time flexibility. To achieve this, you can use a simple string and join everything together. Here's an approach that I use in my own code:
Set<String> filters = new Set<String>(); for(... somerecord:...) { filters.add(String.format('(Patient__c = \'\'{0}\'\' AND Doctor__c = \'\'{1}\'\' AND Times_Treated_Daily__c >= {2})', new String[] { someRecord.Patient__c, somerecord.Doctor__c, String.valueOf(somerecord.Times_Treated_Daily__c })); }Then, you can build your query:
String query = string.format('select ... from record__c where {0}', new string[] { string.join(new list<string>(filters), ' OR ' )});Execute the query with Database.query.
What happens in this code is that we generate individual clauses:
And we string them together with ORs, and put them into a query:
SELECT ... FROM Record__c WHERE (Patient__c = '12345' AND Doctor__c = '98765' AND Times_Treated_Daily__c > 5) OR (Patient__c = '23456' AND Doctor__c = '87654' AND Times_Treated_Daily__c > 3)This greatly increases the complexity of the SOQL, but greatly reduces the number of records that would be returned, since each record should be a relevant match (but you may need to consider that there may be different Times Treated Daily values), thus effectively stripping you down to a single loop with a couple of if statements.
You query the data using the database.query method:
As another note, you'll still want to consider using maps to locate your data, as this is far more efficient than normal for loops:
This will make it easier to find the records than simply looping through all of them each pass.
Thanks sfdcfox! That's brilliant!!! As always, you've got excellent suggestions. I totally get what you're saying and how to pull it off, it just never once occurred to me to do it using dynamic SOQL in that way until you just now explained it (I've always thought of dynamic SOQL in its more primitive usage, like "IN:" and stuff that's more static). Now that you've laid it out though, it seems SO obvious.
Anyway, thanks again!! =D