SOQL Inner query pageblocktable

Question

I have Application object and References as Child object.

I want to display list of Applications and corresponding "number of References records" for each application in VF Page. There is a status picklist field in References with values - Confirmed & Completed. I want to display something like below

  Application Name   Confirmed References   Completed References

           ABC                1                      1

           XYZ                2                      1

---

With the below query I'm able to get the total count of completed and confirmed references count together but not Individual count as above. How do I get the individual confirmed and completed count for each application in a simple way!

List<Application__c> ReviewList = [SELECT Id,Name,(SELECT Id,Name,Status__c FROM References__r WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed'))
                                       FROM Application__c];

---

The references__r.size below gives the total count of the records returned in the Inner Query (in this case count of confirmed & completed records together).

<apex:pageBlockTable value="{!ReviewList}" var="item">
   <apex:column headerValue="Application Name">
       <apex:outputText value="{!item.Name}" />
   </apex:column>
   <apex:column headerValue="References">
       <apex:outputText value="{!item.References__r.size}" />
   </apex:column>
</apex:pageBlockTable>

Answer 1

There can be two solutions

1. Apex based:- retrieve all related records in apex and then perform aggregation in apex.

Basically, extract all required records and their references (completed or confirmed)

[SELECT Id,Name,(SELECT Id,Name,Status__c FROM References__r WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed'))
                                       FROM Application__c];

Iterate over all records in apex code to calculate aggregated values

2. SOQL + Formula fields:- Create two formula fields (of type number) within "References__c" custom object
   • Complete = Should return 1 if "status__c = Complete"
   • Confirmed = Should return 1 if "status__c = Confirmed"

Use following SOQL query to extract application record along with completed and confirmed references (verify field names):-

SELECT Application__r.Name, SUM(IsComplete), SUM(IsConfirmed) FROM References__c WHERE (Status__c = 'Complete' OR Status__c = 'Confirmed')
GROUP BY Application__r.Name

Considering performance of formula fields in SOQL query, if data volume is high, I would recommend using first approach.