Tag Archives: SOQL

System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set.

For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}

In order to avoid System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop, make sure the sub query is limited as follows.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts LIMIT 200) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}

How to get picklist values based on record type in Visualforce page?

Here in below example I’ve a custom object “Customer__c”, which has two record types “HR” and “Marketing”.

Apex Class:

public with sharing class recordTypeSample{

    public String selectedRT {get;set;}
    public List<SelectOption> recordTypeList {get;set;}
    public Customer__c customer {get;set;}
    
    public recordTypeSample(){
    
        customer = new Customer__c();
        recordTypeList = new List<SelectOption>();
        getRecordTypeList();
    
    }
    
    public void getRecordTypeList(){
         
        List<RecordType> rtList = [SELECT Id,Name FROM RecordType WHERE SObjectType='Customer__c'];
        recordTypeList.add(new SelectOption('--None--', '--None--'));
        for(RecordType rt : rtList)
        {
            recordTypeList.add(new SelectOption(rt.Id, rt.Name));
        }
    }
    
     public void getPickListValues(){
     
        if(selectedRT != null){
            customer = new Customer__c(RecordTypeId = selectedRT);
        }
    }
}

Visualforce Page:

<apex:page controller="recordTypeSample">
    <apex:form >
        <apex:pageBlock >
            <apex:pageBlockSection >
                <apex:selectList value="{!selectedRT}" size="1" multiselect="false" label="Record Type" title="Record Type" id="recordTypes"> 
                    <apex:actionSupport event="onchange" action="{!getPickListValues}" reRender="categoryPicList" />
                    <apex:selectOptions   value="{!RecordTypeList}" /> 
                </apex:selectList>
                <apex:inputField id="categoryPicList" value="{!customer.Category__c}"/>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>

HR Record Type Picklist :

Marketing Record Type Picklist :