System.LimitException

Question

I run this code in Developer console, Execute anonymous. How can I resolve this

Limit exception : Line: 1, Column: 1
System.LimitException: Too many query rows: 50001

I definitely have more than 50000 attachments. Any ideas would be highly appreciated.

   Map<ID,Case> CaseMap =  new Map<ID,Case> ([SELECT Id, Status FROM Case]);
List<Attachment> allAtt = [Select Id, ParentId FROM Attachment];

Integer closed = 0;
Integer withdrawn = 0;
Integer cancelled = 0;

for(Attachment att : allAtt){
    Case getcase = CaseMap.get(att.ParentId); 

        if(getcase.Status == 'Closed'){
        closed = closed + 1;
        }
        else if(getcase.status == 'Withdrawn'){
            withdrawn = withdrawn + 1;
        }
        else if(getcase.status == 'Cancelled'){
            cancelled = cancelled + 1;

        }      
}

system.debug('CLOSED Attachment count' + ' ' +  closed);
system.debug('WITHDRAWN Attachment count' + ' ' +  withdrawn);
system.debug('CANCELLED Attachment count' + ' ' +  cancelled);

Answer 1

If you want to filter on records which have an Attachment, you can do so using a Left Inner Join.

List<Case> casesWithAttachment = [
    SELECT Status FROM Case WHERE Id IN (
        SELECT ParentId FROM Attachment
    )
];

You have a couple options for how to use this information, but the important point is that rows pulled in using this sort of join do not count against your query rows governor limit.

I recommend you include your child records using a Left Outer Join (these rows do count but you only get those you care about):

Map<String, Integer> statusToAttachmentCount = new Map<String, Integer>();
for (Case record : [
    SELECT Status, (SELECT Id FROM Attachments) FROM Case WHERE Id IN (
        SELECT ParentId FROM Attachment
    )
]){
    Integer count = statusToAttachmentCount.get(record.Status);
    if (count == null) count = 0;
    statusToAttachmentCount.put(record.Status, count + record.Attachments.size());
}
system.debug(statusToAttachmentCount);

Answer 2

According to the logic of your code you are trying to find the count of cases, grouped into the status, where cases have attachments.

Here is the way to do as Aggregate query is more efficient to retrieve count:

List<String> caseIds = new List<String>();

//gather caseIds into a list
for(AggregateResult result:[SELECT ParentId,COUNT(Id) 
                                FROM Attachment
                                WHERE Parent.Type = 'Case'
                                GROUP BY ParentId])
{
    caseIds.add((String) result.get('ParentId'));
}

//pass caseIds and count based on status
List<AggregateResult> caseResults = [SELECT Status, COUNT(Id) countId
                                     FROM Case
                                     WHERE Status IN ('Withdrawn','Closed','Cancelled')
                                     AND Id IN:caseIds
                                     GROUP BY Status];

for(AggregateResult result:caseResults)
{
    System.debug('Status=' + (String)result.get('Status') + '----' + (Integer)result.get('countId'));
}