Best way to get the Contact field values like Title, Owner ... for each EventRelation/TaskRelation

Question

SELECT id, subject, StartDateTime, EndDateTime, Ownerid, Owner.Name, Type, What.Name,
       Who.Name, RecordTypeId, Description, CreatedById, 
      **(SELECT RelationId, Relation.Name, Relation.Type, Account.Name from EventRelations 
            WHERE Relation.Type='Contact')** 
  FROM Event WHERE whatId = :oppID

The above query is getting all the Event's related to a WhatId(Opportunity) and ContactID , Contact Name related to each Event . Trying to find the best way to get more Contact field values like Title, Owner ... for each EventRelation.

Answer 1

The key thing to note is that Event doesn't have your standard relationship type that you are used to. However, what you are trying to accomplish is possible but requires additional some knowledge. The relationship on the 'Who' object I believe is a lookup on 'Contact' or 'Lead'. Therefore, in order to grab the fields that you are wanting to grab from the Event record you need to do a TYPEOF operator. I'll let the code speak for itself:

SELECT TYPEOF WHO WHEN Contact THEN Id, Name, Title, Account.Name WHEN Lead THEN Id, Name, Title, Company END FROM Event

Notice in order to get the 'Account Name' on a Contact it is Account.Name but on a Lead they do not yet have 'Accounts' Associated with them unless you have extended your Lead Object so you need to pull in the Company field when the TYPEOF WHO is 'Lead'

Hope this helps.