0

I'm using standard set controller, with dynamic search options on VF page. Now soql limit 1000 it works perfect.

But i've to remove the soql querye limit. I.e. I can be able to fetch 70000 record also.

I'm updating record from my apex code via wrapper class.

IF I use read only= true and Standard set control pagination then can i able to fetch more than 50000 record.?

Now i'm try to use the offset in soql limit but when i use the offset is soql query, it will not fech the record(means offset word in query not working).

Please Guide for solution

My lat part of dynamic soql query is .

QueryLion += ' ORDER BY ' + String.escapeSingleQuotes(SortField) + ' ' + String.escapeSingleQuotes(SortDirection) + ' LIMIT '+String.escapeSingleQuotes(String.ValueOf(list_size))+' OFFSET ' +String.escapeSingleQuotes(String.ValueOf(counter));

even if i try below below hard code its not working

QueryLion += ' ORDER BY ' + String.escapeSingleQuotes(SortField) + ' ' + String.escapeSingleQuotes(SortDirection) + ' LIMIT 1000 offset 2';

My Vf page Screenshot. getting zer0 record when used offset. enter image description here

  • Are you trying to generate a PDF or CSV? If not, you might want to pursue remoting. – Adrian Larson Feb 18 '17 at 5:36
  • @AdrianLarson No. I'm just updating record in apex class via wrapper class – Amol Feb 18 '17 at 5:45
  • So...why do you want to display 70k records? What sort of user do you expect to be capable of digesting that much information? – Adrian Larson Feb 18 '17 at 5:45
  • @AdrianLarson I'm fetching leads record. and its client requiremnt – Amol Feb 18 '17 at 5:53
  • 1
    I would try to communicate with the client and understand why he wants 70K records on one page. There's probably some miscommuncation here. Maybe he wants to be able to show them all by using paging? No one can read a page containing 70K rows. It's not usable – mkorman Feb 18 '17 at 11:30
2

The limit for the StandardSetController is 10,000:

The maximum record limit for StandardSetController is 10,000 records. Instantiating StandardSetController using a query locator returning more than 10,000 records causes a LimitException to be thrown. However, instantiating StandardSetController with a list of more than 10,000 records doesn’t throw an exception, and instead truncates the records to the limit.

The limit for OFFSET is 2,000:

The maximum offset is 2,000 rows. Requesting an offset greater than 2,000 results in a NUMBER_OUTSIDE_VALID_RANGE error.

Instead, if you really want to break the limits, you're going to have to go to JavaScript. There's a lot of potential solutions, but basically you're going to create a script to load all of the data into the client, and then render it client-side.

The only problem that you might run into is that there are various megabyte limits for how much data you can transfer at once. For example, any Visualforce response is a limit of 15,000,000 bytes, Apex Code is still going to be limited to 6,000,000 bytes of heap used, and so on.

One common technique I use is to paginate by ID values. That would go like this:

// Apex Code
@RemoteAction public static Account[] getAccounts(Id firstId) {
    return [SELECT Id FROM Account WHERE Id > :firstId ORDER BY Id LIMIT 2000];
}

// JavaScript
(function() {
    var cache = [],
        pageNumber = 1;
    function renderTable() {
        // Do your rendering here
    }
    function getResponse(data, event) {
        if(data && data.length) {
            data.forEach(function(v) { cache.push(v); });
            loadMoreRecords(data[data.length-1].Id);
        } else {
            renderTable();
        }
    }
    function loadMoreRecords(id) {
        MyController.getAccounts(id, getResponse);
    }
    loadMoreRecords(null);
})();

You might want to pre-query how many records there are, so you can render a progress bar. There's various libraries for jQuery, AngularJS, Backbone, and so on that can help you with the pagination. Here's an example written in jQuery that you can use to get started.

This would also be something you could write up in Lightning without too much effort. I've written a gist that demonstrates a quick pagination/sorting demo you could use to get started.

  • @sfdecfox MY soql query is dynamic and I'm using Standard Set controller . SO by using custom Paginatin and offset in query, it is not possible to fetch more than 50 k record? – Amol Feb 20 '17 at 6:03
  • @Amol You could try storing just all 70,000 Id values, and then retrieve all the details for the current page, but you'll still end up not using StandardSetController. The limits in place on that class can't be exceeded. – sfdcfox Feb 20 '17 at 6:17
  • Thanks for your reply. ok. means by using std set contr. we not able to fetch 70k record. still i've to fetch all record so what, I need to change my std set contr method in apex ? Please Guide. I'm confusing too much – Amol Feb 20 '17 at 7:18

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.