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
reuel3reuel3 

Junction Object Query

I'm trying to access a junction object on Contact. This object is to build a many-to-many relationship between Contact and Holiday__c. I drill down into each Account to display info from Opportunity and Contact. I can get to all the data except the junction object on a Visualforce page. My junction object is ContactHolidayAssociations__c

I get this error:

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

 

accounts = [select name, owner.name,
(select FirstName, LastName, Id, (Select Id, Name, Holiday__r.Holiday_Name__c From ContactHolidayAssociations__r) from Contacts), 
(select Name from Opportunities) from Account ];

 

 

Can someone suggest a way around this?

 

Thanks.

mtbclimbermtbclimber

Because you want to grab opportunities under the account you're going to have to separate this into two queries and map the results to something your page can access in the structure you need.

 

You can't go down more than one level nor can you go up and back down currently in the SOQL relationships functionality. If you didn't need to grab the opportunities you could target your query against Contact, grabbing each account for the contact and then just presenting the data in the fashion you require but since you want opptys as well I would just pull the opps and contacts then for each contact get the junction and put the results together.