"Too many SOQL queries: 101" - Apex error occurred while executing Site URL Rewriter

Question

Here is the scenario, when I receive error message.

"Apex error occurred while executing Site URL Rewriter"

I have a link in my VF page to export records as excel. on click of this export link, I call VF page which has content type as "application/vnd.ms-excel#Test.xls"

After implementing URLRewrite, whenever I click on export, it ends up with the above error message. In the debug log it is logged as "Too many SOQL queries: 101"

Below is the URLRewrite class: global class URLRedirect_URLRewriter implements Site.UrlRewriter {

    //Variables to represent the user-friendly URLs for     
    //foo pages 

    String FOO_PAGE = '/foo/';

    //Variables to represent my custom Visualforce pages     

    String FOO_VISUALFORCE_PAGE = '/FooDetails?fooid=';    
    String LIST_FOO_VISUALFORCE_PAGE = '/Foo';

    global PageReference mapRequestUrl(PageReference myFriendlyUrl)
    {
        String url = myFriendlyUrl.getUrl();        
        if(url.startsWith(FOO_PAGE) && !url.contains('&'))
        {
            String name = url.substring(FOO_PAGE.length(), url.length());
            IPFooObject grp;
            try
            {  
                grp = [select Id,Name,Name__c from IPFooObject where Name =:name LIMIT 1];
                if (grp != null)
                {
                    return new PageReference(FOO_VISUALFORCE_PAGE + grp.id);
                }
                else
                {
                    return new PageReference(LIST_FOO_VISUALFORCE_PAGE);
                }
            }
            catch(exception ex)
            {
                return new PageReference(LIST_FOO_VISUALFORCE_PAGE);
            }
        }
        //If the URL isn't in the form of a foo page, continue with the request 

        return null;
    }

    global List<PageReference> generateUrlFor(List<PageReference> mySalesforceUrls)
    {
        //A list of pages to return after all the links  
        //have been evaluated 

        List<PageReference> myFriendlyUrls = new List<PageReference>();

        //a list of all the ids in the urls 

        List<id> grpIds = new List<id>();

        // loop through all the urls once, finding all the valid ids 
        List<String> lstString = new List<String>();
        for(PageReference mySalesforceUrl : mySalesforceUrls)
        {
            lstString.add(mySalesforceUrl.getUrl());
        }

        for(PageReference mySalesforceUrl : mySalesforceUrls)
        {
            //Get the URL of the page     
            String url = mySalesforceUrl.getUrl();        
            if(url.startsWith(FOO_VISUALFORCE_PAGE) && !url.contains('&'))
            {
                //Extract the ID from the query parameter 
                //and store in a list 
                //for querying later in bulk. 
                string id= url.substring(FOO_VISUALFORCE_PAGE.length(),url.length());
                grpIds.add(id);
            }
        }

        // Get all the group names in bulk 
        List <IPFooObject> groups = [SELECT Name FROM IPFooObject WHERE Id IN :grpIds];
        // make the new urls 

        Integer counter = 0;

        // it is important to go through all the urls again, so that the order 
        // of the urls in the list is maintained.  

        for(PageReference mySalesforceUrl : mySalesforceUrls) 
        {
           //Get the URL of the page 
           String url = mySalesforceUrl.getUrl();

           if(url.startsWith(FOO_VISUALFORCE_PAGE)  && !url.contains('&'))
           {
                myFriendlyUrls.add(new PageReference(FOO_PAGE + groups.get(counter).name));
                counter++;
           } 
           else 
           {
                //If this doesn't start like a groups page,     
                //don't do any transformations     
                myFriendlyUrls.add(mySalesforceUrl);
           }
        }
        return myFriendlyUrls;
  }

}

While debugging I noticed that, below line is getting is executed more than once. List groups = [SELECT Name FROM IPFooObject WHERE Id IN :grpIds];

Why is the line getting executed so many times? What's wrong with the code?

Here is the VFPage being called through commandLink:

<apex:page readOnly="false" controller="myController" contentType="application/vnd.ms-excel#{!IF(AND((ExportTitle <> null),(ExportTitle <> '')),ExportTitle,'Patents')}.xls" showHeader="false" standardStylesheets="false" action="{!PageLoad}" cache="true">
    <apex:stylesheet value="{!URLFOR($Resource.IVExchange_Styles, 'CSS/PageStyles.css')}" />
    <apex:form >
        <apex:outputPanel id="FooExport">
            <apex:dataTable value="{!lstFooInfo}" var="lst" id="FooListTable" styleClass="fullwidth" cellpadding="5" headerClass="userTableHeaderWidth" rowClasses="oddRow TextPriority10, evenRow TextPriority10" border="1">
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader alignLeft">Group No.</span></apex:facet>
                    <apex:outputText value="{!lst.GroupNumber}"></apex:outputText>
                </apex:column>
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader">Group Name</span></apex:facet>
                    <apex:outputText value="{!lst.GroupName}"></apex:outputText>
                </apex:column>
                <apex:column >
                    <apex:facet name="header"><span class="userTableListHeader">Issue Date</span></apex:facet>
                    <apex:outputText value="{0,date,yyyy'-'MM'-'dd}">
                        <apex:param value="{!lst.IssueDate}" />
                    </apex:outputText>
                </apex:column>
                <apex:column rendered="{!if((LOWER($CurrentPage.parameters.seltab) == 'licensed' && ($CurrentPage.parameters.grpid != '')),true,false)}">
                    <apex:facet name="header"><span class="userTableListHeader">Participating</span></apex:facet>
                    <apex:outputText value="{!lst.Participating}"></apex:outputText>                                                         
                </apex:column>
            </apex:dataTable>       
        </apex:outputPanel>
    </apex:form>
</apex:page>

Answer 1

The number of executions of your SOQL query would be directly proportional to the number of invocations of generateUrlFor() in one execution context.

I'd recommend having the map to be an instance variable that is initialized at load. Depending on how many records your IPFooObject Object has, you could just have a map that loads up the whole map at startup, rather than lazy loading, which makes sense if IPFooObject has a prohibitively high number of records.

Map <Id, IPFooObject> groups; //instance variable

 if (groups != null) //check that this is not already initialized
       groups = new Map<Id, IPFooObject>([SELECT Name FROM IPFooObject]);

Then just get from this map each time you need to use it in your various methods

Answer 2

First thing which popups in my mind is loop of a page which calls that code.

First thing I would do is to remove/comment/whatever lines with SOQL to be sure that the problem is not in SOQL. I think the problem is in looping request to the page.

Answer 3

My aim is to handle the friendly urls. But I had been getting error while clicking on the export link. After trying different solutions, landed at this point.

Since the url invoked through this action is has no need to be validated as friendly url, I added extra if conditions in my URLRewrite class.

global PageReference mapRequestUrl(PageReference myFriendlyUrl)
{
    String url = myFriendlyUrl.getUrl();        
    if(url.startsWith(FOO_PAGE) && isNumber(url.substring(FOO_PAGE.length(),url.length())) && !url.contains('&'))
    {
        ...
    }
}

global List<PageReference> generateUrlFor(List<PageReference> mySalesforceUrls)
{
    if (mySalesforceUrls.size() > 0 && mySalesforceUrls.get(0).getUrl().startsWith(FOO_VISUALFORCE_PAGE) && !mySalesforceUrls.get(0).getUrl().contains('&'))
    {
        ...
    }
}

This solved my problem. Instead of executing loop for all the pages, it will check only for the pages required. Thank you all for your suggestions.