Using children in WHERE criteria of Parent-to-Child SOQL query

Question

Can't seem to find an example of this.

trigger Opportunity_Triggers on Opportunity (after insert, after update, after delete) {

    List<Account> accList = [SELECT Id, (SELECT Id, AccountId FROM Opportunities) FROM Account WHERE Opportunities IN :Trigger.New];
    ...
}          

I want the trigger to fire upon changes to opportunities, but my handler class works with their accounts as well, and I'm trying to minimize my SOQL queries so this can work in bulk.

My issue seems to be with using Opp data pulled from the inner query in the outer query's WHERE clause.

Now, what if I did this:

List<Account> accList = [SELECT Id, (SELECT Id, AccountId FROM Opportunities WHERE Id IN :Trigger.New) FROM Account];

Only the child opps that were triggered would show up... but would that limit the account query to only the parents of those triggered opps?

Answer 1

It would not limit the account to parents of Opps in the trigger context.

I know you aren't looking for it to, but if you did want it to limit it to parent accounts of the opps in the trigger then this would be a query to do so.

[SELECT Id, (SELECT Id, AccountId FROM Opportunities WHERE Id IN :Trigger.New) 
     FROM Account Where Id IN : (Select AccountId From Opportunity Where Id IN: Trigger.New];

Your query that you have will work, but I think there is a more elegant way. You already have trigger.new to access the Opportunity data, so you really just need a way to access the Account data. I would leverage a map here. Something like this

map<Id,Account> accountMap = new map<Id,Account>();
for(Opportunity o : Trigger.New) {
     if(o.AccountId != null){
          accountMap.put(o.AccountID, Null);
     }
}

accountMap.putall([Select Id, Name, allYourFields From Account Where Id IN : accountMap.keyset()]);

Now you can go through your opps in Trigger.New and have access to the Account data as well

for(Opportunity o : Trigger.New){
     //use your accountMap to access whatever account values you want
     string = accountMap.get(o.AccountId).Name
}