0

How can I get a Contact from the Account while querying Opportunity?

SELECT o.Id, o.AccountId, a.Name
FROM Opportunity o, o.Account a

I can get all fields from Opportunity, account.Name. But how to include contact fields (email) that are inside the Account?

2

I'd suggest just breaking this into two queries. One to get the list of Account Ids given some Opportunity records, and then another one to query for Contacts based on the Account Ids.

If this query is inside of an Opportunity trigger, it'd only require one query (you'd have access to Opportunity.AccountId through one of the trigger context variables).

[SELECT Id, Email FROM Contact WHERE AccountId IN : <list of Account Ids>]

You could also use a parent-child subquery. If you store the result of this query in a Map<Id, Account>, you would have easy access to the Contacts

Map<Id, Account> accountWithContacts = new Map<Id, Account>([SELECT Id, Name, (SELECT Id, Email FROM Contacts) FROM Account WHERE Id IN : <list of Account Ids>]);
List<Contact> oppContacts = accountWithContacts.get(opportunity.AccountId).get('Contacts');

+edit:

After second thought, the method below wouldn't really help save any queries in any situation. If you're outside of an Opporunity trigger, you'd likely need a query to get the Account Ids, and if you were, including the parent-child subquery to Opportunity would be useless.

/edit

If you do need to keep your number of queries down, then it might be worth it to explore refactoring so your main query is on Account, using that as a junction table to tie Opportunity and Contact together.

[SELECT Id, Name, (SELECT Id, email FROM Contacts), (SELECT Id FROM Opportunities) FROM Account WHERE Id IN : <list of Account Ids you get from Opportunities>]

This query would count as 1 SOQL query, with 2 'Aggregate' queries. The transaction limit for these 'Aggregate' queries is currently 300, vs 100 for normal SOQL queries. All of the rows queried from Account, Contact, and Opportunity will (individually) count against the 50,000 limit.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.