How do I access custom fields of Task parent record using SOQL?

Question

I have a custom object called Invoice that allows for Tasks/Events to be created for it. Using the SOQL query below, I am able to query on the Invoice objects standard Name field.

SELECT What.Name FROM Task WHERE Id = 'xxxxxxxxxxxxxxx'

My problem is such that, when I modify the statement to query on a custom field on the parent Invoice object, I receive an error. Why is this?

SELECT What.Name__c FROM Task WHERE Id = 'xxxxxxxxxxxxxxx'

No such column 'Name__c' on entity 'Name'.

I'm guessing that this is querying on the Name field instead of the sObject, but I've tried every permutation I can think of -- WhatId.Name__c, What__r.Name__c, What.Id.Name__c, etc. -- and receive similar errors.

Please help.

Answer 1

Since the WhatId can refer to any number of object types, you can't reference a custom field from a single one of those objects.

WhatId: ID of a related Account, Opportunity, Campaign, Case, or custom object

https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_task.htm

You can however use a subquery:

SELECT Name__c FROM My_Object__c WHERE Id IN (SELECT WhatId FROM Task WHERE Id = 'XXXXXX')

UPDATE:

Apparently you can't use a subquery for Tasks. If you are using Apex, you can break it into two separate queries.

Task t = [SELECT WhatId FROM Task WHERE Id = 'xxx'];
My_Object__c m = [SELECT Name__c FROM My_Object__c WHERE Id =: t.WhatId];

You'll want to add some additional checking for null values, but that should do the trick.

Answer 2

You can get only Standard fields like Id, Name of parentId/WhatId through SOQL Query on Task, as WhatId can get casted to Id of any sObject and SOQL cant search on all the sObjects on this search. You need to use another query to get Name__c after getting the sObjectType of the WhatId.

UPDATE:

Use WhatId.getSObjectType() to get the the object name and use

Database.Query('Select Id from '+WhatId.getSObjectType()+' LIMIT 1') ;