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
rrobertsrroberts 

How to Select a List <Lead> IN a List <Aggregate Result>?

I want to pull a list of Leads from a SOQL query, where the Leads meet some requirements, and are also found in another list from a previous query.

 

A dumbed down version of what I've got:

 

List<AggregateResult> numTasks = [SELECT WhoId
    FROM Task
    WHERE
        status='Completed' AND
        isClosed = true AND
        ActivityDate = LAST_N_DAYS:35
    GROUP BY WhoId
    HAVING count(id) >= :TASKS_MAX];

List<Lead> openLeads = [Select id, ownerId
    FROM Lead
    WHERE
        OwnerId = 'Enter a queue ID here' AND
        Days_Since_Modified__c <= :NUM_OF_DAYS_TO_WATCH AND
        Id IN :numTasks];

 I get:"Error: Compile Error: Invalid bind expression type of SOBJECT:AggregateResult for Id field of SObject Lead at line 63 column 25"

 

I understand it's a type issue, but I find it funny I can go from a Lead List to an Aggregate Result, but not vice versa.

 

How do I do this correctly?  I can't seem to find anything about AggregateResult Lists in the developer's guide that addresses this.

 

Best Answer chosen by Admin (Salesforce Developers) 
Naidu PothiniNaidu Pothini
Map<Id, AggregateResult> numTasks =  new Map<Id, AggregateResult>([SELECT WhoId Id
								   FROM Task
								   WHERE status='Completed'
								   AND isClosed = true
								   AND ActivityDate = LAST_N_DAYS:35
								   GROUP BY WhoId
								   HAVING count(id) >= :TASKS_MAX]);

List<Lead> openLeads = [Select id, ownerId
			FROM Lead
			WHERE OwnerId = 'Enter a queue ID here'
                        AND Days_Since_Modified__c <= :NUM_OF_DAYS_TO_WATCH
                        AND Id IN :numTasks.KeySet()];

 Try this.

All Answers

JHayes SDJHayes SD

Just took a quick look at the code you posted and it looks upon first sight like a syntax error here:

 

:numTasks')

 

What happens when you get rid of the single quote and the closed paren?
rrobertsrroberts
Sorry, that was an editing mistake - not actually in the code. I've edited and fixed it.

It is not a syntax error, it's a typing error.
JHayes SDJHayes SD

Haha I saw you edited your post at the same time as I posted mine.  I'll try the queries out and let you know what I find.

 

Edit:  

 

I think the documentation you're looking for is here:  http://www.salesforce.com/us/developer/docs/apexcode/index_Left.htm#CSHID=langCon_apex_SOQL_agg_fns.htm|StartTopic=Content%2FlangCon_apex_SOQL_agg_fns.htm|SkinName=webhelp

 

You can always create a new List and assign the aggregated field values as members.  A simple example (you will want to create List<Id> instead of List<String>:

 

List<String> statusList = new List<String>();
List<AggregateResult> ars = [Select Status, COUNT(Id) from Lead GROUP BY Status];
for (AggregateResult ar : ars) {
	statusList.add((String) ar.get('Status'));
}
for (String s : statusList) {
	System.debug(s);
}

 
Naidu PothiniNaidu Pothini
Map<Id, AggregateResult> numTasks =  new Map<Id, AggregateResult>([SELECT WhoId Id
								   FROM Task
								   WHERE status='Completed'
								   AND isClosed = true
								   AND ActivityDate = LAST_N_DAYS:35
								   GROUP BY WhoId
								   HAVING count(id) >= :TASKS_MAX]);

List<Lead> openLeads = [Select id, ownerId
			FROM Lead
			WHERE OwnerId = 'Enter a queue ID here'
                        AND Days_Since_Modified__c <= :NUM_OF_DAYS_TO_WATCH
                        AND Id IN :numTasks.KeySet()];

 Try this.

This was selected as the best answer
rrobertsrroberts

I ended up using iterating through numTasks and casting the objects into Set<ID> but after finishing, it became clear that a Map was the best option.  If I were to do it again, I'd go with NaiduPothini's solution.

 

Thanks everyone for your help!
davidjgriffdavidjgriff

You can't actually assign those aggregate results to a Map like that.

You'll have to iterate over the AggregateResults returned by your query and extract the Lead Id to store into another collection.

 

Something like this.

 

Set<Id> leadIds = new Set<Id>();

for(AggregateResult ar : [SELECT WhoId  FROM Task WHERE status='Completed'   AND isClosed = true   AND ActivityDate = LAST_N_DAYS:35 	AND Who.Type = 'Lead'   GROUP BY WhoId   HAVING count(whoid) >= :TASKS_MAX]){

leadIds.add(String.valueOf(ar.get('WhoId')));
}

List<Lead> openLeads = [Select id, ownerId
			FROM Lead
			WHERE OwnerId = 'Enter a queue ID here'
                        AND Days_Since_Modified__c <= :NUM_OF_DAYS_TO_WATCH
                        AND Id IN :leadIds];

 

I also added a filter to ensure you are only returning Tasks that are associated with Leads.