+ Start a Discussion
Patrick DixonPatrick Dixon 

nested apex:repeats and SOQL

I have a VF page with a 2-level nested apex:repeat structure with separate SOQL queries for each loop.  The outer loop returns (amongst other things) a list of strings which I want to use as filter terms in the inner loop's SOQL.  The list is saved as a list variable in the controller (keywords[]), along with an index integer (iKey) for the inner loop.


However, although I can see from the logs that the list of filter terms is all build correctly, the debug loop seems to show that the inner loop's SOQL is only run once and hence it only seems to use the first of the filter terms to get results.


Here's what the debug log looks like:-



18:58:54.115|CODE_UNIT_STARTED|[EXTERNAL]|01pA0000002NfKZ|PageRenderer get(sContentList)
18:58:54.115|CODE_UNIT_STARTED|[EXTERNAL]|01pA0000002NfKZ|PageRenderer invoke(getsContentList)
18:58:54.116|SOQL_EXECUTE_BEGIN|[159]|Aggregations:3|Select URL_Link__c, Image__c, Synopsis__c, Release_Date__c, Name, Heading__c, Content_Type__c,
Internal_Link_Prefix__c, Keywords__c,
Attachment__c, (Select Id From Attachments limit 1),
(Select Id From Contents__r limit 1),
Content_Type__r.Id, Content_Type__r.Title__c, Content_Type__r.Show_Date__c,
Content_Type__r.Image_Position__c, Content_Type__r.Image_Border__c,
Content_Type__r.Read_More__c, Content_Type__r.Read_More_Dest__c,
Content_Type__r.List_Length__c, Content_Type__r.Synopsis_Length__c,
Content_Type__r.List_Style__c, Content_Type__r.Searchable__c,
(Select Page_Layout__c From Sidebar_Elements__r) From Content_Item__c
where Content_Type__c in (select Content_Type__c From Sidebar_Element__c where Page_Layout__c = :pgId)
and Keywords__c like :keywordList[iKey++]
order by Release_Date__c desc
18:58:54.143|CODE_UNIT_FINISHED|PageRenderer invoke(getsContentList)
18:58:54.143|CODE_UNIT_FINISHED|PageRenderer get(sContentList)

The filter terms %all%, %, %, %fractal% are correct, but the SOQL query should execute 4 times ... and must do as I get 4 results on the VF page.


So what's going on?


Cory CowgillCory Cowgill



From your debug log its hard to tell whats exactly going on though. It may be helpful if you give small snippets of the VF Page and the Apex Controller.


Also, you never want to nested SOQL calls within For Loops if you can avoid it at all costs, otherwise you can hit governor limits.


You'll want to use List and Map collections to prevent doing SOQL within nests loops. Not sure if that is what you mean when you say your saving the list variable in the controller along with an index, so maybe your already doing that.




Patrick DixonPatrick Dixon

Yeah - it's hard for me to tell too... ;-)


The VF code looks something like this (with some boring bits cut-out)


<apex:repeat var="sel" value="{!sElements}" >
    <apex:variable var="noItems" value="{!sel.content_type__r.List_Length__c}" />
    <apex:variable var="scl" value="{!noItems}"/>
    <apex:repeat var="scl" value="{!sContentList}" >

        <!-- Only list content of THIS content type -->
        <apex:variable var="sclRender" value="{!(scl.Content_Type__c=sel.Content_Type__c)}" />
        <apex:variable var="sclRender" value="{!IF(noItems<=0, false, sclRender)}" />
        <apex:outputPanel rendered="{!sclRender}" >

        <!-- SNIP -->


and the relevent bits of the controller look something like this (I've moved to dynamic SOQL but it still behaves the same) :-


    private list<string> keywordList {get; set;}
    private list<string> selList {get; set;}
    private integer iKey;

    public list<Sidebar_Element__c> getsElements() {

        //new keywords list
        keywordList = new list<string>();
        //new sidebar elements list
        selList = new list<string>();
        //initialise index
        iKey = 0;
        list<Sidebar_Element__c> elements =
            [Select id, Name, Order_On_Page__c, Page_Layout__c, Content_Item__c, Content_Type__c,
            Twitter_Feed__c, No_of_Tweets__c, Keyword__c,
            Content_Item__r.Heading__c, Content_Type__r.Title__c, Content_Type__r.View_All__c, Content_Type__r.View_All_Dest__c,
            Content_Type__r.List_Length__c, Content_Type__r.Synopsis_Length__c
            From Sidebar_Element__c where Page_Layout__c = :pgId order by Order_On_Page__c asc];
            //build keywords list for this sidebar element
            for (Sidebar_Element__c sel : elements) {
                if(sel.Keyword__c != null)
                    keywordList.add('%' + sel.Keyword__c + '%');
                else keywordList.add('%');
            for (string key : keywordList) system.debug(key);
            for (string sel : selList) system.debug(sel);
        return elements;

       public list<Content_Item__c> getsContentList() {
        // create the query string
        String qryString =
            'Select URL_Link__c, Image__c, Synopsis__c, Release_Date__c, Name, Heading__c, Content_Type__c, ' +
            'Internal_Link_Prefix__c, Keywords__c, Attachment__c, (Select Id From Attachments limit 1), ' +
            '(Select Id From Contents__r limit 1), ' +
            'Content_Type__r.Id, Content_Type__r.Title__c, Content_Type__r.Show_Date__c, ' +
            'Content_Type__r.Image_Position__c, Content_Type__r.Image_Border__c, ' +
            'Content_Type__r.Read_More__c, Content_Type__r.Read_More_Dest__c, ' +
            'Content_Type__r.List_Length__c, Content_Type__r.Synopsis_Length__c, ' +
            'Content_Type__r.List_Style__c, Content_Type__r.Searchable__c, ' +
            '(Select id From Sidebar_Elements__r) From Content_Item__c ' +
            'where Content_Type__c in (select Content_Type__c From Sidebar_Element__c where id = ' + '\'' + selList[iKey] + '\') ' +
            'and Keywords__c like ' + '\'' + keywordList[iKey] + '\'' + ' order by Release_Date__c desc';
        list<Content_Item__c> contentList = Database.query(qryString);

        return contentList;

I'm guessing that the problem is this myserious word 'Aggregations: ' in the log.  It seems to somehow be 'aggregating' the SOQL queries and ignoring the dynamic bit completely - so it just uses the first 'keyword' and 'selList' it gets for all the SOQL queries in the repeat loop.


So how do I make it behave and do a seperate SOQL for each itteration through the outer repeat loop - surely this isn't how dynamic SOQL is supposed to work?