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)
Best Answer
UPDATE
If you want to group
CaseComment
records byCase.AccountId
, the following makes more sense:You've added theParentId
to your join onCaseComment
, but not to your query onCase
itself.Note you've confused yourself a bit by naming the collection
comments
. The collection returned by the query is not aList<CaseComment>
. It's aList<Case>
.If you just want to be able to get the
List<CaseComment>
for a givenCase
Id, you don't need anyfor
loop at all:If you're actually trying to group the
Case
records themselves by theirParentId
, then you do indeed need to add that field to your top levelSELECT
clause: