[SalesForce] Incompatible types since an instance of List is never an instance of AggregateResult

I am trying to execute following apex class , But facing the following error Incompatible types since an instance of List is never an instance of AggregateResult.

I am using the below code:

public with sharing class Eisai_Banzel_Survey_Cls{

    public list<Schema.AggregateResult> listSurvey = new list<Schema.AggregateResult>();
    private static String UserRoleId = Userinfo.getUserRoleId();
    List<UserRole> listRole = new List<UserRole>();
    List<String> listRoleIds = new List<String>();

    public list<Survey_Target_vod__c> getSurveylist(){

        listRoleIds.add(UserRoleId );

        Set<Id> allSubRoleIds = getAllSubRoleIds(new Set<ID>{UserRoleId});

        //Get all the Surveys to be displayed that would be visible to the user as per role hierarchy and sorted according to terriotry

        listSurvey = [SELECT Status_vod__c, Territory_vod__c,Count(Id), User_vod__r.Name,User_vod__r.UserRoleId, User_vod__r.IsActive 
                      FROM Survey_Target_vod__c 
                      WHERE CreatedDate = THIS_YEAR 
                      AND (OwnerId =:Userinfo.getUserId() OR User_vod__r.UserRoleId IN :allSubRoleIds ) 
                      AND User_vod__r.IsActive = TRUE
                      GROUP BY Status_vod__c,Territory_vod__c,User_vod__r.Name,User_vod__r.UserRoleId, User_vod__r.IsActive
                      ORDER BY Territory_vod__c ASC NULLS FIRST];


        if(listSurvey.size()>0){
            return listSurvey;
        }        

        else return null;

        return null;
    }

    private static Set<ID> getAllSubRoleIds(Set<ID> roleIds) {

        Set<ID> currentRoleIds = new Set<ID>();

        // get all of the roles underneath the passed roles
        for(UserRole userRole :[select Id from UserRole where ParentRoleId 
                                IN :roleIds AND ParentRoleID != null])
            currentRoleIds.add(userRole.Id);

        // go fetch some more rolls!
        if(currentRoleIds.size() > 0)
            currentRoleIds.addAll(getAllSubRoleIds(currentRoleIds));

        return currentRoleIds;

    }

}

Facing the error in return listSurvey; :

if(listSurvey.size()>0){
    return listSurvey;
}

Best Answer

When you execute a query that includes a GROUP BY term it is always an array (list) of AggregateResult objects that is returned - see Working with SOQL Aggregate Functions.

So your method will have to return that type:

public AggregateResult[] getSurveylist() {
    ...
    return [SELECT Status_vod__c s, Territory_vod__c t,Count(Id) c, User_vod__r.Name n,User_vod__r.UserRoleId r, User_vod__r.IsActive a
                  FROM Survey_Target_vod__c 
                  WHERE CreatedDate = THIS_YEAR 
                  AND (OwnerId =:Userinfo.getUserId() OR User_vod__r.UserRoleId IN :allSubRoleIds ) 
                  AND User_vod__r.IsActive = TRUE
                  GROUP BY Status_vod__c,Territory_vod__c,User_vod__r.Name,User_vod__r.UserRoleId, User_vod__r.IsActive
                  ORDER BY Territory_vod__c ASC NULLS FIRST];
}

and any other code using the returned result will have to access the values using get(fieldName).

So to get the various values in Apex (where I've added an alias in the above query for each field):

for (AggregateResult ar: getSurveylist()) {
    String status = (String) ar.get('s');
    String territory = (String) ar.get('t');
    Integer count = (Integer) ar.get('c');
    ...
}
Related Topic