Query First Task On An Account Using AccountID

Question

I am trying to write a SOQL query that will return the first task that was completed on an account.

SELECT Id,
(
SELECT id, subject, type, CreatedDate
from Tasks
WHERE TYPE IN ('Phone Call', 'Email') AND IsClosed = True
ORDER BY CreatedDate ASC
LIMIT 1
)
FROM ACCOUNT

My issue is that the results I get back do not include activities where the whatID is blank. These activities have an AccountID because their contacts are on the account, but the WhoID is blank.

I think the reason is happening is because my subquery implicitly grabs activities where the WhatId = ID (where ID is the id of the account).

What I really want is something like this which I can do in postgresql

SELECT Id,
(
SELECT id, subject, type, CreatedDate
from Tasks
WHERE TYPE IN ('Phone Call', 'Email') AND IsClosed = True AND Task.AccountId = Account.Id
ORDER BY CreatedDate ASC
LIMIT 1
)

FROM ACCOUNT

Does anyone know how to explicitly execute this subquery or otherwise achieve the result of subquerying all of the tasks related to an account based AccountID not WhatID

Thanks much

Nick

Answer 1

Try a diiferent related list:

Select id, name, (Select createdDate, Subject, whatId, IsClosed From ActivityHistories 
       where isClosed = true and type IN ('Phone Call', 'Email') 
        order by createdDate asc limit 1) 

  From Account

If you inspect the WhatId column, you'll see it will be blank (a completed activity on a Contact) but where AccountId is not null