SharePoint 2010 REST: getJSON & Append on List with more than 5,000 records

Question

Happy Tuesday!

I got a problem I can't seem to get to work. I am trying to query a list using REST that has more than 5,000 items. I figured the best way would to loop through the 1st 0<>5000 then the other >5000 and then append my results but I can't seem to get it working. Anyone have experience with this?

function fnResidences(choice) {
        $.getJSON("../_vti_bin/ListData.svc/Residences?$expand=Country&$select=Country/Country,*$filter=(Id gt 0 and Id lt 5000) and (Country/Country eq '" + choice + "')", function (data) {
            if (data.d.results == 0) {

                $("#Residences-Table").html("N/A");

                } else {
                $("#Residences-Table").empty();

                    $.each(data.d.results, function (data) {

                    $("#Residences-Table").append("<tr><td>"+this.ResidenceName+"</td></tr>");

                        $.getJSON("../_vti_bin/ListData.svc/Residences?$expand=Country&$select=Country/Country,*$filter=(Id gt 5000) and (Country/Country eq '" + choice + "')", function (data) {

                            if (data.d.results == 0) {

                                $("#Residences-Table").html("N/A");

                                } else {

                                    $("#Residences-Table").empty();

                                    $.each(data.d.results, function (data) {

                                         $("#Residences-Table").append("<tr><td>"+this.ResidenceName+"</td></tr>");

                                    });
                                }
                       })
                });
            }
        })
    }

Answer 1

Consider using paging. It boils down to using the $top and $skip parameters to specify how many items to retrieve and how many items to skip respectively.

You'll just need to make sure that the column you use in the $orderby parameter is indexed. (Or you can use the ID field, which is indexed by default.)

I've used recursion to successfully page through results from a list with >5000 items using the REST API. The recursive getNextBatch() function in the example below retrieves up to 1000 items each time it's called.

var endpointUrl = "/_vti_bin/ListData.svc/ListName";
$.getJSON(
    endpointUrl + "?$orderby=Id&$top=1000",
    function(data){
        processData(data); // you can do something with the results here
        var count = data.d.results.length;
        getNextBatch(count, processData, onComplete); // fetch next page 
    }
);

function getNextBatch(totalSoFar, processResults, onCompleteCallback){
    $.getJSON(
        endpointUrl + "?$orderby=Id&$skip="+totalSoFar+"&$top=1000",
        function(data){
            var count = data.d.results.length;
            if(count > 0){
                processResults(data); // do something with results
                getNextBatch(totalSoFar+count, callback); // fetch next page
            }else{
                onCompleteCallback();
            }
        }
    );
}

Note that not visible in the above example code are the methods processData() and onComplete(), which would be used to process a batch of results and perform any final tasks once the results are completely loaded respectively.