0

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);
  • 3
    You don't even reference the queried Attachment records. Just remove the query. – Adrian Larson Feb 8 '18 at 1:32
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);
| improve this answer | |
  • Hi, I would like to count the number of attachments for all Closed/withdrawn/cancelled cases – begginers Feb 8 '18 at 3:02
  • 3
    @begginers You will get more useful answers faster if you include in your question what you are trying to achieve. – Adrian Larson Feb 8 '18 at 3:20
0

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'));
}
| improve this answer | |
  • hi, I tried it and it returns this error: Line: 4, Column: 1 System.QueryException: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch – begginers Feb 8 '18 at 2:06
  • updated the code, basically use query in for loop, so that error will not occur related to querymore. – Santanu Boral Feb 8 '18 at 2:10
  • the error still exists.. – begginers Feb 8 '18 at 3:04

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.