Need help on SOQL to query all related child case records

Question

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?

Answer 1

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.

Answer 2

You could do something like this. I recently learned about making custom object classes. I do think that having multiple children can be useful sometimes. I used items like this to build tree structures on visualforce pages. Hopefully this will be useful to you.

//declare your new object class
public class YourObject{
    //what is your object made out of?
    public Story__c theStory{get;set;}
    public list(Task__c) theTasks{get;set;}
}

//create method which returns a list of objects of your new class
public List(YourObject) getYourObjects{
    //the returning list
    public list(YourObject) objectList = new list(YourObject);

    //list of stories you wish to use
    public list(Story__c) storyList = new List(Story__c);
    storyList = [SELECT Name, Id FROM Story__c];

    //list of all Tasks
    list(Task__c) taskList = new List(Task__c);
    taskList = [SELECT Name, Story__c FROM Task__c]

    //now it's time to make some objects
    // for each story in your list
    for (Story__c story : storyList){

        //make a new object
        YourObject thing = new YourObject();
        //add the story to your object
        thing.theStory = story;

        //create the list of tasks related to your story
        list(Task__c) newTaskList = new List(Task__c);
        for (Task__c task : taskList){
            if (task.Story__c == story){
            newTaskList.add(task);
            }             
        }

        //make that list the thing's theTasks
        thing.theTasks = newTaskList;

        //add your thing to your list of objects
        objectList.add(thing);
    }
    //return your object list
    return objectList;
}

Be sure to use lists or sets to avoid using soql queries inside of for loops.