1

I have "Items" object with different statuses. I need to get the following output with single soql query.

Ordered Items: 10
Cancelled Items: 3
Reviewed Items: 2

VF Page and apex code below:

 Ordered Items: <apex:commandButton value="{!Count}"/>
 Cancelled Items: <apex:commandButton value="{!Count}" />
 Reviewed Items: <apex:commandButton value="{!Count}"/>


Public Integer Count(){
   List<Items__c> items = [SELECT Count(ID) FROM Items__c WHERE Status == 'Ordered' OR Status == 'Cancelled' OR Status == 'Reviewed'];
   return items.size();
}

With the above code ofcourse, I get the following output

Ordered Items: 15
Cancelled Items: 15
Reviewed Items: 15

Im calling the same method here, but I want to return differently based on the Status. I tried to use apex:param for the commandButton, but doesn't work.

Any ideas how to implement. [I want to use only 1 SOQL, with different SOQL and different methods, I can get what I want]

1
  • Does that code actually compile and run? Items__c is a custom object so the Status field should be custom as well. That means the field should be referenced as Status__c not Status
    – BarCotter
    Mar 30 '15 at 9:28
4

Even though you only have 1 SOQL statement in your code it gets executed 3 times, once for every call to Count().

If you use a map where the key is the status and the value is the count you can do something like the code below. This code will only execute the SOQL once.

 Ordered Items: <apex:commandButton value="{!statusCount['Ordered']}"/>
 Cancelled Items: <apex:commandButton value="{!statusCount['Cancelled']}" />
 Reviewed Items: <apex:commandButton value="{!statusCount['Reviewed']}"/>


public Map<String, Integer> statusCount {
    get {
        if (statusCount == null) {
            statusCount = new Map<String, Integer>();
            statusCount.put('Ordered', 0);
            statusCount.put('Cancelled', 0);
            statusCount.put('Reviewed', 0);
            for (Items__c i = [SELECT Status 
                               FROM Items__c
                               WHERE Status in ('Ordered', 'Cancelled', 'Reviewed']) {
                Integer count = statusCount.get(i.Status) + 1;
                statusCount.put(i.Status, count);
            }
        }
        return statusCount;

    }
    set;
}
2
  • Thanks for the lovely answer. Want to know, how safe is it to write a SOQL in a get;set; method?
    – sfdcFanBoy
    Mar 31 '15 at 3:14
  • 1
    It is safe because the SOQL will only execute when statusCount is null which will only ever happen once
    – BarCotter
    Mar 31 '15 at 8:32

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.