SOQL With Owner Fields cause Errors

Question

I'm trying to receive some fields from Owner in a custom object.

Some of the standard fields are retrieving perfectly and some throws me an error.

Example:

[SELECT Name, Fields__c,Field2__C Owner.Name, Owner.Phone, Owner.MobilePhone, Owner.Email FROM CustomObject__c];

All fields are retrieving except Owner.mobilePhone - and I got the next Error:

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

Which mobile phone is a standard field in the User object.

I saw this post : https://ipfs-sec.stackexchange.cloudflare-ipfs.com/salesforce/A/question/47467.html

But it 5+ years ago.. something changed?

I prefer not creating a new field as a formula to fix it and use this custom Field in the SOQL query.

Any other solutions?

Thanks.

Answer 1

I think the problem here is that User.Owner is a polymorphic field. That is it can reference either a User object or a Group object. Personally, I just do another query on User via the OwnerId field once I've checked that this Id a User object not a group. I'm interested in finding out if there's a better way of handling this.

Why does Owner.Phone work in this situation and Owner.MobilePhone not work when Group has not Phone field? I have no clue.

Answer 2

As the Owner field can be a polymorphic field, you can only access those fields which are common across both objects.

In such cases, you can use TYPEOF in your SOQL query and select the fields based on the Owner type.

SELECT 
    [fieldList,] 
    [TYPEOF typeOfField 
        {WHEN whenObjectType THEN whenFieldList}[...] 
        [ELSE elseFieldList] 
    END][...] 
FROM objectType

So you can write your query like below

SELECT TYPEOF Owner 
    WHEN User THEN UserFields 
    WHEN Group THEN GroupFields END
FROM CASE

Your final query will be something like below wherin you can select the fields based on the type and this will return you List<CustomObject__c>

SELECT Id, Name, Fields__c,Field2__c, 
    TYPEOF Owner 
        WHEN User THEN Name, Email, Phone, MobilePhone 
        WHEN Group THEN Name, Email 
    END
FROM CustomObject__c