SOQL issue when querying ActivityHistory as a subquery..

Question

I'm querying ActivityHistory, and it seems that you can only query it as the object of a SUBQUERY against some other object. That's fine - I changed my query to query against the Account object, with ActivityHistory in a subquery. Seemed to work fine. But in testing, I found a case where the ActivityHistory subquery returned MORE than 200 results. I started getting this error:

    FATAL_ERROR|System.QueryException: entity type ActivityHistory does not support query

... in the debug logs. It seems to be only an issue where an Account has more than 199 related entries in the ActivityHistory object. To see if this was the cause, I tried putting a LIMIT 199 and a LIMIT 200 clause in the subquery. Sure enough, when I use 199 (or anything lower) it works fine. Using 200 (or anything higher) results in the error above.

My code is below. One thing to note is that the query is in a FOR loop. One theory I have as to why it's producing the error for high values of the LIMIT clause is that perhaps 200 is the point where the FOR loop is batching the query into separate chunks - maybe the second chunk doesn't qualify as a 'subquery' (since it's running separately?) - and SalesForce isn't liking that.

Oh, and one more thing: The same code seems to run fine in the Anonymous Apex editor (though I had to make a few modifications - replacing the inline variables with explicit values). Weird that the anon editor is perfectly happy with it, but the SFDC servers don't like it.

Anyway, I'm off to do some more troubleshooting. Anyone have any insights?

Thanks!

Code:

    //  ActivityHistory's on Account
    for (Account a : [  //  you can't query ActivityHistory directly; only in a subquery against another object type
        SELECT
             Id
            ,Name
            ,( SELECT
                    ActivityDate
                   ,ActivityType
                   ,CRM_Meeting_Type__c
                   ,Description
                   ,CreatedBy.Name
                   ,Status
                   ,WhatId
                FROM ActivityHistories
                WHERE ActivityType IN :included_activity_history_types
                //LIMIT 200
            )
        FROM Account WHERE Id = :accountId
    ]) {
     for (ActivityHistory ah : a.ActivityHistories) {
        if ( ah.WhatId==null ) { continue; }  //  skip adding activities without a WhatId

        if (((string)ah.WhatId).startsWith('001')) { //  only add ActivityHistory's tied directly to an Account (the query above pulls back all ActivityHistory's on related Oppty's as well)
            activities.add(new ActivityWrapper(ah));
        }
     }
    }

Matt K Matt K · Accepted Answer · 2012-06-08T15:19:45

Great question; I'm not entirely sure why it would be working in Execute Anonymous, but not in your Apex class.

However, I do have some recommendations. I've found that it's helpful to split out SOQL queries from the for loop, then use the getSObjects method to get the subquery data that was returned. Using this method, I have successfully retrieved more than 200 ActivityHistory records in an Apex class.

Note: one difference (between your code and the previous way I got this working) is that I did not filter by ActivityType in the ActivityHistories WHERE clause. So, if the code below throws the same error, I would check that next.

List<Account> AccountsWithActivityHistories = [  
    // you can't query ActivityHistory directly; only in a subquery against another object type
    SELECT
         Id
        ,Name
        ,( SELECT
                ActivityDate
               ,ActivityType
               ,CRM_Meeting_Type__c
               ,Description
               ,CreatedBy.Name
               ,Status
               ,WhatId
            FROM ActivityHistories
            WHERE ActivityType IN :included_activity_history_types
            //LIMIT 200
        )
    FROM Account WHERE Id = :accountId
];

// ActivityHistories on Account
for (Account a : AccountsWithActivityHistories) {
  for (ActivityHistory ah : a.getSObjects('ActivityHistories')) {
    // skip adding activities without a WhatId
    if ( ah.WhatId==null ) { continue; }  

    if (((string)ah.WhatId).startsWith('001')) { 
        // only add ActivityHistory's tied directly to an Account 
        // (the query above pulls back all ActivityHistory's on related Oppty's as well)
        activities.add(new ActivityWrapper(ah));
    }
  }
}

SOQL issue when querying ActivityHi​story as a subquery..​

2 Answers

SOQL issue when querying ActivityHistory as a subquery..