SOQL query to traverse several levels on Task

Question

I'm trying to write some apex code using this query and not getting anywhere:

List<Task> tasks = [SELECT id, whatid, who.account.parent.name FROM task WHERE who.account.parent.name LIKE 'Procter%'];

I'm not surprised this doesn't work, but can't seem to find documentation anywhere that explains how I would go about this. Does anyone have any idea? I'm trying to get all tasks linked to a contact linked to an account with a parent account of "procter and gamble"...

Answer 1

Looks like the options to "go up" in the mixed fields (the ones where Lookup goes to multiple objects like WhatId going to Account or Opportunity) are very limited. I was able to write "WHERE what.name LIKE 'Procter%' but not "WHERE what.parent.name LIKE 'Procter%'".

By the way I think it should be WhatId and not the WhoId (check out the Validation Rule editor for Tasks, try to insert fields "Contact/Lead ID" and "Opportunity/Account ID"). You will also see that you can't "go up" (or in case of this editor - "go right") on these fields while for some other fields you can explore the relation like for "CreatedBy.UserRole.Name".

Can you try this subquery instead?

[SELECT id, whatid FROM task WHERE whatid IN (SELECT Id FROM Account WHERE Parent.Name LIKE 'United%')]

Answer 2

WhatId and WhoID are polymorphic fields, so these fields do not support traversing multiple levels.