Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
kavayahkavayah 

Getting More Data in chunks of 100

Hi:

 

I have utilized angular JS in visualforce page aling with apex class to make a enhance dynamic search of transaction data in salesforce. The way it is done now is the following.

 

1) Apex Class executes a SOQL which returns a JSON string of data elements.

2) Visual force page which incorporates angular JS shows the data, with a search box on top.

 

Problem:

==========

Some of the objects is returning more than 50000 rows, and governor limit exception is thrown. 

 

Question:

==========

How can I invoke to get chunks of 100 records, the user will search from that 100 records, if the search data do not exist, then "Show More" button will retrieve the next 100 records.

 

Any help is really appreciated.

 

CRMScienceKirkCRMScienceKirk

Within your query, you can use an OFFSET clause.  Using it in along side an ORDER BY will allow you to query for your chunks of 100.  Your "Show More" button should be rendered if your result size = the offset and clicking it should increase the offset number by the 100 (or a chunksize variable).
GlynAGlynA

@kavayah,

 

The problem with OFFSET is that the maximum offset is 2000.  So you won't be able to use offset to get more than 2100 records.

 

Another way to do this is to maintain a set of the IDs of the records you've already queried and use "NOT IN" to exlude them.  This will eventually exceed the limit on viewstate size, however, when the set gets too big.  You won't be able to go 500 pages in to see all the records.

 

private Set<Id> set_RecordsSeen = new Set<Id>();
public List<sObject> list_Records { get; set; }

public void getNextPage()
{
    list_Records =
    [   SELECT  Id
        FROM    sObject
        WHERE   Id NOT IN :set_RecordsSeen
        LIMIT 100
    ];

    set_RecordsSeen.addAll( (new Map<Id,sObject>( list_Records )).keySet() );
}

This code shows the basic idea.  You'll have to replace "sObject" with whatever object you're querying. If you need to do a dynamic query, then you'll have to convert the set into a string.  Let me know if you need help doing this.

 

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator
sfdcfoxsfdcfox
I usually order by ID, then simply use WHERE ID > :lastQueriedID, thus avoiding the limits of offset. You can use this same trick for any field, but ID is convenient because they're always chronological, but GlynA definitely has the right idea.
GlynAGlynA

Brian - that's BRILLIANT!  Simple and elegant (why didn't I think of that!)

 

So the code would be something like:

 

private Id lastRecordSeen;
public List<sObject> list_Records { get; set; }

public void getNextPage()
{
    list_Records = lastRecordSeen == null
        ?   [SELECT Id FROM sObject ORDER BY ID LIMIT 100]  //  first time only
        :   [SELECT Id FROM sObject WHERE Id > :lastRecordSeen ORDER BY Id LIMIT 100];

    if ( !list_Records.isEmpty() ) lastRecordSeen = list_Records[ list_Records.size() - 1 ].Id;
}

And it should work for as many records as there are - no viewstate problems!  Awesome.

 

-Glyn