6

I have the following data model: enter image description here

Now I would like to do the following:

By knowing zced__Interaction__c extract all the zced__Assessment__c.

Then for each of the extracted zced__Assessment__c I would like to extract all the zced__Assessment_Question__c with the following fields (the fields actually do not matter in the case I suppose) zced__Question_text__c and zced__Question_Instruction__c.

At the same time for each extracted zced__Question_Instruction__c I would like to extract all zced__Assessment_Answer__c with the following fields (the fields here do not matter as well I think) zced__Answer_Text__c, zced__Is_Correct__c, zced__Answer_Points__c.

So, after reading this post I come up with the following solution (which does not work and I do not know how to fix it):

    SELECT (
SELECT zced__Question_text__c, zced__Question_Instruction__c,
                    (
                            SELECT zced__Answer_Text__c, zced__Is_Correct__c, zced__Answer_Points__c
                            FROM zced__Assessment_Answer__c
                    )
                    FROM zced__Assessment_Question__c
                )
                FROM zced__Assessment__c
                WHERE zced__Interaction__c = :interactionId

What am I doing wrong here? The interactionId is checked to be correct.

I am stuck while trying to implement the first step of the solution:

first query at the zced__Assessment__c level

List<sObject> l = [
            SELECT Id, (SELECT Id FROM zced__Assessment_Question__c)
            FROM zced__Assessment__c
            WHERE zced__Interaction__c = :interactionId
        ];

It does not allow to query it this way. Why?

  • What is your exact use case or whats the functionality you want to achieve? – Santanu Boral Jul 13 '18 at 13:28
  • 2
    @SantanuBoral, I have an interaction on the front-end. And I would like to extract all assessment related data about the interaction from the back-end. I assure you that there is no x-y problem. – iloveseven Jul 13 '18 at 13:53
11

SOQL parent-child queries only support one level of relationship descent:

In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specifies Account, the SELECT clause can specify only the Contact or other objects at that level. It could not specify a child object of Contact.

The only way you'll be able to do this in a single query is to have the base query object be zced__Assessment_Question__c, filtering on zced__Assessment__r.zced__Interaction__c. That would allow you to pull in the fields you need from Assessment while also performing a parent-child query against Assessment Answer.

However, that approach may not meet your needs, because you're going to get back a List<zced__Assessment_Question__c> and have to postprocess in Apex to get back to a List<zced__Assessment__c>. It may be best to break this into two parent-child queries: first query at the zced__Assessment__c level, then iterate to gather the zced__Assessment_Question__c Ids in a Set, then perform a parent-child query against zced__Assessment_Question__c to get its children.

You can populate the latter query results in an Map<Id, zced__Assessment_Question__c> by doing

Map<Id, zced__Assessment_Question__c> childMap = new Map<Id, zced__Assessment_Question__c>(
    [SELECT zced__Question_text__c, zced__Question_Instruction__c,
            (
                 SELECT zced__Answer_Text__c, zced__Is_Correct__c, zced__Answer_Points__c
                 FROM zced__Assessment_Answer__c
            )
     FROM zced__Assessment_Question__c
     WHERE Id IN :questionIdSet]
);

The Map will then allow easy access to the grandchild records as you iterate over the children of each zced__Assessment__c from your top-level query.

Note that any time you perform a SOQL parent-child query, you must query the child by the relationship name, not by its own API name. For standard objects, this is often the plural of the child object name (SELECT Id, (SELECT Id FROM Contacts) FROM Account), while with custom objects and relationships it will often, but not always, be the plural child object name with __r instead of __c. Make sure to refer to the lookup or master-detail field itself to find the relationship name.

  • If you could, help me, please to tackle the your solution implementation issue. I updated the question. – iloveseven Jul 13 '18 at 13:55
  • See the new last paragraph of my answer for more on this. – David Reed Jul 13 '18 at 14:04
  • 1
    For custom it is often the pluralized child object name plus __r. – Adrian Larson Jul 13 '18 at 14:07

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.