2

I have a many-to many relationship between three objects. LGFProducts, PartsinProducts, and Parts. PartsinProducts is the junction object. I want to be able to get data like the names of the parts that are related to the selected LGFProduct.

I understand that parent-child relationship can only traverse one level down while grandchild-grandpa can travel 5 levels at most. What is the parent-child or child-parent in a many-to-many relationship?

I assumed that I should be traversing upwards from Parts to the junction object partsinproduct and then LGFProducts? But my friend who is not a Salesforce expert but has knowledge in CS said that I should do a parent-child query (of LGFProducts to partsinproduct) to child-parent relationship query (PartsinProduct to Parts). Or perhaps both of these options are wrong and another is appropriate.

Also, I am having a little trouble traversing for custom objects. I understand that I need to append the letter r instead of c but get confused. For example, this is my attempt at a three level upward traversal that fails.

SELECT Id, Name, Parts__r.PartsinProducts__r,LGFProducts__r.Name
FROM Parts__c

Also if three levels of upward traversal are needed, could one provide an example with custom relationships? As seen above, I am having difficulty successfully querying a three level upward traversal statement for custom objects.

This is my attempt at the parent-child relationship query of LGFProduct to PartsinProducts:

SELECT Name,
  (
    SELECT Name
    FROM PartsinProduct__r
  )
FROM LGFProduct__c 

I am then given this error:

enter image description here

2

Your relationship name is likely PartsinProducts__r, which would make your query:

SELECT (SELECT Name FROM PartsinProducts__r) FROM LGFProduct__c

The way you can check:

for (ChildRelationship relation : SObjectType.LGFProduct__c.getChildRelationships())
    if (relation.getChildSObject() == PartsinProduct__c.sObjectType)
        system.debug(relation.getRelationshipName());

Copy the output of the above script verbatim and replace PartsinProducts__r if that is not the correct relationship name.

  • Why does SELECT Name in the first part make the query not work. How do I know when to not have a field and when to have a field, as most examples in the Salesforce SOQL guide does have an field after the select. Also, for the second code script how do I test it? Do I put the code in the query editor? – user1470901 Aug 23 '16 at 22:48
  • @user Yeah don't test it, just run it once in Execute Anonymous so you can write the proper query. As for when to include/not include a field, you can think of the entire subquery ((SELECT Name FROM PartsinProducts__r)) as one field so to speak. You include sub-queries in your outer SELECT clause just like you would any other field. – Adrian Larson Aug 23 '16 at 22:49
  • Also one more question, in my specific case what should I be doing? An upward traversal of grandchild-grandpa or what my friend recommended a parent-child and then a child-parent query for my many to many relationship? – user1470901 Aug 23 '16 at 22:52
  • @user Hmm, it's hard to say what you're after exactly, but you can actually query parent data from the junction, e.g. (SELECT Parts__r.Name FROM PartsinProducts__r), assuming the lookup has an API Name of Parts__c. – Adrian Larson Aug 23 '16 at 22:58
  • We can chat if you're still confused. – Adrian Larson Aug 23 '16 at 23:12

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.