Apex Development: Querying for child records more than 1 level deep - a JSON workaround

I recently had a situation where I wanted the ability to query the database for a list of records, and for each of those records I wanted to include data from some child records (from a master-detail relationship), and then on each of those I also wanted child records (from another master-detail relationship), and then finally I also wanted to include data from a related record (lookup relationship) for each of those child records.  So I wanted to write a top down query (by top down I mean selecting on the top level object) that gave me a List of (top level) SObjects that included data from child records up to 4 levels "deep".

An example would help.  Forget for a moment whether any standard Salesforce objects exist that we could reuse - this example is just for illustration purposes.

Let's say we have a data model where we're representing customers, orders, line items on those orders, and a specific product for each of those line items.  Our model might look like this:

In Salesforce terms, each of those relationships is a master-detail relationship except for the very last one, which is a lookup relationship from Line_Item__c to Product__c.

Let's say I want to get a list of customers.  And along with each customer I want all their orders.  And along with each of those orders I want all the line items for the orders and the name of the product for each line item.  Basically, I want to be able to do this:

// Given:
//   List<Id> myCustomerIds
//

List<Customer__c> myCustomers = getCustomersWithOrderInfo(myCustomerIds);

for (Customer__c currentCustomer : myCustomers) {
    System.debug('Customer: ' + currentCustomer.Name);

    for (Order__c currentOrder : currentCustomer.Orders__r) {
        System.debug('\tOrder: ' + currentOrder.Name);

        for (Line_Item__c currentLineItem : currentOrder.Line_Items__r) {
            System.debug('\t\t' + currentLineItem.Quantity__c + ': ' + currentLineItem.Product__r.Name);
        }
    }
}

The nested for loops show that I want to be able to traverse that data 3 levels deep.  Essentially we want the ability to traverse our SObjects through multiple levels of relationships:

Customer__c.Orders__r.Line_Items__r

 

ONLY 1 LEVEL DEEP IN NESTED QUERIES

Because you can only go 1 level deep with nested queries in SOQL we can't do this:

List<Customer__c> customers = [
    select Name,
           (select Name,
                   (select Quantity__c, Product__r.Name
                      from Line_Items__r)
              from Orders__r)
      from Customer__c
     where Id = :customerIds];

If you try that you'll be told:

SOQL statements cannot query aggregate relationships more than 1 level away from the root entity object.

TURN THE QUERY UPSIDE DOWN?

If you're only looking for Customers who have Orders with Line Items then you could "turn the query upside down" - meaning to query from the bottom up:

List<Line_Item__c> lineItems = [
    select Quantity__c,
           Product__r.Name,
           Order__r.Name,
           Order__r.Customer__r.Name,
           Order__r.Customer__r.Id
      from Line_Item__c
     where Order__r.Customer__r.Id = :customerIds];

But then you end up with a list of Line_Item__c objects, and that's not what I want.  Also, this query won't give you the same data as a top down query, since you're querying on the Line_Item__c object you won't get information about Customers without Orders, or Customers with Orders that don't have Line Items.

Still, assuming that I only want information about Customers with Orders, and that all Orders have at least 1 Line Item, I could work with this query and build my SObject hierarchy manually if I were able to set or add to the relationship fields on SObjects directly.  But we can't do that either:

// Given:
//   Customer__c cust
//   Order__c ord

// runtime error - "Field is not writeable: Orders__r"
cust.Orders__r = new List<Order__c>{ord};

// this compiles and runs, but it doesn't actually do anything
// the assertion will fail
cust.Orders__r.add(ord);
System.assertEquals(1, cust.Orders__r.size());

JSON TO THE RESCUE

One thing we can do, is deserialize JSON into SObjects.  And that deserialization process will work with more than 1 level of child relationships included in your JSON.  So by serializing the objects in the hierarchy and using them like building blocks to create some new JSON representing things in the way we'd like them, we can solve our problem.

First, let's take a look at how to "attach" a List<Line_Item__c> to an Order__c object using this "JSON serialize/deserialize" approach.

// Given:
//   Order__c ord
//   List<Line_Item__c> lineItems

String orderJson = JSON.serialize(ord);

// Build the json for the line items

// start with the related list field and opening object brace
String lineItemsJson = '"Line_Items__r" : {';

// the JSON.deserialize method expects a few informational fields
lineItemsJson += '"totalSize" : ' + lineItemsForOrder.size() + ',';
lineItemsJson += '"done" : true,';

// finally just serialize the records into json right inline
lineItemsJson += '"records" : ' + JSON.serialize(lineItems);
lineItemsJson += '}';

// insert the json for the list of line items into our existing json for the order
orderJson = orderJson.substring(0, orderJson.length()-1) + ',' + lineItemsJson + '}';

// deserialize back to Order__c object
Order__c orderWithLineItems = (Order__c) JSON.deserialize(orderJson, Order__c.class);

Since we're making use of the existing JSON serialize and deserialize methods within Apex, there's really not a whole lot of the JSON structure that we need to explicitly write in order to do this.  Furthermore, the JSON that we do need to write will be almost identical for any situation where we want to do this.  All we really need to know in order to "attach" a List of SObjects to a parent SObject is the name of the relationship on the parent-side.  So we could have a static method like this do it for us:

public static SObject attachListToParent(
    SObject parent,
    List<SObject> children,
    String parentSideRelationshipName)
{
    String targetJson = JSON.serialize(parent);

    String childrenJson = '"' + parentSideRelationshipName + '" : {';
    childrenJson += '"totalSize" : ' + children.size() + ',';
    childrenJson += '"done" : true,';
    childrenJson += '"records" : ' + JSON.serialize(children);
    childrenJson += '}';

    targetJson = targetJson.substring(0, targetJson.length()-1) + ',' + childrenJson + '}';

    return (SObject) JSON.deserialize(targetJson, SObject.class);
}

That's all there is to it.  Using the same approach, we can build our SObject structures as deep as we'd like.

A REUSABLE UTILITY TO DO THIS

Now, I hate writing code more than once, so we can take this a step further and write some code that will handle an arbitrary depth of relationships and wire up a bunch of SObject lists so that we have the top-down structure that we were looking for in our original scenario:

/**
 * Utility class containing a single public inner class (Node) and a single public
 * static method to help create top-down heirarchies of SObjects more than 1 level deep.
 *
 * @Author: Chad Pfrommer (Cloud Giants, Inc.) - chad@cloudgiants.com
 *
 * Copyright: Copyright 2016, Chad Pfrommer, Cloud Giants
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.If not, see <http://www.gnu.org/licenses/>.
 *
 * See <http://www.gnu.org/licenses/gpl.txt> for more info
 *
 */
public class SObjectAggregator {

    public class Node {
        public List<SObject> sObjects;
        public Node childNode;
        public String parentsideRelationshipName;
        public String childsideFieldName;
    }

    /**
     * Take a Node and generate a new List of SObjects with all descendent relationships
     * populated that are contained with the Node tree.
     */
    public static List<SObject> wireUpSObjectHeirarchy(Node topLevelNode) {
        if (topLevelNode.childNode == null) {
            // this is the end of our recursion.No children to add, so just return
            // the SObjects from this node.
            return topLevelNode.sObjects.clone();
        }
        else {

            Node childNode = topLevelNode.childNode;

            // Here's the recursive call:
            //first we go ahead and wire up things for the childNode by making a
            //recursive call to this method passing in the childNode
            List<SObject> wiredUpDescendents = wireUpSObjectHeirarchy(childNode);

            // now that we have all the descendents wired up, just get the current node's
            // SObjects with the wiredUpDescendents added to them
            List<SObject> parentSObjects = topLevelNode.sObjects;
            String parentsideRelationshipName = topLevelNode.parentsideRelationshipName;
            String childsideFieldName = childNode.childsideFieldName;
            return getParentObjectsWithChildObjectsOnThem(
                parentSObjects,
                wiredUpDescendents,
                parentsideRelationshipName,
                childsideFieldName);
        }
    }

    /**
     * Get a list of SObjects containing new instances for each parent SObject.Each
     * of those instances will have the child relationship populated with a clone of
     * each relevant child SObject found in the childSObjects list.
     */
    private static List<SObject> getParentObjectsWithChildObjectsOnThem(
        List<SObject> parentSObjects,
        List<SObject> childSObjects,
        String parentsideRelationshipName,
        String childsideFieldName)
    {

        List<SObject> newParentSObjectsList = new List<SObject>();

        // Loop through each parent and look for any child Objects that belong to the parent.
        // After we build the list of child objects for each parent, add the child objects on
        // to the parent SObject.
        for (SObject currentParentObject : parentSObjects) {

            List<SObject> childObjectsForCurrentParent = new List<SObject>();

            for (SObject currentChildObject : childSObjects) {
                if ((Id) currentChildObject.get(childsideFieldName) == (Id) currentParentObject.get('Id')) {
                    childObjectsForCurrentParent.add(currentChildObject);
                }
            }
            newParentSObjectsList.add(
                tackOnMasterDetailSObjects(
                    currentParentObject,
                    parentsideRelationshipName,
                    childObjectsForCurrentParent));
        }

        return newParentSObjectsList;
    }

    /**
     * Add a list of child SObjects to a target SObject using the specified relationshipName.
     *
     * This method just serializes the target SObject into JSON, modifies that JSON to add
     * the child objects in a way that the JSON deserialization method will understand, and
     * finally deserializes the new JSON into an SObject.
     */
    private static SObject tackOnMasterDetailSObjects(
        SObject targetSO,
        String relationshipName,
        List<SObject> realtedObjects)
    {

        String targetJson = JSON.serialize(targetSO);

        String realtedObjectsJson = '"' + relationshipName + '" : {';
        realtedObjectsJson += '"totalSize" : ' + realtedObjects.size() + ',';
        realtedObjectsJson += '"done" : true,';
        realtedObjectsJson += '"records" : ' + JSON.serialize(realtedObjects);
        realtedObjectsJson += '}';

        targetJson = targetJson.substring(0, targetJson.length()-1) + ',' + realtedObjectsJson + '}';
        System.debug(targetJson);

        return (SObject) JSON.deserialize(targetJson, SObject.class);
    }
}

APPLYING THIS TO OUR SCENARIO

Let's say that I want data for a list of Customers regardless of whether the Customers have Orders or the Orders have Line Items.  We can query that data in 3 queries, and then taking our new handy utility class/method we can build ourselves an top-down representation of that data in SObjects:

// Given:
//   List<Id> customerIds

// ******************************************
// Query for our data
// Make sure to query all the relationship fields for each level
// ******************************************
List<Customer__c> customerList = [
    select Name,
           Id
      from Customer__c
     where Id = :customerIds];

List<Order__c> orderList = [
    select Id,
           Name,
           Customer__r.Id
      from Order__c
     where Customer__r.Id = :customerIds];

List<Line_Item__c> lineItemList = [
    select Id,
           Name,
           Product__r.Name,
           Order__r.Id,
           Order__r.Customer__r.Id
      from Line_Item__c
     where Order__r.Customer__r.Id = :customerIds]; 

// ******************************************
// Build our SObjectAggregator.Node heirarchy
// ******************************************
SObjectAggregator.Node listItemsNode = new SObjectAggregator.Node();
listItemsNode.sObjects = lineItemList;
listItemsNode.childsideFieldName = 'Order__c';

SObjectAggregator.Node ordersNode= new SObjectAggregator.Node();
ordersNode.sObjects= orderList;
ordersNode.childNode = listItemsNode;
ordersNode.parentsideRelationshipName= 'Line_Items__r';
ordersNode.childsideFieldName= 'Customer__c';

SObjectAggregator.Node customersNode = new SObjectAggregator.Node();
customersNode.sObjects = customerList;
customersNode.childNode= ordersNode;
customersNode.parentsideRelationshipName = 'Orders__r';

// ******************************************
// User our utility method to get a top-down representation of
// our data.
// ******************************************
List<Customer__c> customersWithChildren = (List<Customer__c>) SObjectAggregator.wireUpSObjectHeirarchy(customersNode);

DISCLAIMER: APEX CPU TIME LIMIT EXCEEDED

Depending on how many levels of relationships and how many records per level you're trying to do this with you might run into CPU governor limits (Apex CPU time limit exceeded).  There's a lot of serializing and deserializing going on here, and each level of your hierarchy adds increases the complexity of that.

Therefore - if you plan on using this approach then be aware of this limitation and make sure that your data model and anticipated data set size won't trip the governor limits.  If it does you might be able to do things asynchronously (higher time limit), or maybe tune things so that you're not dealing with as much data.  When all else fails you'll just need to create some wrapper objects I suppose ;)