[SalesForce] Counting query results and displaying them in visualforce

I'm trying to count the number of each asset for an account and I'm not sure where I'm going wrong. I'm getting this error "Content cannot be displayed: Invalid field quantity for AggregateResult". Any ideas?

Controller Class

public class assetSummary {
    public account acc;

    public String name {get;set;}
    public integer quantity {get;set;}

    public assetSummary(string n,integer q){
        this.quantity=q;
        this.name=n;
    }
    public assetSummary(ApexPages.StandardController controller){
        this.acc = (Account)controller.getRecord();
    }

    public List<assetSummary> assetList = new List<assetSummary>();

    public list<assetSummary> getListOut(){
        AggregateResult[] ar = [SELECT Item__r.name name, count(id) FROM Serial_Tracker__c WHERE Account__c = :acc.ID GROUP BY Item__r.name];
        for (AggregateResult resultList : ar){
            assetList.add(new assetSummary(String.valueOf(resultList.get('name')),integer.valueOf(resultList.get('quantity'))));
        }
        return assetList;
    }
}

VF page`

<apex:page standardController="Account" extensions="assetSummary">   
<apex:pageblock title="Asset Summary">
    <apex:pageblocktable value="{!ListOut}" var="lo">
        <apex:column value="{!lo['name']}" headervalue="Name" >
        </apex:column>
        <apex:column value="{!lo['quantity']}" headervalue="quantity">
        </apex:column>
    </apex:pageblocktable>
</apex:pageblock>          
</apex:page>

Best Answer

It is best to create separate classes for things like controllers and data holders so something like this:

public class AssetSummaryController {

    // This inner class just holds the data needed for the page
    public class AssetSummary {

        public String name {get;set;}
        public integer quantity {get;set;}

        public AssetSummary(string name, integer quantity) {
            this.name = name;
            this.quantity = quantity;
        }
    }

    private account acc;

    public AssetSummaryController(ApexPages.StandardController controller){
        this.acc = (Account)controller.getRecord();
    }

    public list<AssetSummary> getListOut() {
        list<AssetSummary> assetList = new list<AssetSummary>();
        AggregateResult[] ar = [
                SELECT Item__r.name name, count(id) quantity
                FROM Serial_Tracker__c
                WHERE Account__c = :acc.ID
                GROUP BY Item__r.name
                ];
        for (AggregateResult resultList : ar){
            assetList.add(new AssetSummary(
                    String.valueOf(resultList.get('name')),
                    Integer.valueOf(resultList.get('quantity'))
                    ));
        }
        return assetList;
    }
}

which then allows the page to look like this:

<apex:page standardController="Account" extensions="AssetSummaryController">   
<apex:pageblock title="Asset Summary">
    <apex:pageblocktable value="{!ListOut}" var="lo">
        <apex:column value="{!lo.name}" headervalue="Name" >
        </apex:column>
        <apex:column value="{!lo.quantity}" headervalue="Quantity">
        </apex:column>
    </apex:pageblocktable>
</apex:pageblock>          
</apex:page>

The error you were seeing was that you had not provided the alias name "quantity" in the aggregate query. I strongly suggest formatting queries on multiple lines so it is easier to spot such omissions.

Related Topic