Searching through REST API and CAML with Paging

Question

I have requirement to filter two fields with substring but I found out that substring is not working in REST API it have some kind of bug. So below question stackexchange.com suggested to use listdata.svc

logical not of substringof() in 2013 REST API

But I cannot use two fields at the same time with listdata.svc. So I decided to use CAML Query with spservices jquery but there paging is not working.

Below is my code

var news = ''
    $().SPServices({
      operation: 'GetListItems',
      async: false,
      webURL: _spPageContextInfo.siteAbsoluteUrl,
      listName: 'News',
      CAMLQuery: `<Query>
                    <Where>
                        <And>
                          <Or>
                              <Contains>
                                <FieldRef Name='Title' />
                                <Value Type='Text'>KFH</Value>
                              </Contains>
                              <Contains>
                                <FieldRef Name='Detail' />
                                <Value Type='Note'>KFH</Value>
                              </Contains>
                          </Or>
                          <Eq>
                              <FieldRef Name='Active' />
                              <Value Type='Choice'>Yes</Value>
                          </Eq>
                        </And>
                    </Where>
                    <OrderBy>
                      <FieldRef Name='PublishDate' Ascending='False' />
                    </OrderBy>
                  </Query>`,
      CAMLViewFields: `<ViewFields>
                        <FieldRef Name='Title' />
                        <FieldRef Name='Detail' />
                        <FieldRef Name='PublishDate' />
                        <FieldRef Name='ID' />
                        <FieldRef Name='Attachments' />
                      </ViewFields>`,
      CAMLRowLimit: 6,
      CAMLQueryOptions:
        "<QueryOptions><Paging ListItemCollectionPositionNext='" +
        newpos +
        "'><QueryOptions>",
      completefunc: function(xData, Status) {
        pos = $(xData.responseXML)
          .find("[nodeName='rs:data']")
          .attr('ListItemCollectionPositionNext')
        $(xData.responseXML)
          .SPFilterNode('z:row')
          .each(function() {
            news = 'HTML CODE'
            $('#news').append(news)
          })
      },
    })

I don't know but CAMLQueryOptions never works and never works paging. Please help for forward paging

Answer 1

Try using the following script:

Note: Change the listname, pagesize and other information based on your requirement.

<script type="text/javascript" src="https://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type="text/javascript">
var context, 
    web, 
    spItems, 
    position, 
    nextPagingInfo, 
    previousPagingInfo, 
    listName = 'cl02', 
    pageIndex = 1, // default page index value 
    pageSize = 2, // default page size value 
    list, 
    camlQuery, 
    sortColumn = 'PublishDate'; // this is sort column, you can add more than one column, but you should add it also to CAML Query & managePagerControl function

// This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model  
$(document).ready(function () { 
    SP.SOD.executeFunc('sp.js', 'SP.ClientContext', function () {
        context = SP.ClientContext.get_current(); 
        list = context.get_web().get_lists().getByTitle(listName); 
        camlQuery = new SP.CamlQuery(); 

        $("#btnNext").click(function () { 
            pageIndex = pageIndex + 1; 
            if (nextPagingInfo) { 
                position = new SP.ListItemCollectionPosition(); 
                position.set_pagingInfo(nextPagingInfo); 
            } 
            else { 
                position = null; 
            } 

            GetListItems(); 
        }); 

        $("#btnBack").click(function () { 
            pageIndex = pageIndex - 1; 
            position = new SP.ListItemCollectionPosition(); 
            position.set_pagingInfo(previousPagingInfo); 
            GetListItems(); 
        }); 

        GetListItems(); 
    });
}); 

function GetListItems() { 
    //Set the next or back list items collection position 
    //First time the position will be null 
    camlQuery.set_listItemCollectionPosition(position); 

    // Create a CAML view that retrieves all contacts items  with assigne RowLimit value to the query 
    camlQuery.set_viewXml("<View>" + 
                                "<ViewFields>" + 
                                       "<FieldRef Name='Title'/>" + 
                                       "<FieldRef Name='Detail'/>" + 
                                       "<FieldRef Name='PublishDate'/>" + 
                                       "<FieldRef Name='ID' />"+
                                       "<FieldRef Name='Attachments' />"+
                                  "</ViewFields>" + 
                               "<Query>" + 
                                "<Where>" +
                                    "<And>" +
                                      "<Or>" +
                                          "<Contains>" +
                                            "<FieldRef Name='Title' />" +
                                            "<Value Type='Text'>KFH</Value>" +
                                          "</Contains>" +
                                          "<Contains>" +
                                            "<FieldRef Name='Detail' />" +
                                            "<Value Type='Note'>KFH</Value>" +
                                          "</Contains>" +
                                      "</Or>" +
                                      "<Eq>" +
                                          "<FieldRef Name='Active' />" +
                                          "<Value Type='Choice'>Yes</Value>" +
                                      "</Eq>" +
                                    "</And>" +
                                    "</Where>" +
                                    "<OrderBy>"+
                                    "<FieldRef Name='"+sortColumn+"' Ascending='False' />"+
                                    "</OrderBy>"+
                               "</Query>" + 
                               "<RowLimit>" + pageSize + "</RowLimit></View>"); 

    spItems = list.getItems(camlQuery); 

    context.load(spItems); 
    context.executeQueryAsync( 
            Function.createDelegate(this, onSuccess), 
            Function.createDelegate(this, onFail) 
        ); 
} 

// This function is executed if the above OM call is successful 
// This function render the returns items to html table 
function onSuccess() { 

    var listEnumerator = spItems.getEnumerator(); 
    var items = []; 
    var item; 

    while (listEnumerator.moveNext()) { 
        item = listEnumerator.get_current(); 
        items.push("<td>" + item.get_item('Title') + "</td><td>" + item.get_item('Detail') + "</td><td>" + item.get_item('PublishDate') + "</td><td>" + item.get_item('ID') + "</td><td>" + item.get_item('Attachments') + "</td>"); 
    } 

    var content = "<table><tr><th>Title</th><th>Detail</th><th>PublishDate</th><th>ID</th><th>Attachments</th></tr><tr>" 
                + items.join("</tr><tr>") + "</tr></table>"; 
    $('#content').html(content); 

    managePagerControl(); 
} 

function onFail(sender, args) {
    console.log('Request failed. ' + args.get_message() +
    '\n' + args.get_stackTrace());
}

function managePagerControl() { 

    if (spItems.get_listItemCollectionPosition()) { 
        nextPagingInfo = spItems.get_listItemCollectionPosition().get_pagingInfo(); 
    } else { 
        nextPagingInfo = null; 
    } 

    $("#pageInfo").html((((pageIndex - 1) * pageSize) + 1) + " - " + ((pageIndex * pageSize) - (pageSize - spItems.get_count()))); 

    previousPagingInfo = "PagedPrev=TRUE&Paged=TRUE&p_ID=" + spItems.itemAt(0).get_item('ID') + "&p_" + sortColumn + "=" + encodeURIComponent(spItems.itemAt(0).get_item(sortColumn).toISOString()); 

    if (pageIndex <= 1) { 
        $("#btnBack").attr('disabled', 'disabled'); 
    } 
    else { 
        $("#btnBack").removeAttr('disabled'); 
    } 

    if (nextPagingInfo) { 
        $("#btnNext").removeAttr('disabled'); 
    } 
    else { 
        $("#btnNext").attr('disabled', 'disabled'); 
    } 

}
</script>
<div id ="content"></div>
<div><input type="button"id="btnBack" value="Back"/><span id="pageInfo"></span><input type="button"id="btnNext" value="Next"/></div>

Testing results:

reference:

https://social.technet.microsoft.com/wiki/contents/articles/18606.sharepoint-2013-paging-with-sharepoint-client-object-model.aspx

Code example:

https://code.msdn.microsoft.com/sharepoint/SharePoint-JSOM-list-5104ca92