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

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 22:58
  • We can chat if you're still confused.
    – Adrian Larson ♦
    Aug 23, 2016 at 23:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .