+ Start a Discussion
Jon KeenerJon Keener 

SOQL question on returning tasks directly and indirecty related to an Account in a single query

I've put together a relatively simple SOQL statement and have ran into an issue with it returning indirectly related tasks.  Tasks where the they are related to the contact related to the account vs. directly related to the account.  Is there a simple adjustment that can be made to the following SOQL statement so that it will return all of the tasks, as you would see on the regular account activity history related list, whether they are directly or indirectly related?
 
Code:
SELECT Id, Name, OwnerID, Owner.Name, RecordTypeId, RecordType.Name, Type,
    (SELECT Id, FirstName, LastName, Title, Phone, Fax, Email, OwnerId, Owner.Name FROM Contacts ORDER BY LastName ASC, FirstName ASC),
    (SELECT Id, Subject, ActivityDate, Description, Status, Type, OwnerId, Owner.Name, CreatedById, CreatedBy.Name FROM Tasks ORDER BY ActivityDate DESC)
  FROM Account 
 WHERE id = :accountId

 
The query above returns all of the tasks that are directly related to the account via the WhatId field, but not those indirectly related through the contact related to the account.
 
Thanks!
 
Jon Keener
Best Answer chosen by Admin (Salesforce Developers) 
aalbertaalbert
You are correct, when using SOQL relationships to pull back related Tasks, it is querying by WhatId. I reproduced your scenario.
It will require an additional query statement, but if you query the Task object directly, you can filter by AccountId field and get the direct and indirectly related
tasks. The AccountId field is a read-only field that you can query.

For example:

Task[] tsks = [SELECT Id, Subject, ActivityDate, Description, Status, Type, OwnerId, Owner.Name, CreatedById, CreatedBy.Name FROM Task where accountid = :accountId];

All Answers

aalbertaalbert
You are correct, when using SOQL relationships to pull back related Tasks, it is querying by WhatId. I reproduced your scenario.
It will require an additional query statement, but if you query the Task object directly, you can filter by AccountId field and get the direct and indirectly related
tasks. The AccountId field is a read-only field that you can query.

For example:

Task[] tsks = [SELECT Id, Subject, ActivityDate, Description, Status, Type, OwnerId, Owner.Name, CreatedById, CreatedBy.Name FROM Task where accountid = :accountId];

This was selected as the best answer
Jon KeenerJon Keener
That is basically what I ended up doing and now have my code working the way it needs to. 
 
I was hoping there might be a way to do this in a single query so later on in my code when I looped over the results, I would just have to loop over 1 list, without any additional work to combine the results of multiple queries for processing/display in a VF dataTable.
 
Thanks