+ Start a Discussion

Database.QueryLocator : Unexpected Token



I have a query in SOQL that returns a number of nested result sets (relationship query). When the number of rows gets beyond 25 the page loading gets very very slow. So I decided to try and age the results, say 20 at a time. I did a bit of reading and Googling and noticed that you can use ApexPages.StandardSetController and Database.QueryLocator to page results which looks perfect for what I want.


However, I have been unable to make much sense of it thus far for the reasons below...


  1. The query will not take bind variables - it just replies with MALFORMED_QUERY: Colleague_Availability__r where Date__c >= :weekstarting and Date__c < :weekending) ^ ERROR at Row:1:Column:164 unexpected token: ':'
  2. When I temporarily hard code the dates in, the paging will simply not work! I have tried rerender and everything but the viewState is not loaded with any fresh data whenever the command link for "next" is pressed.

Here is my code...


public ApexPages.StandardSetController con {
        get {
        string qry = 'Select Id, Name,Worker_First_Name__c, UK_Mobile_Number__c, (Select Id, Name, Availability__c, Date__c, Confirmed__c from Colleague_Availability__r where Date__c >= :weekstarting and Date__c < :weekending) from  Colleague__c where Id in (select Colleague__c from Junction__c where Site__c =: siteId and Status__c = \'Active\' and Home__c = \'Home\') and isdeleted = false order by Name asc';
            if (con == null) {
                con = new ApexPages.StandardSetController(Database.getQueryLocator(qry));
            return con;

 And the VF Code here...


<apex:pageBlock mode="edit" id="theBlock" rendered="{!RenderPage}" >
        <apex:pageBlockButtons id="btns">
            <apex:commandButton onclick="return goPrev();" value="<< Prev" id="prev"   />
            <apex:commandButton onclick="return goNext();" value="Next >>" id="next"   />
             <apex:commandLink immediate="true" rerender="theBlock" action="{!previous}" rendered="{!hasPrevious}" >Previous</apex:commandlink>
            <apex:commandLink immediate="true" rerender="theBlock" action="{!next}" rendered="{!hasNext}" >Next</apex:commandlink>


This is getting rather urgent that I arrive at a solution. Do you think I'd be better trying to get it to page the data with my own code? If so how would I do my own pager?


Is there any other way of speeding up loading of data in this VF page? It takes over 5 seconds to load with 60 records, sometime longer.


As far as I know I have used all the tricks in the book to make it load faster. The query itself is fast, just seems that the page is very very slow at loading nested data?


Thanks in advance for any help/advice you can give me.