[SalesForce] What does this error mean – SOQL statements cannot query more than 35 different parent types

From the Spring 15 Docs, I found this info under Relationship Query Limits:

No more than 35 child-to-parent relationships can be specified in a
query. A custom object allows up to 25 relationships, so you can
reference all the child-to-parent relationships for a custom object in
one query.

I am trying to run a query for Child Relationship objects of a CustomObject. Now I think it means that I cannot run query with 35 different lookup fields? I tried finding more details about it but I could not make clear sense of what the documentation want me to read.

When I open the Custom Object the "Limits" section reads that a Maximum of 40 "Custom Relationship Fields" can be created on that object. But then by the documentation it also means that I can only query 35 of the fields, and there are System defined Lookup fields as well in the object which I'm not counting here?

I'm really confused here how to overcome this since I could not clearly understand the meaning of the error. Has someone else here also experienced this kind of error in SOQL?

Source Documentation Link: here

Best Answer

Every time you go through a relationship to a parent, each unique relationship you reference counts as a relationship for the purposes of this limitation.

First, let's take a look at zero relationships:

SELECT AccountId, Name FROM Contact

Since there's no . notation, there's no parent relationships being specified, so the total number of relationships is zero. You can always get all of the ID values for all of the reference fields on a record at once.

So, what is this limitation talking about? It refers to using the . notation to gain access to parent data directly:

SELECT Account.Name, Account.AccountNumber, Name FROM Contact

In this case, we're using 1 relationship (Account) to get two pieces of data from the account plus some data from the contact.

Let's say we also wanted to get the account's parent account data. We can do this, too:

SELECT Account.Parent.Name, Name FROM Contact

However, we have had to walk through two relationships to get there (of the allowed 35). Basically, salesforce is telling us that we're allowed to use up to 35 joins in the SQL that will be built from our SOQL query.

Of course, there's other limitations that exist, too, such as the maximum number of parents you can reference in a chain, but aren't relevant to this particular limit.

If you get this error, you need to break up your query into multiple pieces. For example, query all of the fields on the record, then aggregate the ID values together and perform queries on the parent data, and repeat as necessary.

Data Model and Terminology

Given an object Parent__c and object Child__c with a lookup to Parent__c.

Field Name

API Name for the lookup field on Child__c, i.e. Child__c.Parent_Lookup__c

Parent Relationship Name

Use to reference fields on the parent from the child object, i.e. String parentName = child.Parent_Lookup__r.Name. Derived from field name.

Child Relationship Name

Used to reference children from the parent, i.e. List<Child__c> children = parent.Children__r;

Uniqueness Constraints

Child relationship names must be unique for the parent. Meaning that there can't be more than one lookup to the parent object (doesn't matter what the child object is) for each child relationship name.

Conversely, parent relationship names, must be unique for each child object. Meaning there can't be more than one lookup on the child object with the same name (doesn't matter what the parent object is).

Examples

So if you had two account lookups on the case object, you couldn't use Cases__r for both child relationship names and instead would need to change one to Cases1__r. You'll notice that Salesforce automatically adds a numeral for you if the parent object already has a child relationship name with the child object's plural label.

Discussion

If it helps, you can think about whether something would need to be unique or not, by thinking about whether it would lead to a SOQL statement that could be interpreted two different ways which would make life for the database pretty tough.

Gotchas - Non-Unique Parent and Child Relationship

As @bryan points in his answer Salesforce doesn't check that a field relationship doesn't have the same base as a child relationship. This can cause issues it can't tell if obj.Value__r is looking for the custom field Value__c or the child relationships Value__r. By convention child relationships are plural so you'd need to change the default value to a singular version for this to happen.

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

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.