4

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'

2
  • I suspect you'll have to use an uglier: where whatid in (select id from account where phone = '123456') or whatid in (select id from opportunity where name = '123456')
    – sfdcfox
    Jan 15, 2015 at 0:15
  • thanks @sfdcfox but you couldnt use right outer join for whatId field. Dont you get a error when you execute the above SQOL Jan 15, 2015 at 16:31

1 Answer 1

5

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.

2
  • Note, I didn't have enough Event data loaded in my test org to verify the proposed SOQL query returns the required results. It does however execute without an error. Jan 15, 2015 at 0:21
  • 1
    Thats very unfortunate that we dont polymorphism in the where clause, Using WHAT.Phone helps but it doesnt work on all account fields , if you try What.BillingCity doesnt not work. I am not sure how only few fields like name, phone etc are working for what.FIELDNAME Jan 15, 2015 at 16:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .