Count of Tasks from the WhoId Owner

Question

I'm trying to write a query to get the number of Tasks on a Contact or Lead that are owned by the same person that owns the task.

SELECT OwnerId, (SELECT OwnerId FROM Tasks)  FROM Contact

The query above gives me the results I need, but won't let me derive a COUNT on the Tasks.

I can certainly use the above to loop through within Apex to grab the number, but feel like there might be a cleaner way to do this?

Answer 1

There's only an elegant solution to this problem in the limited case where you have a single Contact you're interested in. Otherwise, SOQL's limitations on what your WHERE comparands can be (i.e., concrete values, not references to other fields) limits you to using Apex postprocessing as you describe.

Given one Contact, you can do

SELECT OwnerId FROM Contact

and then

SELECT count() FROM Task WHERE OwnerId = :myContact.OwnerId AND WhoId = :myContact.Id

But supposing you have many Contacts, and you need to know the counts for all of those. How do you do this efficiently?

One way to do this besides the parent-child query you already have is the aggregate route. Let's say you have a list of Contacts contactList and you want to find the count of Owner-owned Tasks for each one. You can do something like this with an aggregate query:

List<AggregateResult> ars;
Map<Id, Integer> counts = new Map<Id, Integer>();
Map<Id, Contact> contactMap = new Map<Id, Contact>(contactList);

ars = [SELECT count(Id) taskCount, OwnerId owner, WhoId contact
       FROM Task 
       WHERE WhoId IN :contactMap.keySet()
       GROUP BY WhoId, OwnerId];


for (AggregateResult ar : ars) {
    if (ar.get('owner') == contactMap.get(ar.get('contact')).OwnerId) {
        contactMap.put(ar.get('contact'), ar.get('taskCount'));
    }
}

For extra points, you can also accumulate a Set of Contacts' OwnerIds and use that in an additional WHERE clause to reduce the amount of irrelevant data returned to you.

This query also costs you less against your SOQL rows governor limit, since aggregate queries' cost is only the total count of aggregate rows returned now - rather than the total count of rows rolled up.