How to get List of Opportunities with condition on OpportunityContactRole?

Question

I would like to retrieve the list of Opportunities which have at least one OpportunityContactRole with role equal to test

Right now, I am doing the below query but it retrieves all the rows in the opportunity table..

[SELECT Id,
    (SELECT Id FROM OpportunityContactRoles WHERE Role = 'test')
FROM Opportunity 
WHERE IsClosed = TRUE AND IsWon = TRUE];

Is there any better alternative for this??

Answer 1

You have two options. Which approach to use depends on what else you're trying to do.

1. Query from the child object:

[SELECT Id, OpportunityId
 FROM OpportunityContactRoles
 WHERE Opportunity.IsClosed = TRUE
    AND Opportunity.IsWon = TRUE
    AND Role = 'test']

You can then loop over these results to get unique Opportunity Ids.

2. Use a second sub-query in your WHERE clause:

[SELECT Id,
    (SELECT Id FROM OpportunityContactRoles WHERE Role = 'test')
FROM Opportunity 
WHERE IsClosed = TRUE AND IsWon = TRUE
    AND Id IN (SELECT OpportunityId FROM OpportunityContactRoles WHERE Role = 'test')];

This approach filters the main query based on the records found in the sub-query.