SOQL To Get Opportunity, Account and Contact from one query to Opportunity Object

Question

I'm wondering if it is possible to query the Opportunities object and return associated Account and Contact data.

I so far have:

SELECT Opportunity.Id, 
Opportunity.AccountId, 
Opportunity.Owner.Name, 
Account.Company_Division__c, 
Account.Name, 
Account.BillingStreet, 
Account.BillingCity, 
Account.BillingState, 
Account.BillingPostalCode,     
Account.Phone, 
Account.Fax
FROM Opportunity

But I can't figure out the syntax to get the Contacts that are associated with the Account.

I know it has something to do with the AccountContactRelations object, but I can't seem to get there from the Opportunity object.

** This question is NOT looking for the OpportunityContactRole, but rather contacts associated with an Account. **

P.S.: The code I'm working with will take hours to fix if I have to change the FROM Opportunity part of this query. I would greatly prefer to not change that if possible. Thanks!

Answer 1

You cannot do this from a single SOQL query using FROM Opportunity. You'll either need a second query (and some additional processing) or to switch your query to be on Account instead.

SOQL makes it simple (though tedious) to traverse up a relationship hierarchy, but really limits what we can do to query down a relationship hierarchy. We can only traverse down one level, and only from the object that we're querying in the FROM clause.

What you're suggesting would require us to be able to query up a relationship (Opportunity -> Account) and then down from Account (Account -> AccountContactRelation if you have the allow contacts to be related to multiple accounts feature enabled, Account -> Contact otherwise), which isn't possible.

A second query, if you can afford to use another, would probably be the fastest way around this.

If your code is relying on being able to use simple dot-notation to traverse from Opp -> Account -> AccountContactRelation/Contact, then there's not going to be a way for you to avoid rewriting that.