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
JoyJobingJoyJobing 

Select newest Task from Map of AcctId's

I'm trying to recreate Last Activity on the Account but need a few different parameters so built a Trigger.  My code is currently 

		List<Task> results = new List<Task> ([SELECT id, ActivityDate, AccountId FROM Task 												WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true AND AccountId IN :dates.keySet()												ORDER BY ActivityDate DESC LIMIT 1]);

 BUT I realized that this is giving me back only 1 result period...not 1 result per iD in the map keySet.  Is there a way to do this, or am I forced to make this NOT bulk safe, and throw the SELECT into a for loop around the map?

 

And I imagine that pulling ALL Tasks on each would be a bad idea - could be hundreds or thousands per Account.

 

Thanks in advance!

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
public void soqlupdate(set<id> accountids) {
  account[] query = [select id,(select id,activitydate from tasks where owner_matches_account_owner__c = true and isclosed = true order by activitydate desc limit 1) from account where id in :accountids], updates = new account[0];
  for(account record:query)
    updates.add(new account(id=record.id, last_activity_by_employer_owner__c=record.tasks.isempty()?null,record.tasks[0].activitydate));
  update updates;
}

ORDER BY and LIMIT both work in a sub-query, so this is the probably the most optimal in terms of query rows returned (but does require "two" queries towards the SOQL query limit).

All Answers

ryanjuptonryanjupton

Yep, that 'LIMIT 1' limits the query to only returning one result. Since the list will be ordered by DESC you could try this.

 

List<Task> results = new List<Task> ([SELECT id, ActivityDate, AccountId FROM Task 												WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true AND AccountId IN :dates.keySet()												ORDER BY ActivityDate DESC]);

 This is giving you back one result per ID in the map stored in the list results. As to the second part of your question, could you post the complete code?
JoyJobingJoyJobing

Thanks Ryan, wouldn't that statement then pull ALL Tasks for each AccountId in the map then?

 

Here's my code for the function:

public void SOQLUpdate(Map<ID,Date> dates) {
	List<Account> AcctsUpdate = new List<Account> ();
	List<Task> results = new List<Task> ([SELECT id, ActivityDate, AccountId FROM Task 									WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true AND AccountId IN :dates.keySet()									ORDER BY ActivityDate DESC LIMIT 1]);
	//update any that we got a new date for
	for(Task t:results) {
		dates.put(t.AccountId, t.ActivityDate);
	}
		
	//create a list of ALL accounts (not just those we got a date for) to send to update
	for (ID i:dates.keySet()) {
		Account a = new Account (ID = i, Last_Activity_by_Employer_Owner__c = dates.get(i));
		AcctsUpdate.add(a);
	}
	//update the list
	update AcctsUpdate;
}

 
JoyJobingJoyJobing

Perhaps some sort of nested query/subquery?  I'm reading the documentation trying to figure them out since I've never used them before...

 
sfdcfoxsfdcfox
public void soqlupdate(set<id> accountids) {
  account[] query = [select id,(select id,activitydate from tasks where owner_matches_account_owner__c = true and isclosed = true order by activitydate desc limit 1) from account where id in :accountids], updates = new account[0];
  for(account record:query)
    updates.add(new account(id=record.id, last_activity_by_employer_owner__c=record.tasks.isempty()?null,record.tasks[0].activitydate));
  update updates;
}

ORDER BY and LIMIT both work in a sub-query, so this is the probably the most optimal in terms of query rows returned (but does require "two" queries towards the SOQL query limit).

This was selected as the best answer
JoyJobingJoyJobing

Great!  I had a bit of a time reading your code (your shorthand is confusing for this beginner :-) ) but I think I got it.  Here's what I ended up with, and it's working, so thank you mucho mucho!

public void SOQLUpdate(Map<ID,Date> dates) {
//the map passed has all the ID's and nulls in all the dates - so if no eligible tasks are found, it will update to null
	List<Account> AcctsUpdate = new List<Account> ();
	List<Account> results = new List<Account> ([SELECT id, (SELECT id, ActivityDate, AccountId FROM Tasks WHERE Owner_Matches_Account_Owner__c = true AND IsClosed = true ORDER BY ActivityDate DESC LIMIT 1) FROM Account WHERE id IN :dates.keySet()]);

	//update any that we got a new date for
	for(Account a:results) {
		dates.put(a.Id, a.tasks[0].ActivityDate);
	}
	//create a list of ALL accounts (not just those we got a date for) to send to update
	for (ID i:dates.keySet()) {
		Account a = new Account (ID = i, Last_Activity_by_Employer_Owner__c = dates.get(i));
		AcctsUpdate.add(a);
	}
	//update the list
	update AcctsUpdate;	
}

 

THANK YOU!!! 
sfdcfoxsfdcfox
Your code will fail if no activities are found. Make sure you at least check that a.tasks.isempty() == false before trying to read a.tasks[0], or you'll get an exception.
JoyJobingJoyJobing

Good point, I updated the code to check for that as well.  Thanks!  So happy my whole trigger now works for all cases!  Yet when I went to deploy, I got errors on classes in production for year or more now that are blocking me from deploying this new one.  So frustrated to have to debug old code I never wrote! :-(