7

I have a SOQL query in a recursive FOR loop. It didn't give me SOQL limit issues on my test data, but apparently we have a managed package whose Apex is hitting the limit due to this code.

    public static void Get_Children(ID mainID, List<ID> ID_Group){        
    for(Account child :[SELECT ID, ParentID FROM Account WHERE ParentID = :mainID]){
        if(child.ParentId == mainID){ /*IF clause to make sure SOQL returned an actual account*/
            ID_Group.add(child.id);
            Get_Children(child.id, ID_Group);
        }
    }        
}

My understanding is that this SOQL query fires once for each child account in an account hierarchy.So if I have a parent account with two children, each of which have two children, this method will use a SOQL query five times to traverse them all (I think).

Thing is, this method is part of a larger function that triggers on changes to opps, and runs this code for every account whose had an opp changed - so if opps are changed en masse (as done by this managed package), this code runs to find the account hierarchies of all those related accounts, which I can see will quickly hit the 20 SOQL query limit.

Is there a way for me to avoid SOQL in this recursive method? Or do I need to reevaluate the trigger itself, and think about making this a periodically run batch Apex thing (which I know nothing about)?

1 Answer 1

6

Unfortunately querying hierarchies can be kind of a pain, and you're current approach scales linearly with the hierarchy size, which means assuming no other code is sharing your limits, you can deal with a max hierarchy size of 100. You'll need to re-architect this method to remove the recursion for the query portion.

The basic approach is to query to every account that has that parent at any level, and then build a map by parent. Then you can recurse down from the parent to build out a list of childs.

Here's an example from an old project

/*
Developer: Ralph Callaway <[email protected]>
Description:
    Representation of an account hierarchy.
*/
public class AccountHierarchy {

    /* Variables */

    private Boolean accountsLoaded = false;
    private Boolean hierarchyLoaded = false;
    private Id refId;
    private Set<String> queryFields = new Set<String>();
    private static Set<String> alwaysQueryFields = new Set<String>{ 'name', 'parentid' }; 

    /* Properties */

    private List<Account> accounts { 
        get {
            if(!accountsLoaded) {
                loadAccounts(); 
            }
            return accounts;
        }
        set;
    }

    public Integer limitAmt { get; set; }

    public String whereClause { 
        get;
        set {
            if(whereClause != value) {
                whereClause = value;
                accountsLoaded = false;
                hierarchyLoaded = false;
            }
        } 
    }

    public Node root {
        get {
            if(whereClause != null) {
                root = null;
            } else if(!hierarchyLoaded) {
                buildHierarchy();
            }
            return root;
        }
        private set;
    }

    /* Constructor */

    public AccountHierarchy(Id refId) {
        this.refId = refId;
    }

    /* Public Methods */

    public void addQueryField(String field) {
        if(field != null) {
            Boolean newField = queryFields.add(field.toLowerCase());
            if(newField) {
                accountsLoaded = false; 
                hierarchyLoaded = false;
            }   
        }
    }

    // returns by value
    public Set<String> getQueryFields() {
        return queryFields.clone();
    }

    public List<Account> toAccountList() {
        return (root == null) ? accounts : root.toAccountList();
    }

    public List<Id> toIdList() {
        List<Id> idList = new List<Id>();
        if(root == null) {
            Map<Id, Account> tempMap = new Map<Id, Account>(accounts);
            idList.addAll(tempMap.keySet());
        } else {
            idList = root.toIdList();
        }
        return idList;
    }

    public List<Node> toList() {
        return (root == null) ? new List<Node>() : root.toList();
    }

    // returns true if value present, false if value not found
    public Boolean removeQueryField(String field) {
        if(field != null) {
            field = field.toLowerCase();
            if(queryFields.contains(field)) {
                queryFields.remove(field);
                return true;
            }
        }
        return false;
    }

    public void setRefToTop() {
        refId = getTopId(refId);
    }

    /* Static Public Methods */

    public static Id getTopId(Id startId) {
        Account account = [
            select
                  parentId
                , parent.parentId
                , parent.parent.parentId
                , parent.parent.parent.parentId
                , parent.parent.parent.parent.parentId
                , parent.parent.parent.parent.parent.parentId
            from Account
            where id = :startId
        ];
        if(account.parent.parent.parent.parent.parent.parentId != null) {
            return account.parent.parent.parent.parent.parent.parentId;
        } else if(account.parent.parent.parent.parent.parentId != null) {
            return account.parent.parent.parent.parent.parentId;
        } else if(account.parent.parent.parent.parentId != null) {
            return account.parent.parent.parent.parentId;
        } else if(account.parent.parent.parentId != null) {
            return account.parent.parent.parentId;
        } else if(account.parent.parentId != null) {
            return account.parent.parentId;
        } else if(account.parentId != null) {
            return account.parentId;
        } else {
            return account.id;
        }
    }

    /* Support Methods */

    private void buildHierarchy() {
        hierarchyLoaded = true;

        // bail if we don't have any accounts to build
        if(accounts.isEmpty()) return;

        // query all accounts down the hierarchy and organize by their parent
        Map<Id, List<Node>> nodeByParent = new Map<Id, List<Node>>();
        for(Account account : accounts) {
            if(!nodeByParent.containsKey(account.parentId))
                nodeByParent.put(account.parentId, new List<Node>());
            Node accountNode = new Node(account);
            if(account.id == refId)
                root = accountNode; 
            nodeByParent.get(account.parentId).add(new Node(account));
        }

        // build the hierarchy one level at a time
        root.depth = 1;
        recursiveNodeBuild(root, nodeByParent);
    }

    private String buildQuery() {
        String selectFields = '';
        Set<String> allFields = new Set<String>();
        allFields.addAll(alwaysQueryFields);
        allFields.addAll(queryFields);
        for(String field : allFields) {
            selectFields += ', ' + field;
        }
        selectFields = selectFields.substring(2);
        String innerWhereClause = ''
            + ' where (id = \'' + refId + '\''
            + ' or parentId = \'' + refId + '\''
            + ' or parent.parentId = \'' + refId + '\''
            + ' or parent.parent.parentId = \'' + refId + '\''
            + ' or parent.parent.parent.parentId = \'' + refId + '\''
            + ' or parent.parent.parent.parent.parentId = \'' + refId + '\''
            + ' or parent.parent.parent.parent.parent.parentId = \'' + refId + '\')';

        String queryString = 'select ' + selectFields + ' from Account' + innerWhereClause;

        if(whereClause != null) {
            whereClause = whereClause.toLowerCase();
            whereClause = whereClause.replace('where', '');
            queryString += ' and (' + whereClause + ')';
        }

        queryString += ' order by name asc';

        if(limitAmt != null) {
            queryString += ' limit ' + limitAmt;
        }

        return queryString;
    }

    private void loadAccounts() {
        accountsLoaded = true;
        accounts = (List<Account>) Database.query(buildQuery());
    }

    private void recursiveNodeBuild(Node currentNode, Map<Id, List<Node>> nodesByParent) {
        if(nodesByParent.containsKey(currentNode.id)) {
            for(Node child : nodesByParent.get(currentNode.id)) {
                child.depth = currentNode.depth + 1;
                currentNode.children.add(child);
                recursiveNodeBuild(child, nodesByParent);
            }
        }
    }

    /* Inner Classs */

    public class Node {

        // variables
        public Integer depth { get; set; } // root node has depth of 1
        public String name { get; private set; }
        public String id { get; private set; }
        public Account record { get; set; }
        public List<Node> children { get; set; }

        // constructor
        public Node(Account record) {
            this.record = record;
            name = record.name;
            id = record.id;
            depth = -1; // -1 indicates depth has not been set
            children = new List<Node>();
        }

        // methods
        public List<Account> toAccountList() {
            List<Account> accountList = new Account[] { record };
            for(Node child : children) {
                accountList.addAll(child.toAccountList());
            }   
            return accountList;
        }

        public List<Id> toIdList() {
            List<Id> toIdList = new Id[] { id };
            for(Node child : children) {
                toIdList.addAll(child.toIdList());
            }
            return toIdList;
        }

        public List<Node> toList() {
            List<Node> nodeList = new Node[] { this };
            for(Node child : children) {
                nodeList.addAll(child.toList());
            }
            return nodeList;
        }
    }
}
5
  • 1
    Ralph, looking through your code it seems like you're doing basically the same thing as me, but instead of querying only the immediate child/parent relationship (which would require a new query for every tier), you're taking advantage of the 5-level deep maximum for relationship lookups (which would only require a new query for every five tiers). Do I have this right?
    – smohyee
    Oct 25, 2013 at 17:26
  • Exactly, your way is much more elegant, but SOQL queries being expensive you've got to minimize them at all costs. Fortunately, you still get to keep the recursion when building out the hierarchy. Oct 25, 2013 at 17:29
  • OK cool, I think I'll give that a try then. Thanks for the example! Also, I've commented on an existing idea related to this asking for a system method that would return all children accounts, so we don't have to deal with this stuff: success.salesforce.com/…
    – smohyee
    Oct 25, 2013 at 17:31
  • I'm going there to vote right now!
    – sfdcfox
    Oct 25, 2013 at 17:44
  • I should note, if you try the query @Ralph provided in the developer consle's query editor, you'll only see the first level deep... Jan 9, 2015 at 17:08

You must log in to answer this question.

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