SOQL issue when querying ActivityHi​story 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. Does 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));

        }

     }

    }

Answer 1

Well, it's helpful to split out SOQL queries from the for loop, then use the getSObjects method to get the subquery data that has been returned. Using this method, I successfully retrieved more than 200 ActivityHistory records in an Apex class. Try the following code:

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));

    }

  }

}

I hope this solves your query.