+ Start a Discussion
PrazPraz 

queryMore like functionality in apex

Hi

 

I have a query like

 

String querySoql = 'Select Id, Name from Account limit 10000';

 

 

Now I have the following functionality to implement them..

 

setCon = new ApexPages.StandardSetController(Database.query(soqlQuery));

setCon.next

setCon.prev

 

where next and prev are used for page wise move..

 

Since the number of rows in the objects is more than 10000 it is not showing the values in the page instead giving following error

 

Content cannot be displayed: Too many query rows: 10001

 

Content cannot be displayed: retrieve id limit reached

 

 

Any help on this will be much appreciated.

HarmpieHarmpie

You will need to pass a querylocator to the setcontroller, something like the code below...

 

 

public class QlController {
    public PageReference next() {
        setCon.next();
        return null;
    }
    public Integer totalRows {
        get {
            return setCon.getResultSize();
        }
    }
    public PageReference previous() {
        setCon.previous();
        return null;
    }
    public ApexPages.StandardSetController setCon {
        get {
            if(setCon == null) {
                setCon = new ApexPages.StandardSetController(Database.getQueryLocator([SELECT Id, Name from Account LIMIT 10000]));
            }
            return setCon;
        }
        set;
    }
    public List<Account> getAccs() {
        return (List<Account>) setCon.getRecords();
    }
}

 Page code:

 

<apex:page controller="QlController">
<apex:form id="dataform">
    <apex:commandLink value="Previous" action="{!previous}" rerender="dataform" />
    &nbsp;&nbsp;{!totalRows}&nbsp;&nbsp;
    <apex:commandLink value="Next" action="{!next}" rerender="dataform" /><br /><br />
    <apex:repeat value="{!accs}" var="acc" rows="10">
        {!acc.Name}<br />
    </apex:repeat>
</apex:form>
</apex:page>

 

 

Hope this helps

 

PrazPraz

Initially,

 

I have been using getQueryLocator as well..still it was giving the problem...same error :smileysad:

HarmpieHarmpie

The code I posted works like a charm, tested with 12k+ accounts.

PrazPraz

OK..that I got but the thing is if the number of rows is 11000 actually..is there any chance that I can slide the cursor to the records of 10000 to 11000 portion?

HarmpieHarmpie

Not sure what the best approach is there, besides filtering the criteria in your selection. You will probably need to get creative. Perhaps create 2 setcontrollers and then check whether you reached the end of setcontroller 1 with hasnext() and switch to setcontroller 2 if you reached the end of the first. Setcontroller 2 could perhaps be ordered in reverse direction as 1....

 

Just a haunch, not sure if this is the best approach.

PrazPraz

Problem will be see if I have 25K records... now with this two controller approach I will get first 10K with ascending and last 10K with descending...the 5K records in between left...

 

Now even if we set another controller and get the criteria it will be some record specific..

 

now for another set if I get say 35K set of records..this approach won't work...

jeffdonthemic2jeffdonthemic2

I have a blog post with a demo and source code that uses the StandardSetController:

 

Visualforce Page with Pagination

 

Jeff Douglas

Appirio, Inc.

http://blog.jeffdouglas.com

 

Author: The Salesforce Handbook

PrazPraz

Thanks a lot Jeff..

 

I read ur blog during my pagination work..and I am reading again..

 

the only difference is...I have created page numberwise buttons to navigate... life FIRST PREVIOUS 1 2 3 4 5 NEXT LAST..

 

Can I send you the code..can tell me is there any wrong there for the navigattion?

 

Regards

 

Prasenjit

HarmpieHarmpie

Nice blog Jeff, does that approach (although theres a limit of 100 in the query) work on all records, if the object has say 15k+ records?

PrazPraz

I have another question is that if I am using getQueryLocator() then how can I test to know that the cursor has actually slided and showing me 12Kth record?

Is there any way to test and ensure it?