[SalesForce] Multilevel SOQL Query (Parent to Children) more than 1 level

I am trying to get some related contact information from a SOQL query but have run into the error "SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object."

Here is my relationships;
Activity_List__c <– Activity_List_Contacts__c –> Contact (Junction Object)

Activities can have Sub Activities (Only one Level)
Multiple contacts can be assigned to each activity.

Here is my SOQL query :

select id, Name,
(select id, Name, 
    (SELECT Id, Contact_Name__r.FirstName,  Contact_Name__r.LastName FROM Activity_List_Contacts__r)
from Activity_Lists__r limit 50000),
(SELECT Id, Contact_Name__r.FirstName,  Contact_Name__r.LastName FROM Activity_List_Contacts__r) 
from Activity_List__c where Campaign__c ='701M0000000O7Ic' limit 50000

I know I need to use a map from reading the boards but am not sure how / what I need to do to accomplish it.

Thanks
Colsie

Best Answer

You are right.Subquery can go only one level deep. If I understand your object model correctly, you can get everything using two subqueries in one query like below,

SELECT Id, Name,
    (SELECT Contact_Name__r.FirstName, Contact_Name__r.LastName
        FROM Activity_Lists__r LIMIT 50000),
    (SELECT Id, Contact_Name__r.FirstName, Contact_Name__r.LastName
        FROM Activity_List_Contacts__r)
    FROM Activity_List__c
    WHERE Campaign__c IN :subCampaignIds
    OR Campaign__c = :CampaignId
    LIMIT 50000

First subquery will return child activity lists, second subquery will return Activity List contacts records from child relationship.

Related Topic