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);
1
  • 3
    You don't even reference the queried Attachment records. Just remove the query.
    – Adrian Larson
    Feb 8, 2018 at 1:32

2 Answers 2

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);
2
  • Hi, I would like to count the number of attachments for all Closed/withdrawn/cancelled cases
    – begginers
    Feb 8, 2018 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, 2018 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'));
}
3
  • 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, 2018 at 2:06
  • updated the code, basically use query in for loop, so that error will not occur related to querymore. Feb 8, 2018 at 2:10
  • the error still exists..
    – begginers
    Feb 8, 2018 at 3:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .