[SalesForce] Save a nested query into a Map

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

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)

Best Answer

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);
}

Related Topic