I have written a nested SOQL query for Invoice and Payment object.

select Id,Name, Zuora__External_Id__c, Zuora__Balance2__c, Zuora__PaymentAmount__c,(select Zuora__EffectiveDate__c FROM Zuora__Payments__r) from Zuora__ZInvoice__c

How can I assign the Zuora__EffectiveDate__c value to one of the fields on another object?


This style of coding - where the query is built into the for loops - and where you loop over all parent and child objects can be a good starting point:

for (Zuora__ZInvoice__c invoice : [
        SELECT Id, Name,
                (SELECT Zuora__EffectiveDate__c FROM Zuora__Payments__r)  
        FROM Zuora__ZInvoice__c
        ]) {
    // Add code that uses the invoice fields here
    for (Zuora__Payments__c payment : invoice.Zuora__Payments__r) {
        // Add code that uses the payment fields here

Note that you should add a WHERE clause to limit the query to the specific rows you need to process.

On the logic of this, consider that each parent object may have many child objects. So if you are trying to assign a value from the child objects to the parent object, you need to think about which child object "wins" and build that into your logic.


If you want to access child records, you can use following syntax:

List<Zuora__ZInvoice__c> invoices = 
  [SELECT Id,Name,Zuora__External_Id__c,
           (SELECT Zuora__EffectiveDate__c
            FROM Zuora__Payments__r)  
    FROM Zuora__ZInvoice__c];

Zuora__ZInvoice__c invoice = invoices.get(0);
DateTime child_effective_date = invoice.Zuora__Payments__r.get(0).Zuora__EffectiveDate__c;

Basically, child objects behave like a lists, so consider scenarios when there are no child records or couple of them.

  • 1
    Child records in a parent-child subquery behave like a list because it is actually a list (similarly, if you query for parent object fields from a child object, child.parent__r is itself an instance of the parent SObject). I don't think I've really been able to exploit that fact (except for one or two unit tests), but I still find it interesting to know. – Derek F Jun 29 '17 at 12:59
  • Subquery relationships behave somewhat like likes, and are assignable to those, but they are not equivalent to lists. In this example, you can do this: List<Zuora__Payments__c>myList = invoices[0].Zuora__Payments__r; myList.add(new Zuora__Payments__c()); but not invoices[0].Zuora__Payments__r.add(new Zuora__Payments__c()); Also, when doing the assignment to myList and adding a SObject to the list, the original list does not change, rather a shallow copy of the list in created in the assignment – Jochen Jun 29 '17 at 14:23

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.