[SalesForce] Is SOQL Polymorphism also supported in WHERE clause

I am using the polymorphism in my SOQL queries which is working fine, an generic example of the query is below

SELECT 
    TYPEOF What 
        WHEN Account THEN Phone, NumberOfEmployees 
        WHEN Opportunity THEN Amount, CloseDate 
        ELSE Name, Email 
    END 
FROM Event

Now if i want to only query all the Event associated to all accounts which have a phone number of '123456', is that possible? I would need to use polymorphic fields in my where condition. I am trying the syntax but i am getting issues, not very sure if we can use polymorphism in where condition. What i am trying to get is something like this but is not working

SELECT TYPEOF What WHEN Account THEN Phone, NumberOfEmployees WHEN
Opportunity THEN Amount, CloseDate ELSE Name, Email END FROM Event
where TYPEOF What WHEN Account THEN Phone Else Name END ='123456'

Best Answer

Based on the current TYPEOF documentation that clause is only applicable within the SELECT statement.

TYPEOF is an optional clause that can be used in a SELECT statement of a SOQL query when you’re querying data that contains polymorphic relationships.

You might need to use something like What.Type = 'Account' and What.Phone = '123456' in the SOQL query. Maybe build it up with some additional conditions.

E.g.

SELECT ID, WhatId 
FROM Event 
WHERE 
    (What.Type = 'Account' and What.Phone = '12345') or 
    (What.Type = 'Campaign' and What.Name = '12345')

Oddly, as @Anil observes in the comments, other Account fields produce an error:

SELECT ID, WhatId, What.Type
FROM Event 
WHERE 
    (What.Type = 'Account' and What.BillingCity = '12345')

gives:

INVALID_FIELD:
(What.Type = 'Account' and What.BillingCity = '12345')
ERROR at Row:4:Column:36
No such column 'BillingCity' on entity 'Name'. 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 suspect the What.Type = 'Account' guard statement isn't preventing the column checking on the polymorphic What field.

I was going to suggest that if all the types referenced by the field have the field being checked then the filter will work and if the field only belongs to a subset of the types they you will get the error. However, this doesn't appear to be the case either, as Campaign doesn't have a Phone field.

Related Topic