When querying for CreatedBy.SmallPhotoUrl from subquery throws an error message

Question

I am trying to query CreatedBy.SmallPhotoUrl from the case feed item object but I keep getting an error "No such column 'SmallPhotoUrl' 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."

This error is thrown only when I try to retrieve the photo url field from the subquery, it work's fine if the field is in the parent query.

SELECT Id, CreatedById, CreatedBy.Name, CreatedBy.SmallPhotoUrl, (SELECT Id, CreatedById, CreatedBy.Title, CreatedBy.Name, CreatedBy.SmallPhotoUrl FROM Feeds ORDER BY CreatedDate DESC NULLS LAST LIMIT 10) FROM Case ORDER BY CreatedDate DESC NULLS LAST LIMIT 25

Answer 1

Interestingly the CaseFeed.CreatedById is a polymorphic field (polymorphicForeignKey is true). Much the same as Case.OwnerId which can be a User or a Queue. See https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_name.htm

You can use the TYPEOF operator in SOQL queries to determine the type and then access a field only if the type is User:

SELECT Id, TYPEOF CreatedBy WHEN User THEN CreatedBy.SmallPhotoUrl END FROM CaseFeed

Answer 2

You can get from SmallPhotoUrl with this query.

select Id,SmallPhotoUrl from User

But Can't get it direct way as you mentions. There is no field like CreatedBy.SmallPhotoUrl in salesforce.

You need to store all CreatedByIds in a variable and you need to filter like

Map<ID, User> userMap =  new Map<ID, User>([select Id,SmallPhotoUrl from User where Id =: <your variable>])

Now you can get User.SmallPhotoUrl using userMap.get('').