Salesforce Apex And Visualforce

I'm working on visualforce and apex and I got a situation where I want to combine 3 different tables in which handyman's name is common. Also, the specialities custom field on handyman custom object is a multipicklist so I couldn't query this directly from any of openorders or closeorders aggregated query because we cannot group by specialities i.e. a multipicklist. I would be very glad if someone help me out with this.

Note: orders custom object has lookup field on handyman.

Visualforce Page Code

<apex:page standardController="Orders__c" extensions=“HandymanInfo">
  <apex:form>
   <apex:pageBlock>
     <apex:pageBlockSection columns="6" title=“ Handyman Tables">
          <apex:pageBlockTable value=“{!lsthandyman}” var=“h”>
            <apex:column value=“{!h.Name}">
                <apex:facet name="header”>Handyman Name</apex:facet>
        </apex:column>
            <apex:column value=“{!h.Specialities__c}" >
            <apex:facet name="header">Specialities</apex:facet>
            </apex:column> 
            </apex:pageBlockTable>
             
            <apex:pageblocktable value=“{!openorders}" var="oo">  
             <apex:column value="{!oo[’n']}"><apex:facet name="header”>Handyman Name</apex:facet></apex:column> 
             <apex:column value="{!oo[’sumopen']}"><apex:facet name="header”>Total Orders Opened</apex:facet></apex:column>                                                                 
            </apex:pageblocktable>
                
              <apex:pageBlockTable value=“{!closeorders}" var="co">
                  <apex:column value="{!co[’n']}"><apex:facet name="header”>Handyman Name</apex:facet></apex:column> 
                    <apex:column value="{!co[’sumclosed']}"><apex:facet name="header">Total Orders Closed</apex:facet></apex:column> 
            </apex:pageBlockTable>
                  
        </apex:pageBlockSection>
    </apex:pageBlock>
</apex:form>
</apex:page>

Controller Class Code:

    public class HandymanInfo {
    public List<Handyman__c> lsthandyman {get;set;}
    Public List<AggregateResult> openorders {get; set;}
    Public List<AggregateResult> close orders {get; set;}

    
public HandymanInfo(ApexPages.StandardController controller) {
 
    lsthandyman = [SELECT Name,Specialities__c FROM Handyman__c ORDER BY Name ASC];
   
        openorders = [SELECT Handyman__r.Name n, COUNT(Name) sumopen FROM Orders__c 
        WHERE ((Status__c='New') OR (Status__c ='Assigned’) OR (Status__c='Accepted') OR 
        (Status__c ='In Progress'))
        GROUP BY Handyman__r.Name
        ORDER BY Handyman__r.Name ASC];
       
        closeorders = [SELECT Handyman__r.Name n, COUNT(Name) sumclosed FROM Orders__c 
        WHERE ((Status__c='Completed') OR (Status__c='In Review’) OR (Status__c ='Paid'))
        GROUP BY Handyman__r.Name
        ORDER BY Handyman__r.Name ASC];
    }
}

Solution

Are orders -> handyman connected in lookup relationship or master detail? can order be without handyman? if the handyman would ever be deleted - would you expect to cascade delete the orders or would you want to keep the "orphans"?

If you have it as master-detail then you could make 2 rollup fields from order to handyman. And then your controller becomes very simple, SELECT Name, Specialities__c, OrdersCountOpen__c, OrdersCountClosed__c FROM Handyman__c ORDER BY Name, job done.

If it has to stay as lookup... You could make 2 Map<Id, Integer> where key would be the handyman's id and value would be the count. Or even cheat a bit with a subquery to get open orders and then just run another query for closed.

// make this your class variable
public Map<Id, Integer> closedOrders {get; private set;}

// and then
lsthandyman = [SELECT Name, Specialities__c,
        (SELECT Id FROM Orders__r WHERE Status__c IN ('New', 'Assigned', 'Accepted', 'In Progress'))
    FROM Handyman__c 
    ORDER BY Name ASC];
closedOrders = new Map<Id, Integer>();
// there's no guarantee all handymen have any closed orders and we don't want to display error if map key isn't found. So let's make sure we have something in there even if it's all zeroes
for(Handyman__c h : lsthandyman){
    closedOrders.put(h.Id, 0);
}
// and now let's get some actual closed order numbers
for(AggregateResult ar : [SELECT Handyman__c h, COUNT(Id) cnt
    FROM Orders__c 
    WHERE Status__c IN ('Completed','In Review’,'Paid')
    GROUP BY Handyman__c]){
    closedOrders.put((Id) ar.get('h'), Integer.valueOf(ar.get('cnt')));
}

And then your Visualforce will be something like

<apex:pageBlockTable value=“{!lsthandyman}” var=“h”>
    <apex:column header="Handyman Name" value=“{!h.Name}"/>
    <apex:column header="Specialities" value=“{!h.Specialities__c}"/>
    <apex:column header="# Open" value="{!h.Orders__r.size}" />
    <apex:column header="# Closed" value="{!closedOrders[h.Id]}" />
</apex:pageBlockTable>