4

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

3
  • 1
    What is {1}? Some sort of merge typo?
    – Adrian Larson
    May 13 '15 at 16:20
  • Take a gander at the documentation for Relationship Queries. You can only query one level deep.
    – Adrian Larson
    May 13 '15 at 16:23
  • {1] is a field list that I use regularly. May 13 '15 at 17:28
1

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.

1
  • Thanks Victor - I've just checked the initial SOQL Query and it seems there was a slight issue.... I've edited the SOQL query at the top. May 13 '15 at 17:34
0

Just following up on this... so as discussed it's not possible to drill down to the 3rd level of children with a single SOQL query. My solution was to create a separate SOQL query and put these into map.

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.