Hierarchial SOQL Query

Question

Does anyone know how to retrieve the hierarchical model of a account based on it's id?

I tried using this query but all i got is the first set of child nodes.

select a.Name,a.parentId,a.ownerid,a.id from  Account a where Parent.id ='00711314'

Answer 1

SOQL does not support hierarchical retrieval, you have to retrieve level by level, for each level generate a list of id's and then retrieve next level using in <list> where operator.

Keep in mind though that governor limitations apply and if you have large trees you'll run into a limit quite easily.

Answer 2

select a.Name,a.parentId,a.ownerid,a.id from  Account a where a.parentId ='00711314'

Answer 3

If you know there is a limited number of levels in your hierarchy, and you are querying a limited number of fields at each level, you can do something like this:

select Id, Name, ownerid, 
    parent.Id, parent.Name, parent.OwnerId, 
    parent.parent.Id, parent.parent.Name, parent.parent.OwnerId, 
    parent.parent.parent.Id, parent.parent.parent.Name, parent.parent.parent.OwnerId 
from Account where Parent.id ='00711314'

It's ugly, but allows you to get a fixed number of hierarchy levels in a single query.

Answer 4

As already stated you can not make use of hierarchical retrieval with SOQL. When I've needed this functionality with other objects (and when I know there are < 10k rows) I've selected all records, then used a map of lists to build up the hierarchy in memory instead:

map<id, list<id>> mapParentToChildren = new map<id, list<id>>();

for(Record__c [] sRecordArr : [select Id, Parent__c from Record__c limit 10000])
{
    for(Record__c sRecord : sRecordArr)
    {
        if(mapParentToChildren.get(sRecord.Parent__c) == null)
        {
            mapParentToChildren.put(sRecord.Parent__c, new list<id>{sRecord.Id});
        }
        else
        {
            mapParentToChildren.get(sRecord.Parent__c).add(sRecord.Id);
        }
    }
}

You can then make use of a recursive function to perform operations etc. on the data, for instance (untested):

// top level records will have a null parent, so be in the 'null' list
for(id idRecord : mapParentToChildren.get(null))
{
    PrintTree(idRecord, 0);
}

public void PrintTree(id idRecord, int iLevel)
{
    string strLevel = '*';

    for(integer i = 0; i < iLevel; i++)
    {
        strLevel += '*';
    }

    System.Debug(strLevel + idRecord);

    if(mapParentToChildren.get(idRecord) != null)
    {
        for(id idChild : mapParentToChildren.get(idRecord))
        {
            PrintTree(idChild, iLevel + 1);
        }
    }
}

This code is inefficient and untested (I've just written this version straight into the browser) but it should give you an idea of how you can deal with hierarchical data on the platform.