2

I have created a custom report for pulling quite a bit of data from Salesforce. I have a special circumstance that requires a VF component and a 2nd Class to get some data due to the way the Objects are related using a Joint Object, Many to Many relationship. When I go and run the report it gives me a SOQL query limit error. Without the 2nd class it works just fine. I used the Dev Console and found that only 2 SOQL queries run when it is just the primary Class but 101 run when I add in the 2nd class. I am not sure what is causing this. Any help would be appreciated. Thanks so much. I have added all the data below hope it is not too much. Thanks!

Main VF Page:

<apex:page standardController="Case__c" extensions="MonthlySeizureController">
<style type="text/css">
    table.myTable{
        border-collapse: collapse;
    }
    table.myTable td{
        border: 1px solid black;
        padding: 5px;
    }
    table.myTable th{
        border: 2px solid black;
        padding: 5px;
    }
    a.linkAsBtn,
    a.linkAsBtn:hover {
        text-decoration: none;
        padding: 3px 6px;
    }
</style>
<apex:form >
<apex:panelGrid columns="8" id="dates1">
    <b>Start Date: </b><apex:inputfield value="{!taskRec.ActivityDate__c}" required="true"/>
    <b>End Date: </b><apex:inputfield value="{!taskRec1.ActivityDate__c}" required="true"/>
    &nbsp;<apex:commandLink value="Submit" rerender="test" styleClass="btn linkAsBtn"/>  
    <apex:commandLink value="Printable View" action="{!next}" id="cmdNext" target="_blank" styleClass="btn linkAsBtn"/> 
    <apex:commandLink value="Save as PDF" action="{!next1}" id="cmdNext1" target="_blank" styleClass="btn linkAsBtn"/>
</apex:panelGrid>
<apex:outputPanel id="test">  
    <table class="myTable">
        <tr>
            <th>BPR Case</th>
            <th>BP MGR</th>
        </tr>
    <apex:repeat value="{!ca}" var="c" id="case">
        <tr>
            <td>
            <apex:outputText value="{0,date,MM'-'dd'-'yyyy}"><apex:param value="{!c.Notification_Date__c}"/></apex:outputText>
            </td>
            <td>
            <apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
                <apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Trading Company'}"></apex:outputText><br></br>
            </apex:repeat>
            </td>   
            <td>
            <apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
                <apex:outputText value="{!bus.Business__r.Business_Name__c}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"></apex:outputText><br></br>
            </apex:repeat>
            </td>  
            <td>
            <apex:repeat value="{!c.Subject_for_Cases__r}" var="sub">
                <apex:outputText value="{!sub.Subject_Name__r.Subject_Name__c}" rendered="{!sub.Subject_Name__r.Subject_Type__c = 'Exporter'}"></apex:outputText><br></br>
            </apex:repeat>
            </td>  
            <td>
            <apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
                <c:Business_Shipper_Contact_Info BusinessID="{!bus.Business__r.Id}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"/><br></br>
            </apex:repeat>
            </td>                                                      
            <td>
            <apex:repeat value="{!c.Products__r}" var="pro">
                <apex:outputText value="{!pro.Brand__c}"></apex:outputText>&nbsp;<apex:outputText value="{!pro.Product_Types__c}"></apex:outputText>&nbsp;(<apex:outputText value="{0,number,###,###}"><apex:param value="{!pro.Total_Product_Quantity__c}"/></apex:outputText>)<br></br>
            </apex:repeat>
            </td>
            <td>
                <apex:outputText value="{!c.Total_Product__c}"></apex:outputText>
            </td>
            <td>
            <apex:repeat value="{!c.Incident_Locations__r}" var="loc">
                <apex:outputText value="{!loc.Container_Type__c}"></apex:outputText><br></br>
            </apex:repeat>
            </td>
            <td>
                <apex:outputText value="{!c.Comments_Postal_Express__c}"></apex:outputText>
            </td>            
         </tr>              
    </apex:repeat>
    </table>
</apex:outputPanel>
</apex:form>
</apex:page>

Main Class:

public class MonthlySeizureController{
    public task taskRec{get; set;}
    public task taskRec1{get; set;}    
    public MonthlySeizureController(ApexPages.StandardController sc){
        taskRec= new task (ActivityDate__c = Date.today().addMonths(-1));
        taskRec1= new task (ActivityDate__c = Date.today());

    }
    public Case__c[] ca {
        get {
                Case__c[] cases = 
                    [SELECT Id, Name, CreatedDate, LastModifiedDate, Owner.FirstName, Owner.LastName, Notification_Date__c, Incident_City__c, Incident_Country__r.Name, 
                            Total_Product__c, Comments_Postal_Express__c,
                            (SELECT RecordType.Name, Container__c, Ship_Date__c, Date_of_Retention__c, Carrier__c, Port_of_Loading__c, Port_of_Discharge__c, Container_Type__c, 
                            Place_of_Retention__c, 
                            Vessel__c, Master_BOL__c, House_BOL__c  FROM Incident_Locations__r), 
                            (SELECT Brand__c, Product_Detail__c, Product_Types__c, Total_Product_Quantity__c FROM Products__r),
                            (SELECT Business__r.Business_Name__c, Business__r.Business_Type__c FROM BusinessCasesAssociations__r),
                            (SELECT Subject_Name__r.Subject_Name__c, Subject_Name__r.Subject_Type__c FROM Subject_for_Cases__r)
                    FROM Case__c
                    WHERE Supply_Chain_Type__c = 'SEA'
                        AND Notification_Date__c >= :taskRec.ActivityDate__c
                        AND Notification_Date__c <= :taskRec1.ActivityDate__c
                    ORDER BY Notification_Date__c ASC];

                return cases;
            }
    }   
    public PageReference next(){
        PageReference nextpage = new PageReference('/apex/securityreport1ppv');
        return nextpage;
    }
    public PageReference next1(){
        PageReference nextpage = new PageReference('/apex/securityreport1sap');
        return nextpage;
    }
}

VF Component:

<apex:component controller="BusinessContactController" access="global">
<apex:attribute id="recordID" assignTo="{!recordID}" name="BusinessID" type="Id" description="The Business's SF Id"/>
    <apex:repeat value="{!bus1}" var="addr1">
            <apex:outputText value="{!addr1.Street__c}"></apex:outputText>
            &nbsp;<apex:outputText value="{!addr1.Business_City__c}"></apex:outputText>
            &nbsp;<apex:outputText value="{!addr1.State_Province__c}"></apex:outputText>
            &nbsp;<apex:outputText value="{!addr1.Country__c}"></apex:outputText>
            &nbsp;<apex:outputText value="{!addr1.Zip_Postal_Code__c}"></apex:outputText>
    </apex:repeat>       
</apex:component>

2nd Class:

public with sharing class BusinessContactController {
    public BPR_Business_Addresses__c[] bus1 {
        get {   
            BPR_Business_Addresses__c[] AddressList = 
                    [SELECT Street__c, Business_City__c, State_Province__c, Country__c, Zip_Postal_Code__c, Business__r.Id
                    FROM BPR_Business_Addresses__c
                    WHERE (Business__r.Id = :recordId)];

            return AddressList ;
        }
        set;
    }
    Public string recordId {get;set;}
}
  • 2
    A good design practice is to lazy load in your getters. i.e. instead of always querying, check first whether the variable is null, and only if it's null do you go and query the database. Having said that, the issue looks to be that you have your component within an apex repeat, which means there will be as many SOQL queries and instances of the component's controller, as you have records within that repeat element. I think you're going to have to stop using the component to get this working without those errors. – Nick Cook Mar 16 '16 at 20:08
  • Or query for the records in the main controller and pass in the record to the component . Takes away the need to even have a controller on the component – Eric Mar 17 '16 at 0:31
  • Eric, Not sure what you mean but it sounds promising. Could you provide an example? Thanks! – Nathan Mar 17 '16 at 20:41
2

In your VF page you reference the component within an apex:repeat element:

<apex:repeat value="{!c.BusinessCasesAssociations__r}" var="bus">
   <c:Business_Shipper_Contact_Info BusinessID="{!bus.Business__r.Id}" rendered="{!bus.Business__r.Business_Type__c = 'Exporter'}"/><br></br>
</apex:repeat>

For each item within the repeat a new component is created, which includes the AddressList SOQL query. I'm asusming this is where the 'too many SOQL queries' error is coming from.

To solve this, I think you'll have to stop using the component and do something like:

  • In your controller, create a set of Ids that will be in your repeat
  • perform your query with this set of Ids and assign it to a list of BPR_Business_Addresses__c records
  • replace the component with some code that displays the value of this new list of BPR_Business_Addresses__c records

NOTE: A good design practice is to lazy load in your getters. i.e. instead of always querying, check first whether the variable is null, and only if it's null do you go and query the database.

  • Nick, That is what I was thinking but I am not sure how to do that since the IDs that are needed are in a subquery. The Id that matches up is in the query (SELECT Business__r.Id, Business__r.Business_Name__c, Business__r.Business_Type__c FROM BusinessCasesAssociations__r), Any ideas how to put this into a list would be great. Thx – Nathan Mar 16 '16 at 20:48
  • @Nathan You're going to need to loop through the cases, then loop through the subquery. It's probably worth asking that as a separate question. It looks like you're probably going to have to create a wrapper class as well, which is going to make this a lot more complicated than your current controller. Hope you've got some spare time to learn it :) – Nick Cook Mar 16 '16 at 20:55

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.