Salesforce - Apex - query accounts based on Activi

Question

Hey Salesforce experts,

I have a question on query account information efficiently. I would like to query accounts based on the updates in an activityHistory object. The problem I'm getting is that all the accounts are being retrieved no matter if there's "complete" activeHistory or not. So, Is there a way I can write this query to retrieve only accounts with activeHistory that has status="complete" and Type_for_reporting='QRC'?

List<Account> AccountsWithActivityHistories = [    
    SELECT
         Id
        ,Name
        ,( SELECT
                ActivityDate
               ,ActivityType
               ,Type_for_Reporting__c
               ,Description
               ,CreatedBy.Name
               ,Status
               ,WhatId
            FROM ActivityHistories
            WHERE Status ='complete'  and Type_for_Reporting__c = 'QRC'
        )
    FROM Account
];

Answer 1

You have a WHERE clause on the histories but you still miss one on the Account level.

For example this would return only Accounts that have Contacts:

SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact) // try with NOT IN too

---

With Activities it's trickier because they don't like to be used in WHERE in that way.

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select.htm

The following objects are not currently supported in subqueries:

• ActivityHistory
• Attachments
• Event
• EventAttendee
• Note
• OpenActivity
• Tags (AccountTag, ContactTag, and all other tag objects)
• Task

Additionally the fine print at the bottom of ActivityHistory definition is also a bit discouraging.

The following restrictions on users who don’t have “View All Data” permission help prevent performance issues:

• In the main clause of the relationship query, you can reference only one record. For example, you can’t filter on all records where the account name starts with ‘A’; instead, you must reference a single account record.
• You can’t use WHERE clauses.
• You must specify a limit of 499 or fewer on the number of rows returned in the list.
• You must sort on ActivityDate in ascending order and LastModifiedDate in descending order; you can display nulls last. For example: ORDER BY ActivityDate ASC NULLS LAST, LastModifiedDate DESC.

---

Looks like you will need multiple queries. Go for Task (or Event, depending for which the custom field is visible), compose a set of AccountIds and then query the Accounts?

Or you can manually filter through list from your original query, copying accounts to helper list:

List<Account> finalResults = new List<Account>();
for(Account a : [SELECT...]){
    if(!a.ActivityHistories.isEmpty()){
        finalResults.add(a);
    }
}