+ Start a Discussion
nishanth0208nishanth0208 

How to avoid the query in for loop?

Hi All,

In the code pasted below, i need to avoid the query in the for loop. My Intention is to pick up one record per id(per each UniqueId). To achieve this i have to place the query in a for loop which will hit the governer limits(max SOQL query 100) if number of accounts are more. So, is there a way of getting one record (already mentioned in query as LIMIT 1) per account id without looping through each account id(eachUniqueId).

Please help me out.

 

Thanks in advance,

Nishanth.

 

Here goes my code:

------------------

List<Account> updatableAccountList = new List<Account>();

for(Id eachUniqueId:uniqueIdList){

List<Task> tasksPerId = [Select WhatId,Status,ActivityDate from Task where ActivityDate>today and WhatId =: eachUniqueId and Status != 'Completed' ORDER BY ActivityDate ASC NULLS last LIMIT 1];

Account acc = new Account(Id=tasksPerId[0].WhatId);

           acc.whs_task_Status__c = tasksPerId[0].Status;

acc.whs_upcomingTask__c = tasksPerId[0].ActivityDate;

updatableAccountList.add(acc);

}

update updatableAccountList;

------------------


Best Answer chosen by Admin (Salesforce Developers) 
nishanth0208nishanth0208

Thanks for your help Homer!

Actually my requirement is to get the Latest upcoming task record(based on ActivityDate field)   for each account and want to capture that activity field value and need to place in one of the date field in that account. 

I may not get some thing like that if i do like above. 

I achieved this requirement in the following way(eliminating duplicates and taking one latest task record for each account and updating):

 

List<Task> tasksPerId = [Select WhatId,Status,ActivityDate from Task where ActivityDate>today and WhatId in: uniqueIdList and Status != 'Completed' ORDER BY ActivityDate ASC NULLS last];


/* uniqueIdList is list of account ids which are collected into a list and duplicates are eliminated */


List<Account> updatableAccountList = new List<Account>();

for(Task thisRecord:tasksPerId){

String convertedWhatId = (String)thisRecord.WhatId;

Boolean duplicateFound = false;

for(Integer k=0;k< updatableAccountList.size();k++){

String IdInUniqueList = (String)updatableAccountList[k].Id;

if(convertedWhatId.equalsIgnoreCase(IdInUniqueList)){

duplicateFound = true;

break;

}

}

if(!duplicateFound){

Account acc = new Account(Id=thisRecord.WhatId);

acc.whs_task_Status__c = thisRecord.Status;

acc.whs_upcomingTask__c = thisRecord.ActivityDate;

updatableAccountList.add(acc);

}

}

update updatableAccountList;

 

Once again thanks for your reply Homer!

All Answers

homer671homer671

This should do it.  You use the "in" to grab the whole list and then you look through the list instead of query one you get all the items in one query. 

 

List<Account> updatableAccountList = new List<Account>();
List <Task> tlist = [Select WhatId,Status,ActivityDate from Task where ActivityDate>today and WhatId in uniqueIdList and Status != 'Completed' ORDER BY ActivityDate ASC NULLS last LIMIT 1];
for(Task t :tlist ){
    Account acc = new Account(Id=t.WhatId);
    acc.whs_task_Status__c = t.Status;
    acc.whs_upcomingTask__c = t.ActivityDate;
    updatableAccountList.add(acc);
}
update updatableAccountList;

List<Account> updatableAccountList = new List<Account>();
List <Task> tlist = [Select WhatId,Status,ActivityDate from Task where ActivityDate>today and WhatId in uniqueIdList and Status != 'Completed' ORDER BY ActivityDate ASC NULLS last LIMIT 1];for(Task t :tlist ){
    Account acc = new Account(Id=t.WhatId);
    acc.whs_task_Status__c = t.Status;
    acc.whs_upcomingTask__c = t.ActivityDate;
    updatableAccountList.add(acc);
}
update updatableAccountList;

 

nishanth0208nishanth0208

Thanks for your help Homer!

Actually my requirement is to get the Latest upcoming task record(based on ActivityDate field)   for each account and want to capture that activity field value and need to place in one of the date field in that account. 

I may not get some thing like that if i do like above. 

I achieved this requirement in the following way(eliminating duplicates and taking one latest task record for each account and updating):

 

List<Task> tasksPerId = [Select WhatId,Status,ActivityDate from Task where ActivityDate>today and WhatId in: uniqueIdList and Status != 'Completed' ORDER BY ActivityDate ASC NULLS last];


/* uniqueIdList is list of account ids which are collected into a list and duplicates are eliminated */


List<Account> updatableAccountList = new List<Account>();

for(Task thisRecord:tasksPerId){

String convertedWhatId = (String)thisRecord.WhatId;

Boolean duplicateFound = false;

for(Integer k=0;k< updatableAccountList.size();k++){

String IdInUniqueList = (String)updatableAccountList[k].Id;

if(convertedWhatId.equalsIgnoreCase(IdInUniqueList)){

duplicateFound = true;

break;

}

}

if(!duplicateFound){

Account acc = new Account(Id=thisRecord.WhatId);

acc.whs_task_Status__c = thisRecord.Status;

acc.whs_upcomingTask__c = thisRecord.ActivityDate;

updatableAccountList.add(acc);

}

}

update updatableAccountList;

 

Once again thanks for your reply Homer!

This was selected as the best answer