Salesforce Apex和Visualforce

Question

我正在研究visualforce和a，我遇到了一种情况，我想将3种不同的表组合在一起，在这些表中，杂工的名字很常见。而且，handyman自定义对象上的specialities自定义字段是一个多选择列表，因此我不能直接从任何openorder或closeorders聚合查询中查询此字段，因为我们不能按特性(即多选择列表)进行分组。如果有人能帮我解决这个问题，我会很高兴的。

注意: orders自定义对象在杂工上有查找字段。

可视化页面代码

<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>

控制器类代码：

    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];
    }
}

Answer 1

订单->杂工在查找关系中是连接的还是主细节？点菜可以没有杂工吗？如果这个杂工会被删除--你是希望删除订单，还是想留下“孤儿”？

如果你把它作为主细节，那么你可以把两个汇总字段从订单转到杂工。然后你的控制器变得非常简单，SELECT Name, Specialities__c, OrdersCountOpen__c, OrdersCountClosed__c FROM Handyman__c ORDER BY Name，工作完成。

如果它必须继续寻找..。您可以制作2 Map<Id, Integer>，其中键是杂工的id，值是计数。或者使用子查询欺骗一点，以获得打开的订单，然后运行另一个已关闭的查询。

// 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')));
}

然后你的视觉力量就会像

<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>