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
Lee SinLee Sin 

Avoid Too Many Query Rows

Can anyone teach me how to fix this query?

List<Account> advisors=[select id,client_territories__c,name from Account where ......];
for(Account a : advisors)
{ 
      List<Account> a1=[select id from Account where client_territories__c=:a.client_territories__c];
      ..........
      ........

}

I want to avoid wrting queries in For Loop.
So I want to fix the query this way:
List<Account> advisors=[select id,client_territories__c,name,[select id from Account where client_territories__c=?????] from Account where ......];

I'm stucked here.
 

Best Answer chosen by Lee Sin
Hargobind_SinghHargobind_Singh
You could use a Collection and use that collection in your next query like:

Set<String> territorySet = new Set<String>(); 

List<Account> advisors=[select id,client_territories__c,name from Account where ......];
for(Account a : advisors)
{
      territorySet.add(a.client_territories__c);
      ..........
      ........
}

List<Account> a1=[select id from Account where client_territories__c in :territorySet];

All Answers

Hargobind_SinghHargobind_Singh
You could use a Collection and use that collection in your next query like:

Set<String> territorySet = new Set<String>(); 

List<Account> advisors=[select id,client_territories__c,name from Account where ......];
for(Account a : advisors)
{
      territorySet.add(a.client_territories__c);
      ..........
      ........
}

List<Account> a1=[select id from Account where client_territories__c in :territorySet];

This was selected as the best answer
Lee SinLee Sin
Hargobind, your code does different thing than mine. I need to process Account records in each client territory. You put all Account records int one List.
What I want to do is similar to this: https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops

But the example above does not have a where clause in the nested select statement.
How can I add a where clause?
Hargobind_SinghHargobind_Singh
Hi Jack,

You would need to take the query out of the loop for sure, and the only way to do it is to collect all conditions and take out records at one time (my last query). 

I have just stated an example, but you need to add your own where condition to this query. And, I am assuming that the where condition is common and is not changing with each iteration of advisors loop.

Once you have the records from second query, you would then need to run another loop and then process the accounts as per your business rules. 

This could be the logic:
  1. Query on Accounts, collect in Advisors collection based on your business logic
  2. Loop on advisors and collect all information you need, to run another query. 
  3. Run one or more queries (Depends on your business logic) on accounts based on collection in step 2
  4. Run loop on the new collection and execute your business logic.

I'm afraid without knowing the business logic, I wont' be able to give you the exact solution, but hope this helps. 
Lee SinLee Sin
@Hargobind_Singh, I got you!  You are saying I need to use the "where in" clause to replace the query in for loop.

So this can help me avoid the SOQL query limit.   

Can I ask one more thing?   Once I used the where in cluse, I got another error which is the too many rows 50001.
Does this mean I have to use batch apex?  
But I checked batch apex and it is mostly used for DML operations.
If I hit the rows limit in my query can I also use batch apex? 
Hargobind_SinghHargobind_Singh
Hi Jack, yes, if you are crossing 50,000 limit, then batch apex would be a solution.

The reason is, that Salesforce has a hard limit of 50,000 records per Apex Transaction, but for batch apex, the limit is reset after every execute method (unless you are using stateful). Usually any transaction in which you are processing more than 50k records can bedone in batch transaction