Save a nested query into a Map

Question

I have a nested query:

List<Case> comments = [SELECT (SELECT Id, CommentBody, CreatedDate, ParentId, CreatedById FROM CaseComments), AccountId, CaseNumber FROM Case WHERE AccountId = :strAccountId ORDER BY CaseNumber, CreatedDate DESC];

I want to save it into a map with the ParentId as the key, and the values are the rest of the Case fields.

Map<String, Case[]> caseGroups = new Map<String, Case[]>();

What I have so far:

public Map<String, Case[]> commentFinalList {
    get {
        Map<String, Case[]> caseCommentGroups = new Map<String, Case[]>();
        List<Case> comments = [SELECT (SELECT Id, CommentBody, CreatedDate, ParentId, CreatedById FROM CaseComments), AccountId, CaseNumber FROM Case WHERE AccountId = :strAccountId ORDER BY CaseNumber, CreatedDate DESC];
        for (Case c : comments) {
            if (c.ParentId != null) {
                if (caseCommentGroups.containsKey(c.ParentId)) {
                    caseCommentGroups.get(c.ParentId).add(c);
                }
                else {
                    caseCommentGroups.put(c.ParentId, new Case[]{
                        c
                    });
                }
            }
        }
        return caseCommentGroups;
    }
    set;
}

When I system debug this it does not recognize ParentId. FATAL_ERROR|System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Case.ParentId When I sub ParentId for CaseNumber, I only get one CaseComment per CaseNumber when there are actually a few CaseComments per CaseNumber.

I have to do a nested query because I need to pick CaseComments on a specific AccountId, but CaseComments does not have a AccountID field to filter with.

There are quite a few requirements and I am trying to hack my way around it. Is there a way to store a nested query field value into the key of a map? Another option would be a single query just on CaseComments but figure out how to filter only CaseComments with a specific AccountId.

Any suggestions on how to break this up?

UPDATE:

• Is there a way to walk back on a CaseComment query to filter by AccountId?
• How would you loop through a nested query list to pull out the fields of the inner query?

The Arrow Should Be The CaseNumber or the ParentID (disregard the ID in place now, it is just a place holder for structural purposes)

Under each ParentId should be a list of CaseComments that all are related to the same Account. (Disregard that the example says task, this picture was just for example purposes)

Answer 1

UPDATE

If you want to group CaseComment records by Case.AccountId, the following makes more sense:

Map<Id, List<CaseComment>> accountIdToComments = new Map<Id, List<CaseComment>>();
for (CaseComment comment : [
    SELECT Parent.AccountId, Other_Fields__c FROM CaseComment
    WHERE ...
]){
    Id accountId = comment.Case.AccountId;
    if (!accountIdToComments.containsKey(accountId))
        accountIdToComments.put(accountId, new List<CaseComment>());
    accountIdToComments.get(accountId).add(comment);
}

---

You've added the ParentId to your join on CaseComment, but not to your query on Case itself.

List<Case> records = [
    SELECT (SELECT ParentId, ... FROM CaseComments), ParentId, ...
    //             ^ not on Case object              ^ on Case Object
    FROM Case WHERE ...
];

Note you've confused yourself a bit by naming the collection comments. The collection returned by the query is not a List<CaseComment>. It's a List<Case>.

If you just want to be able to get the List<CaseComment> for a given Case Id, you don't need any for loop at all:

Map<Id, Case> records = new Map<Id, Case>([/*query*/]);
// intermediate code ...
Case parentCase = records.get(someCaseId);
if (parentCase != null)
{
    List<CaseComment> comments = parentCase.CaseComments;
    // additional processing
}

If you're actually trying to group the Case records themselves by their ParentId, then you do indeed need to add that field to your top level SELECT clause:

Map<Id, List<Case>> groupedCases = new Map<Id, List<Case>>();
for (Case record : [/*above query*/])
{
    if (!groupedCases.containsKey(record.ParentId))
        groupedCases.put(record.ParentId, new List<Case>());
    groupedCases.get(record.ParentId).add(record);
}

Answer 2

This is the logic to group CaseComments by AccountId.

Map<Id, List<CaseComment>> mapAccoutIdComment=new Map<Id, List<CaseComment>>();

List<Case> lstCase = [SELECT (SELECT Id, CommentBody, CreatedDate, ParentId, CreatedById FROM CaseComments), 
                       AccountId, CaseNumber 
                        FROM Case 
                        WHERE AccountId =: strAccountId
                        ORDER BY CaseNumber, CreatedDate DESC];

for(Case caseObj:lstCase)
{
    if(mapAccoutIdComment.containsKey(caseObj.AccountId))
    {

        if(caseObj.CaseComments.size()>0)
        {
            List<CaseComment> lst = mapAccoutIdComment.get(caseObj.AccountId);

            for(CaseComment comment:caseObj.CaseComments)
            {
                lst.add(comment);
            }
            mapAccoutIdComment.put(caseObj.AccountId,lst);
        }        
    }
    else
    {

        if(caseObj.CaseComments.size()>0)
        {
            List<CaseComment> lst = new List<CaseComment>();
            for(CaseComment comment:caseObj.CaseComments)
            {
                lst.add(comment);
            }
            mapAccoutIdComment.put(caseObj.AccountId,lst);
        }
    }    
}

System.debug('test=' + mapAccoutIdComment);

Answer 3

A large part of this question was figuring out what the right question was but with the help of you guys I got the answer!

I needed to loop through the CaseComments that was inside of the Case and then save that to the map. See below:

    caseCommMap = new Map<String, List<CaseComment>>();
    for ( Case c : [SELECT (SELECT Id, CommentBody, CreatedDate, ParentId, CreatedById FROM CaseComments), AccountId, CaseNumber FROM Case WHERE AccountId =: strAccountId ORDER BY CaseNumber, CreatedDate DESC]) {
        system.debug('this is c.id: ' + c.CaseNumber);
        for (CaseComment caseComm : c.CaseComments) {
            system.debug('    detail: ' + caseComm);
            if(caseCommMap.containsKey(c.CaseNumber)){
                caseCommMap.get(c.CaseNumber).add(caseComm);
            }
            else {
                caseCommMap.put(c.CaseNumber, new CaseComment[]{
                        caseComm
                });
            }
        }
     }

This way allows me to avoid having to make the map to find the AccountId and allows me to filter on the Case records.