Help with SOQL Query on Event to get Opportunity Fields

Question

I'm trying to construct a query on Event to retrieve some custom fields on Opportunity in the same query as the Event fields. The "WhatID" is the Opportunity.Id. The WhoID is unrelated to the Account. The OwnerID owns the Opportunity.

I keep getting the error message that Apex doesn't understand the relationship of Opportunity, Opportunity__r, Opportunities, Opportunities__r, etc., regardless of what I seem to do; including enclosing that portion in a separate sub select.

Here's what I want to select:

Assnmnt = [select Id, OwnerID, Owner.Name, Subject, ActivityDateTime, ActivityDate, DurationInMinutes, StartDateTime, EndDateTime, Description, WhatID, WhoID, Who.Name, What.Opportunity.Account_Address__c, What.Opportunity.Account.Name, What.Opportunity.Account_Phone__c, Opportunty__r.Customer_Contact__c from Event Where Id =: Asn2Create and WhatId =: What.Opportunity.Id];

Any help on how to properly construct this to work would be appreciated.

Answer 1

In the query example you provided above, "What" is already the Opportunity, you don't need to "drill-in" to a reference off of that field. So, you should be able to access Opportunity fields using What.[Opportunity-Field-Name] as long as the fields you are querying are Standard fields.

To query custom fields from a related object on Tasks or Events, you could use TYPEOF in a SOQL statement, but you'd need to contact Salesforce to get it enabled first.

I'm not entirely sure if this would work because I don't have TYPEOF enabled, but you could try this:

// where getOpportunityId is a method that returns the Opportunity Id
My_Opportunity_ID = getOpportunityId(); 

Assnmnt = [
    SELECT 
        Id, OwnerID, Owner.Name, Subject, ActivityDateTime, ActivityDate, 
        DurationInMinutes, StartDateTime, EndDateTime, Description,  WhatID, WhoID, 
        Who.Name, 
        TYPEOF What 
            WHEN Opportunity THEN Account_Address__c, Account.Name, 
                 Account_Phone__c, Customer_Contact__c 
        END
    FROM Event 
    WHERE Id = :Asn2Create 
    AND WhatId = :My_Opportunity_ID
    AND What.Type = 'Opportunity'
];