0

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)

  • Have you considered building a custom Report Type of Cases with Case Comments and then building a Summary report to do the Case & Comment grouping for you? If you need to pull the report output into apex, that can be done too via the Reports & Dashboards API. – Mark Pond Apr 21 '17 at 18:47
2

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

1

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);
  • ah great suggestion. I am working through it right now – Olivia Apr 21 '17 at 18:24
  • you can refactor the code according to your need, but it this perfectly working at my DE – Santanu Boral Apr 21 '17 at 18:32
  • You know, there is an addAll method. – Adrian Larson Apr 21 '17 at 23:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.