[SalesForce] SOQL for getting CaseComent for a case

I want to get the associated CaseComment's CommentBody for a list of cases. 

String queryFields = 'Id, Subject, CaseComment.CommentBody';
 List<Case> cases = Database.query('select '+ queryFields +' from Case');
 for(Case c: cases){
       System.debug(c.CaseComment.CommentBody);
 }

but this doesn't work as there is no reference in Case object for the CaseComment. CaseComment has the field parentId which stores id of associated Case. How can I get CaseComment. I don't want to query the CaseComment object for each case in a loop.

Best Answer

You must traverse the child relationship CaseComment, for example:

List<Case> cases = [Select (Select Id, ParentId, IsPublished, CommentBody, CreatedById, CreatedDate, SystemModstamp, LastModifiedDate, LastModifiedById, IsDeleted From CaseComments) From Case ];

for(Case c : cases)
{
    for(CaseComment comm : c.CaseComments)
    {
        System.debug(comm.CommentBody);
    }
}

This is because there are many Case Comments to one Case, i.e. a one to many relationship between case and comment as per the master/detail or lookup relationship.

You should consider using Eclipse schema viewer so that you can find these relationships out yourself, its a simple point/click interface so you can traverse them graphically.

Related Topic