SOQL Filter OpenActivties & ActivitiesHistory Query

I am working with ActivityHistories & OpenActivties and trying to see if there is a work around to my issue.
This query works but returms accounts that do not have the specific activities

Select id,
(SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
(SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account

But if I try to use this query I receive an error:

Select id,
(SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
(SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account where id IN (SELECT Account.id FROM ActivityHistories where subject ='Email: Test')

I just want to return the Accounts with the correct associated activities. Is this possible in SOQL?

Cheers,
M

Answer

I just want to return the Accounts with the correct associated activities. Is this possible in SOQL?

Yes it is possible but neither the OpenActivity nor ActivityHistory sObject are supported for semi join inner selects. What this means is that your second query would be invalid.

Select id,
    (SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
    (SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account where id IN (SELECT Account.id FROM ActivityHistories where subject ='Email: Test')

What you will need to do is make separate queries for OpenActivity and ActivityHisory sObjects, hold the Account Ids in a Set and then use the Set to query the Accounts.

Something like this should work for you.

Set<Id> accountIds = new Set<Id>();
for(OpenActivity oa : [SELECT AccountId FROM OpenActivity WHERE Subject = 'Email: Test']){
    accountIds.add(oa.AccountId);
}
for(ActivityHistory ah : [SELECT AccountId FROM ActivityHistory WHERE Subject = 'Email: Test']){
    accountIds.add(ah.AccountId);
}

List<Account> accounts = [SELECT id,
                            (SELECT AccountId, What.Name, ActivityDate FROM ActivityHistories WHERE Subject ='Email: Test'),
                            (SELECT AccountId FROM OpenActivities WHERE Status = 'Submitted')
                        FROM Account WHERE Id IN :accountIds]

Attribution
Source : Link , Question Author : Michael , Answer Author : dBeltowski

Leave a Comment