SOQL WHERE Clause Filtering on Polymorphic Relationships Bug for Cases (or other objects)

agentworkcasepolymorphicsoql

According to the documentation, you should be able to run a SOQL query where you filter on the type for a Polymorphic relationship. And this works in most cases. However, there's a conflict when the relationship includes the Case object because the Case object has a built-in field called "Type" which conflicts with this implicit Type field.

Now consider the following SOQL query:

SELECT Id FROM AgentWork WHERE WorkItem.Type IN ('Case', 'Voice_Callback_Request__c')

This will return no data, This query is actually looking in the Case.Type field for values.

The same would be true for any other object that has a built-in Type field.

I thought I was clever, so I tried the following:

WHERE
    WorkItem.CaseNumber != NULL
    OR WorkItem.Type IN ('Voice_Callback_Request__c')

But that is not having the expected result. I can't seem to get any of the records where the WorkItem type is Voice_callback_Request__c because SOQL is always querying the Case.Type field.

I also tried:

WHERE
    WorkItem.CaseNumber != NULL
    OR WorkItem.Callback_Phone__c != NULL

Since the Voice_Callback_Request__c object is the only object that has that Callback_Phone__c field I presumed it would filter to just those two types of objects. However, because Case doesn't have that field I receive this error:

WorkItem.CaseNumber != NULL OR WorkItem.Callback_Phone__c != NULL ^ ERROR at Row:1:Column:287 No such column 'Callback_Phone__c' on entity 'Case'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

I also considered filtering based on the prefix of the Id field and that would work, the only issue there is that we have several different environments (dev, uat, staging, production) and so the custom objects will have different prefixes in each environment.

This seems like a bug. The issue is that "Type" should be reserved word and not available for use as a field name in any object. Alas, this is not the case.

Does anyone know if there is some workaround available here? Is there some way to indicate to SOQL that I want the object type rather than the Type field of the Case object?

Best Answer

I can't speak for AgentWork.WorkItem as our org doesn't use this but the following works just fine:

SELECT Id, 
  TYPEOF What
    WHEN Case THEN CaseNumber
    WHEN Asset THEN Name
  END
FROM Task WHERE What.Type IN ('Case','Asset')

and returns records from any Task with a WhatId pointing at a Case or Asset - even though Case.Type is a standard field

Does the schema (use Workbench) actually define a polymorphic relationship for AgentWork.WorkItem? That could be the root of your issue and you might need to use two queries

Related Topic