function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
amyer2240amyer2240 

SOQL across multiple objects?

I'm trying to write a SOQL statement that will give me a count of opportunity objects grouped by contact role account. These fields aren't related to each other - the path is:

Opportunity -->
OpportunityContactRole -->
Contact -->
Account

In SQL I'd just join these tables, but I don't know how to do this in SOQL. Any ideas? Thank you!!
kevin Carotherskevin Carothers
Normally I'd say this;

SELECT ID,
   (SELECT Id,
      (SELECT Id from OpportunityContactRoles) from Opportunities ), (SELECT ID 
     FROM contacts) 
FROM Account

...But you'll likely get an error message like this;

MALFORMED_QUERY
SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

So... You'll likely have to write your query into two queries and put it into an apex class of some sort (controller, scheduled, batch, etc...)
amyer2240amyer2240
Before getting that error, I'm getting this one: Didn't understand relationship 'OpportunityContactRoles' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name.

Is OpportunityContactRoles not related to Opportunity? That would make this so much harder!