[SalesForce] Need help on SOQL to query all related child case records

I can query the child cases for current case by something like below,

list<case> parentids=trigger.new

select id,status from case where parentid=:parentids

What if the children have more children? Does it make sense to have more than one level hierarchical relationship? Should I use lookup filter on parent case to allow only parent cases linked to child case and not child to child relationship? What's the best practice?

Is that possible to query all cases related to parent case (child, grand child etc)? Can somebody give me an idea to retrieve all related records?

Best Answer

The basic idea is to create a formula for Ultimate Parent

BLANKVALUE(Parent.Parent.Parent.Parent.ParentId,
    BLANKVALUE(Parent.Parent.Parent.ParentId,
        BLANKVALUE(Parent.Parent.ParentId,
            BLANKVALUE(Parent.ParentId,
                BLANKVALUE(ParentId, Id)
))))

Then, you can get your relevant cases as follows:

Set<Id> parentids = new Set<Id>();
for (Case newCase : trigger.new) parentIds.add(newCase.parentId);
List<Case> relevantCases = [
    SELECT Id FROM Case
    WHERE Ultimate_Parent__c
    IN (SELECT Ultimate_Parent__c FROM Case WHERE Id IN :parentids)
];

You might be able to use recalculateFormulas() to get the values without doing an inner-join-sub-select, but there is a known issue dealing with cross-object logic, so I have my doubts it would work.

Update

The recalculateFormulas method does give you the correct value, but also causes an exception if you execute it on the trigger records:

System.UnexpectedException: Unable to create/update fields: LastModifiedDate, IsDeleted, SystemModstamp, CreatedById, CreatedDate, LastModifiedById.

Related Topic