function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
mnz123mnz123 

SOQL multilevel access to grandchild from a parent

Here I'm trying to display grandchild, child of a parent record. ORDER is the parent, order line is the child and shipment lines is the grandchild. I'm trying to display all of them in a nested table. So far I'm successful in displaying order lines for a single ORDER, but I'm messing up with the related shipment lines.

The controller
  
public class GE_PW_OrderTrackingList{
    public GE_PW_OrderTrackingList(ApexPages.StandardController controller) {

    }

    public list<wrapgroupwise> singlegroup;
  //  Map <String, Invoices__c> uniqueInvoices = new Map <String, Invoices__c>();
    public List<wrapgroupwise> getStartHere(){
    singlegroup= new List<wrapgroupwise>();
    List<order_lines__c> temol= [SELECT Id, name,calculated_estimated_delivery_date__c,order_number__c,status__c,planned_ship_date__c,material_name__c,material_no__c, quantity_ordered__c, material_group__c,(SELECT Id,Name,Order_Number__c,Actual_Delivery_Date__c, estimated_delivery_date__c,Shipment_Number__r.name, Shipment_Number__r.carrrier_name__c, Shipment_Number__r.carrier_website__c,Shipment_Number__r.tracking_number__c,quantity_shipped__c, material_group__c,order_lines__c  From shipment_lines__r ) From order_lines__c where number_of_order_lines__c=:ApexPages.currentPage().getParameters().get('id')];
    
    List<orders__c> tempacc=[SELECT Orders__c,CreatedBy.FirstName,CreatedDate, number_of_line_items__c, CurrencyIsoCode,account__r.name,contract__r.ContractNumber,customer_order_number__c,invoices__c,my_view__c,order_acknowlegment__c,order_date__c,order_value__c, order_no__c, requested_delivery_date__c,shipments__c,Id,Name,
                                 (SELECT Id, name,calculated_estimated_delivery_date__c,order_number__c,status__c,planned_ship_date__c,material_name__c,material_no__c, quantity_ordered__c, material_group__c From Order_Lines__r),
                          
                                 (SELECT invoices__r.invoice_no__c, CurrencyIsoCode,account__c,due_date__c,invoice_date__c,material_group__c,material_name__c,material_number__c,orders__c, order_lines__c, product_family__c,product_type__c,Id,Name,quantity__c, invoices__r.Name, invoices__r.status__c, invoices__r.invoice_value__c , invoices__r.due_date__c, invoices__r.number_of_days_past_due__c, invoices__r.invoice_date__c From Invoice_Lines__r)
                                  FROM orders__c where id=:ApexPages.currentPage().getParameters().get('id')];
     system.debug('-----tempacc-------'+tempacc);
     
     for(Integer i=0; i<tempacc.size() ; i++){
        List <order_lines__c> tempOrderLines = tempacc[i].order_lines__r;
        List <shipment_lines__c> tempShipmentLines = [SELECT Id,Name,Order_Number__c,Actual_Delivery_Date__c, estimated_delivery_date__c,Shipment_Number__r.name, Shipment_Number__r.carrrier_name__c, Shipment_Number__r.carrier_website__c,Shipment_Number__r.tracking_number__c,quantity_shipped__c, material_group__c,order_lines__c  From shipment_lines__c Where order_lines__c IN: tempacc[i].tempOrderLines];
        List <invoice_lines__c> tempInvoiceLines = tempacc[i].Invoice_Lines__r;
        
   //    uniqueInvoices.put(tempacc[i].id, tempInvoiceLines);   
               
         System.debug('temp order '+tempacc.size());
         System.debug('temp order lines'+tempOrderLines.size());
         System.debug('temp shipment lines'+tempShipmentLines.size());
         System.debug('temp invoice lines'+tempInvoiceLines.size());
         
         if(tempOrderLines.size()==0 ){/*&& tempShipmentLines.size()==0 && tempInvoiceLines.size()==0*/
                  singlegroup.add(new wrapgroupwise(tempacc[i],null,null,null)); 
            }
        else{
         
         if(tempShipmentLines.size()==0)
            {
              if(tempOrderLines[i].status__c=='Open')
               {
                   tempOrderLines[i].calculated_estimated_delivery_date__c=tempOrderLines[i].planned_ship_date__c;
               }
                                 
            singlegroup.add(new wrapgroupwise(tempacc[i],tempOrderLines,null,null)); 
                  
            }//if condition for tempShipmentLines size check    
         else
         {
         //Estimated Delivery Date done

           if(tempOrderLines[i].status__c=='Open')
           {
               tempOrderLines[i].calculated_estimated_delivery_date__c=tempOrderLines[i].planned_ship_date__c;
           }
           if(tempOrderLines[i].status__c=='Invoiced')
           {
               tempOrderLines[i].calculated_estimated_delivery_date__c=tempShipmentLines[i].estimated_delivery_date__c;
           }
           if(tempOrderLines[i].status__c=='Shipped but not billed')
           {
               tempOrderLines[i].calculated_estimated_delivery_date__c=tempShipmentLines[i].estimated_delivery_date__c;
           }           
         if(tempShipmentLines.size()==0)
         {
             singlegroup.add( new wrapgroupwise(tempacc[i],tempOrderLines,tempShipmentLines, null));
         }
             else{
                  singlegroup.add( new wrapgroupwise(tempacc[i],tempOrderLines,tempShipmentLines, tempInvoiceLines)); 
             }
          }//size check of temp shipping order  
         }
       }
    return singlegroup; 
   }
     public class wrapgroupwise
     {
        public List<order_lines__c> con {get;set;}
        public orders__c acc {get;set;}
        public List<shipment_lines__c> opp {get;set;}
        public List<invoice_lines__c> inv {get;set;}
    
         public wrapgroupwise( orders__c a , list<order_lines__c> c,list<shipment_lines__c> o, List<invoice_lines__c> i)
         {
            acc = a;
            con = c;
            opp = o;
            inv = i;
          } 
      } 

}
The visualforce: 
<apex:page standardcontroller="orders__c" extensions="OrderTrackingList" sidebar="true" title="Order Tracking" showHeader="true">
    <!--Order details-->
    <apex:sectionheader title="Order" subtitle="Detail"/>
    <!--   <apex:form style="align-center:center;"> -->
         <apex:pageBlock >                   
             <apex:repeat value="{!StartHere}" var="gp">                              
                 <apex:pageblocksection title="Order Details" showheader="true" collapsible="true" columns="2">
                      <apex:outputText value="{!gp.acc.Orders__c}" />
                      <apex:outputText value="{!gp.acc.number_of_line_items__c}" label="Total Materials"/>
                      <apex:outputText value="{!gp.acc.customer_order_number__c}"  label="Customer PO Number"/>
                      <apex:outputText value="{!gp.acc.account__r.name}"/>
                      <apex:outputText value="{!gp.acc.CreatedDate}"/>
                 </apex:pageblocksection>     

                 <!--Order Line details-->
                 
                <apex:pageblocksection title="Order Lines" showheader="true" collapsible="true" columns="1">
                <!-- rendered="{!NOT(ISNULL(gp.con))}" -->        
                   
                  <apex:pageBlockTable value="{!gp.con}" var="p" border="9">          
               
                    <apex:column colspan="7" > 
                        <apex:facet name="header">#</apex:facet>
                        <!--<apex:outputLink value="/{!p.id}"><apex:outputText value="{!p.Name}"/></apex:outputLink>-->
                        <apex:outputLink value="/{!p.id}"><apex:outputText value="{!p.Name}"/></apex:outputLink>
                    </apex:column>
                     <apex:column >
                         <apex:facet name="header">Material Name</apex:facet>
                         <apex:outputText value="{!p.material_name__c}"/>
                     </apex:column>                    
                     <apex:column >
                         <apex:facet name="header">Material No.</apex:facet>
                         <apex:outputText value="{!p.material_no__c}"/>
                     </apex:column>
                     <apex:column >
                         <apex:facet name="header">Quantity</apex:facet>
                         <apex:outputText value="{!p.quantity_ordered__c}"/>
                     </apex:column>                                          
                     <apex:column >
                         <apex:facet name="header">Status</apex:facet>
                         <apex:outputText value="{!p.status__c}"/>
                     </apex:column>
                     <apex:column title="Customer Requested Delivery Date">
                         <apex:facet name="header">RDD</apex:facet>              
                          <apex:outputText value="{!IF(OR(p.material_group__c = 'K', p.material_group__c = 'KL', p.material_group__c = 'KC', p.material_group__c = 'E', p.material_group__c = 'E1', p.material_group__c = 'E2', p.material_group__c = 'E3', p.material_group__c = 'W4', p.material_group__c = 'EKC', p.material_group__c = 'EC', p.material_group__c = 'EMC') ,'Week Of {0, date, MM/d/yyyy}','{0, date, MM/d/yyyy}')}" rendered="{!gp.acc.requested_delivery_date__c!=null}">
                         <apex:param value="{!gp.acc.requested_delivery_date__c}" /> 
                        </apex:outputText>     
                     </apex:column>
                     <apex:column title="Estimated Delivery Date at time of Order">
                         <apex:facet name="header">EDD</apex:facet>              
                          <apex:outputText value="{!IF(OR(p.material_group__c = 'K', p.material_group__c = 'KL', p.material_group__c = 'KC', p.material_group__c = 'E', p.material_group__c = 'E1', p.material_group__c = 'E2', p.material_group__c = 'E3', p.material_group__c = 'W4', p.material_group__c = 'EKC', p.material_group__c = 'EC', p.material_group__c = 'EMC') ,'Week Of {0, date, MM/d/yyyy}','{0, date, MM/d/yyyy}')}" rendered="{!p.planned_ship_date__c!=null}">
                          <apex:param value="{!p.planned_ship_date__c}" />                            
                        </apex:outputText>    
                     </apex:column>  
                    
                                       
                     <apex:column breakBefore="true" colspan="12">
                  
                 <!--Shipment Line details-->                    
                     <apex:pageblocksection title="Shipment Informations" collapsible="true" columns="1" rendered="{!NOT(ISNULL(gp.opp))}" >
                        <apex:pageBlockTable value="{!gp.opp}" var="q" >
                       
                              <apex:column> 
                                     <apex:facet name="header">Shipment Lines</apex:facet>
                                     <apex:outputLink value="/{!q.id}"><apex:outputText value="{!q.Name}"/></apex:outputLink>
                              </apex:column> 
                              <apex:column>
                                     <apex:facet name="header">Quantity Shipped</apex:facet>
                                     <apex:outputText value="{!q.quantity_shipped__c}"/>
                              </apex:column>  
                              <apex:column >
                                   <apex:facet name="header">Carrier Name</apex:facet>
                                   <apex:outputText value="{!q.Shipment_Number__r.carrrier_name__c}"/>
                              </apex:column>   
                              <apex:column >
                                  <apex:facet name="header">Carrier Website</apex:facet>
                                  <apex:outputLink value="{!q.Shipment_Number__r.carrier_website__c}" ><apex:outputText value="{!q.Shipment_Number__r.carrier_website__c}"/></apex:outputLink>
                              </apex:column>    
                              <apex:column >
                                  <apex:facet name="header">Tracking Number</apex:facet>
                                  <apex:outputText value="{!q.Shipment_Number__r.tracking_number__c}"/>
                              </apex:column>
                                                                               
                             <apex:column title="Estimated Delivery Date at the time of Shipment">
                                  <apex:facet name="header">Est Deliv Date</apex:facet>              
                                  <apex:outputText value="{!IF(OR(p.material_group__c = 'K', p.material_group__c = 'KL', p.material_group__c = 'KC', p.material_group__c = 'E', p.material_group__c = 'E1', p.material_group__c = 'E2', p.material_group__c = 'E3', p.material_group__c = 'W4', p.material_group__c = 'EKC', p.material_group__c = 'EC', p.material_group__c = 'EMC') ,'Week Of {0, date, MM/d/yyyy}','{0, date, MM/d/yyyy}')}" rendered="{!q.estimated_delivery_date__c!=null}">
                                  <apex:param value="{!q.estimated_delivery_date__c}" /> 
                                  </apex:outputText> 
                             </apex:column>     
                             <apex:column >
                                  <apex:facet name="header" >Actual Delivery Date</apex:facet>              
                                  <apex:outputText value="{!IF(OR(p.material_group__c = 'K', p.material_group__c = 'KL', p.material_group__c = 'KC', p.material_group__c = 'E', p.material_group__c = 'E1', p.material_group__c = 'E2', p.material_group__c = 'E3', p.material_group__c = 'W4', p.material_group__c = 'EKC', p.material_group__c = 'EC', p.material_group__c = 'EMC') ,'Week Of {0, date, MM/d/yyyy}','{0, date, MM/d/yyyy}')}"  rendered="{!q.Actual_Delivery_Date__c!=null}">
                                  <apex:param value="{!q.Actual_Delivery_Date__c}"/> 
                                  </apex:outputText> 
                             </apex:column>  
                       </apex:pageBlockTable>
                       </apex:pageblocksection>   
                       </apex:column>     
                     
                    </apex:pageBlockTable>
 
                 </apex:pageblocksection>
           <!--Invoice Line details -->

                <apex:pageblocksection title="Invoice Details" showheader="true" collapsible="true" columns="1" >
                    <apex:pageBlockTable value="{!gp.inv}" var="i">   
                    <apex:column colspan="7"> 
                        <apex:facet name="header">Invoice No.</apex:facet>
                        <apex:outputLink value="/apex/InvoiceList?id={!i.invoices__r.id}"><apex:outputText value="{!i.invoices__r.invoice_no__c}"/></apex:outputLink>
                    </apex:column>  
                    <apex:column >
                        <apex:facet name="header">Status</apex:facet>
                        <apex:outputText value="{!i.invoices__r.status__c}"/>
                    </apex:column>        
                    <apex:column >
                        <apex:facet name="header">Invoice Value</apex:facet>
                        <apex:outputText value="{!i.invoices__r.invoice_value__c}"/>
                    </apex:column>  
                    <apex:column >
                        <apex:facet name="header">Due Date</apex:facet>
                        <apex:outputText value="{0, date, MM/d/yyyy}"> 
                        <apex:param value="{!i.invoices__r.due_date__c}"/>
                        </apex:outputText>  
                    </apex:column>     
                    <apex:column >
                     <apex:facet name="header">Invoice Date</apex:facet>   
                        <apex:outputText value="{0, date, MM/d/yyyy}"> 
                        <apex:param value="{!i.invoices__r.invoice_date__c}"/>
                        </apex:outputText>  
                    </apex:column>  
                    </apex:pageBlockTable>                 
                 </apex:pageblocksection>
                 
             </apex:repeat>             
             </apex:pageBlock>

    <!-- </apex:form>  -->
 </apex:page>

 Any help would be great. Thanks

 
BalajiRanganathanBalajiRanganathan
You are using SOQL inside a for loop. you will hit a goveror limit very easily.
Looks like you are generating custom report. you can add readonly="true" to the apex:page element and you can use @readonly annotation to the
getter methods.
http://www.salesforce.com/us/developer/docs/pages/Content/pages_controller_readonly_context_methodlevel.htm

You can refer this template code below. you have to follow the similar approach to get the grandchild. 
List<Account> accList = [SELECT id,(SELECT id from Opportunities),(SELECT id from contact) FROM Account where id=:inputid];

Map<Id,Opportuniy> oppMap = new Map<Id,Opportuniy> ([SELECT id, (SELECT id from OpportunityLineItems) FROM Opportunity where accounid=:inputid]);

for (Account acc : accList) {
  for (Opportunity opp : acc.Opportunities) {
     opp.OpportunityLineItems = oppMap.get(opp.id).OpportunityLineItems;
  }   
}

 
mnz123mnz123
Thanks for the tip Bala. I'm trying to get order, orderlines and shipment in a single nested table. Shipment lines are repeating over.
BalajiRanganathanBalajiRanganathan
1) you should not put the select query for tempShipmentLines inside for loop. it should be outside of for loop
    ie select fields from From shipment_lines__c Where order_lines__r.Order__C = : id.

2) you have to revise your wrapper so that it will be a flat list as you want to see the output.
  
public class wrapgroupwise     {
        public order_lines__c con {get;set;}
        public orders__c acc {get;set;}
        public shipment_lines__c opp {get;set;}
        public invoice_lines__c inv {get;set;}
  }