SOQL: I need to display a Visualforce list of cases where each row can be expanded to display indirectly related cases

Question

So there are 3 case records types: A, B, and C.

A and C cases both have lookups to B cases, but otherwise have no direct relationship.

I need to display a VF list of A cases where each row can be expanded to display all of the C cases that also point to the same B cases.

Here's a basic visual representation

A1 -> B1 <- C1, C2, C3
A2 -> B2 <- C4, C5, C6
A3 -> B3 <- C7, C8, C9

So, I should see a list of A cases, and each entry can be expanded to display "related" C cases.

I've played around with maps and inner selects, but I am utterly stumped.

Answer 1

More step by step than navD87's approach:

public with sharing class MyController {

    public class CaseWrapper {
        public Case a {get; set;}
        public Case[] cs {get; set;}
        CaseWrapper(Case a) {
            this.a = a;
        }
    }

    public CaseWrapper[] getWrappers() {

        Set<Id> bIds = new Set<Id>();
        CaseWrapper[] wrappers = new CaseWrapper[] {};

        // Query the A Cases
        for (Case a: [
                select Name, CaseB__c
                from Case
                where RecordType.Name = 'A'
                and CaseB__c != null
                and ...
                order by Name
                ]) {
            wrappers.add(new CaseWrapper(a));
            bIds.add(a.CaseB__c);
        }

        // Query the B and C Cases
        Map<Id, Case> bs = new Map<Id, Case>([
                select Id, (
                        select Name
                        from CaseCRelationshipName__r
                        where RecordType.Name = 'C'
                        order by Name
                        )
                from Case
                where RecordType.Name = 'B'
                and Id in :bIds
                ]);

        // Add the C Cases to the wrapper
        for (CaseWrapper w : wrappers) {
            Case b = bs.get(w.a.CaseB__c);
            if (b != null) w.cs = b.CaseCRelationshipName__r;
            else ws.cs = new Case[] {};
        }

        return wrappers;
    }
}

with the Visualforce (without any expand collapse fancyness):

<apex:pageBlockTable value="{!wrappers}" var="wrapper">
    <apex:column value="{!wrapper.a.Name}"/>
    <apex:column>
        <apex:pageBlockTable value="{!wrapper.cs}" var="c">
            <apex:column value="{!c.Name}"/>
        </apex:pageBlockTable>
    </apex:column>
</apex:pageBlockTable>

Answer 2

You can query out all A and C cases at once, assuming you need to display all As with related Cs on the Visualforce page.

List<Case> allCases = [SELECT Id, RecordType.Name, B_Case_lookup FROM Case WHERE RecordType.Name == 'A' OR RecordType.Name == 'C'];
List<Case> Acases = new List<Case>();
Map<Id, List<Case>> cCasesBybCaseIds = new Map<Id, List<Case>>();    

//Get all the B lookup Ids from the A records
for(Case case : allCases) {
    if(case.RecordType.Name == A) {
        aCases.add(case);
    } else if(case.RecordType.Name == C && case.B_Case_lookup != null) {
        if(cCasesBybCaseIds.containsKey(case.B_Case_lookup)) {
            cCasesBybCaseIds.get(case.B_Case_lookup).add(case);
        } else {
            cCasesBybCaseIds.put(case.B_Case_lookup, new List<Case>{case});
        }
    }
}

You can further relate As to Cs directly by creating a map that maps A Case Ids to a list of C cases.

Map<Id, List<Case>> cCasesByACaseIds = new Map<Id, List<Case>>();
for(Case case : Acases) {
    if(cCasesBybCaseIds.containsKey(case.B_Case_lookup)) {
        cCasesByACaseIds.put(case.Id, cCasesBybCaseIds.get(case.B_Case_lookup));
    }
}