7

I have an object, Widget__c. This object is very simple, but it has a lookup to Widget__c as well, as each object can be nested within another. Now, I need to query and get the entire structure of Widget__c. I want to loop over a List of Widget__cs, which each have their own list of Widget__cs, which each have their own list of Widget__cs, all the way to the Nth level.

How would I go about doing this properly?

Below is something I have tried (not sure if it will help), but stopped working on it when I realized this might require a ridiculous amount of looping. I am hoping there is a better solution and I am missing something obvious. The idea is that I would loop over the widgets list and add the children, but then I would need to keep checking for more and more children as well as it progresses. Once I get a few levels deep, this is going to be incredibly inefficient.

Map<Id, Widget__c> parentWidgetMap = new Map<Id, Widget__c>([
    SELECT
        Id, Name, Widget__c 
    FROM
        Widget__c
    WHERE 
        Widget__c = NULL 
    LIMIT 40000
]);
List<Widget__c> widgets = parentWidgetMap.values();

Map<Id, Widget__c> childWidgets;
do{
    childWidgets = new Map<Id, Widget__c>([
        SELECT
            Id, Name, Widget__c 
        FROM
            Widget__c
        WHERE 
            Widget__c IN :parentWidgetMap.keySet() 
        LIMIT 40000
    ]);

    if(!childWidgets.isEmpty()){
        for(Widget__c childWidget:childWidgets.values()){

        }
    }
    parentWidgetMap = childWidgets;
}while(!childWidgets.isEmpty());
6
  • 1
    How many widgets will there ultimately be? If there were only a few you could query the whole lot and rebuild the relationship structure in code. Ref. I'm guessing from the LIMIT 40000 you are expecting lots of records. Jan 24, 2014 at 0:10
  • 1
    That said, I haven't found a nice way to handle recursive tree structures other than brute force and carefully monitoring the Limits object to avoid a LimitException. Jan 24, 2014 at 0:11
  • To be honest, I have no idea how many. It could be any amount. 40000 was an arbitrary number I used so the SOQL query has no chance of blowing up. Jan 24, 2014 at 0:12
  • 1
    Do these change hierarchy often? If not you could use a workflow rule or trigger to capture the final parent into another field on new/updated records (by copying from it's parent, or the parent if the parent's parent is null).
    – drakored
    Jan 24, 2014 at 0:20
  • That would simplify the query. Otherwise I think you're stuck rebuilding in apex like Daniel said.
    – drakored
    Jan 24, 2014 at 0:21

2 Answers 2

3

If you're starting from the top going down, you'll find you can only get one level deep in SOQL. Placing a hard limit on nesting (e.g. each widget can only reference a widget that does not reference a widget), you can get them in one call.

First, create a map to hold all the widgets:

Map<Id, widget__c> widgets = new Map<Id, Widget__c> { null => null };
Set<Id> usedIds = new Set<Id>();

Then, simply repeat this query until there are no "new" id values:

select id, widget__r.id, widget__r.widget__c,
           widget__r.widget__r.id, widget__r.widget__r.widget__c,
           widget__r.widget__r.widget__r.id, widget__r.widget__r.widget__r.widget__c
           widget__r.widget__r.widget__r.widget__r.id,
           widget__r.widget__r.widget__r.widget__r.widget__c
from widget__c
where widget__c in :widgets.keyset() or
      widget__r.widget__c in :widgets.keyset() or
      widget__r.widget__r.widget__c in :widgets.keyset() or
      widget__r.widget__r.widget__r.widget__c in :widgets.keyset() or
      widget__r.widget__r.widget__r.widget__r.widget__c in :widgets.keyset()

This gives you all the widgets five levels deep; you can add additional fields to the query to get their name and so on (not included for ... brevity). You can gather any widget ids that were not previously included in the first set, and repeat this query; each one will give you another five levels of branches in the tree.

You will still have to construct all of your data in Apex Code after the fact in order for this to work, and you'll want to be cognizant of sucking in too many rows/queries (you'll hit rows first in any complex tree). Your limit would be 50,000 rows or 500 tree levels deep.

The processor for that query might start out like this:

// Remember the ones used before
usedIds.addAll(widgets.keyset());
// Add all new ones, and run query from those
for(widget__c w1: lastQuery) {
    widgets.put(w1.id, w1);
    if(w1.widget__r != null) {
        widgets.put(w1.widget__c, w1.widget__r);
        if(w1.widget__r.widget__r != null) {
            widgets.put(w1.widget__r.widget__c, w1.widget__r.widget__r);
            if(w1.widget__r.widget__r.widget__r != null) {
                widgets.put(w1.widget__r.widget__r.widget__c, w1.widget__r.widget__r.widget__r);
                if(w1.widget__r.widget__r.widget__r.widget__r != null) {
                    widgets.put(w1.widget__r.widget__r.widget__r.widget__c, w1.widget__r.widget__r.widget__r.widget__r);
                }
            }
        }
    }
}

I think you should be able to piece all this together from here, as I'm not inclined to type the "w" word even once more at this point.

4
  • 3
    badgerbadgerbadger.com
    – eyescream
    Jan 24, 2014 at 6:04
  • This is an interesting idea, but it would get ugly quickly and as you noted it limits you to 5 rows. It is too bad there isn't a better way to handle a tree that doesn't have so many restrictions. Jan 24, 2014 at 21:08
  • 1
    Ironically, Oracle has a query like: select id, name, widget__c, level from widget__c start with id = 'ABCD' connect by prior id = widget__c that would allow you to instantly build an entire hierarchy in a single query regardless of the ordering, leaving the developer with the satisfying knowledge that they used the most optimal tools available. Unfortunately, salesforce.com doesn't allow that syntax, and it's a major limitation on all sorts of projects that would benefit from this query.
    – sfdcfox
    Jan 24, 2014 at 22:56
  • Yea, it really is disappointing that this structure is not something that is easy to query for. I tried a few more things and nothing felt right (at least nothing that felt like I wouldn't eventually run into some sort of other limitation). It seems like the solution is to limit the depth of the structure and use a query similar to yours. Thanks! Jan 27, 2014 at 13:33
0

If your tree is reasonably sized reference data and you can provide the following:

  • root node Id
  • type of object SObjectType
  • list of all fields SObjectField[]
  • the child relationship or ParentId

This technique works pretty well:

data = new HierarchyGenerator(objectType, objectFields, parentField, rootId)

It gives a traversable SObject with all the native child relationship fields populated, which can be navigated without any DTO etc. Just deserialize it: Json.deserialize(data, SObject.class)

public class HierarchyGenerator {
    
    String relationship;
    List<String> fields = new List<String>();
    JsonGenerator g = Json.createGenerator(false);
    Map<Id,SObject> id2parent = new Map<Id,SObject>();
    Map<Id,List<SObject>> id2children = new Map<Id,List<SObject>>();
    
    public HierarchyGenerator(SObjectType objectType, List<SObjectField> objectFields, SObjectField parentField, Id rootId) {
        
        //identify relationship field
        for (ChildRelationship cr : objectType.getDescribe().getChildRelationships())
        if (cr.getField() == parentField) relationship = cr.getRelationshipName();
        
        //stringify field names
        for (SObjectField objectField : objectFields)
        fields.add(String.valueOf(objectField));
        
        //query all parents and children
        List<SObject> parents = Database.query(''
            + 'SELECT ' + String.join(fields, ',') + ','
            + '(SELECT ' + String.join(fields, ',') + ' FROM ' + relationship + ')'
            + 'FROM ' + objectType + ' '
            + 'LIMIT 10000'
        );
        
        //associate collections by id
        for (SObject parent : parents) {
            id2parent.put(parent.Id, parent);
            id2children.put(parent.Id, parent.getSObjects(relationship));
        }
        
        //start with root
        traverseChildren(rootId);
    }
    
    void traverseChildren(Id parentId) {
        
        //grab hydrated record
        SObject parent = id2parent.get(parentId);
        List<SObject> children = id2children.get(parentId);
        
        if (children == null) {
            //nothing to do
            children = new List<SObject>();
        }
        
        //populate non-null keys
        g.writeStartObject();
        for (String key : fields) {
            Object value = parent.get(key);
            if (value == null) break;
            g.writeObjectField(key, value);
        }
        
        //imitate serialized relationship
        g.writeFieldName(relationship);
        g.writeStartObject();
        g.writeNumberField('totalSize', children.size());
        g.writeBooleanField('done', true);
        g.writeFieldName('records');
        g.writeStartArray();
        for (SObject child : children) traverseChildren(child.Id);
        g.writeEndArray();
        g.writeEndObject();
        g.writeEndObject();
    }
    
    override public String toString() {
        return g.getAsString();
    }
}

You must log in to answer this question.

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