How to limit a query on Event based on Opportunity standard field - StageName

Question

In the below query, I am trying to extract all Events that have a Opportunity of record type Id = 012240000002Cu8. I also want to further restrict this query based on the opportunity's stage

My query:

Select Id,X1st_booked_meeting__c, whatId FROM Event where what.recordtypeId = '012240000002Cu8' 

What I am trying to achieve:

Select Id,X1st_booked_meeting__c, whatId FROM Event where what.recordtypeId = '012240000002Cu8' AND what.StageName!='Order Processed' AND what.StageName!= 'Closed Lost'.

what.StageName is throwing an error saying :

No such column 'StageName' 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.

Here StageName is standard picklist field on opportunity. what.StageName or whatId.Stagename does not seem to work.

I would like to know if this is possible?

Answer 1

You can do it in one soql-query without not needed loops:

[
    select Id,X1st_booked_meeting__c, WhatId
    from Event
    where WhatId in (
        select Id
        from Opportunity
        where RecordTypeId = '012240000002Cu8'
            and StageName != 'Order Processed' 
            and StageName != 'Closed Lost'
        )
]

PS. Hardcoding ids in code is a bad practice.

Answer 2

Since you can not directly query this on Event as your what Id can be any object. What you can do is first query the opportunity

 List<Opportunity> opp = [select Id from Opportunity where RecordTypeId = '012240000002Cu8' AND StageName != 'Order Processed' AND StageName != 'Closed Lost'];
List<Id> oppId = new List<Id>();
for(Opportunity op : opp){
   oppId.add(op.Id);
}

Then query your Event based on this

Select Id,X1st_booked_meeting__c, whatId FROM Event where whatId IN : OppId