Monday, 31 October 2011

Specifying Sort Order When Querying the SharePoint List Web Service with CAML and jQuery (via Lists.asmx)

I had a couple of problems today with specifying the sort order of data returned from a CAML query via a jQuery AJAX call. It seemed that no matter what setting I put in, it always seemed to return an unordered resultset.

I resolved the problem by looking at the requests in Fiddler that were produced by U2U CAML query Builder. The main problem was that I wasn't correctly wrapping the CAML "Query" node in a parent "query" node. Now you only have to do this when you're doing jQuery calls directly against web services - and are shielded from this somewhat when you are calling the SharePoint client object model (for details you can look here Why I'm using web service calls directly is a discussion for another time (and I wouldn't automatically turn to using it as a preference - as described here - but I'll say for now that it is for consistency with existing code (The client object model is using the web services underneath as well, but has batching facilities). See below for an example of a jQuery call which does sorting that worked for me. U2U CAML Query builder will also give you the valid internal name for the field.

var errorMessage;
  //Add order by if variable is set in other part of page (so it can be switched on or off on a page-by-page basis.
  if (typeof missingCategoryDisplayMode_IsOnline != 'undefined' && missingCategoryDisplayMode_IsOnline == true  ) //Variable should be set by another content query web part on page
   var orderBy = "\

   var contentCategoryCAML = "<?xml version='1.0' encoding='utf-8'?> \
        <soap:Envelope xmlns:xsi='' \
           xmlns:xsd='' \
           xmlns:soap=''><soap:Body> \
           <GetListItems xmlns=''> \
           <listName>Content Categories</listName> \
           <Query xmlns=''>\
           " + orderBy +
           <ViewFields xmlns='' />\
           <QueryOptions xmlns=''/> \
           </queryOptions></GetListItems> \
    url: listUrl,
    type: "POST",
    dataType: "xml",
    data: contentCategoryCAML,
    complete: ContentCategoryResult,
    contentType: "text/xml; charset=\"utf-8\""
  catch (ex)
   displayError("There was an error retrieving data from the Content Categories list. Please see IT support for assistance.");
  function ContentCategoryResult(xData, status)
   $(xData.responseXML).find("z\\:row").each(function () 
      var Title = $(this).attr("ows_LinkTitle");           
      arrContentCat.push(Title);  //Adding the results in an array

This adds items to an array that are used later for rendering. I needed a different sort order based on the page - so this was done using a seperate javascript file that is placed into a Content Editor Web Part on pages to set the sort order variable.

I also use the SharePoint client model to programatically determine the site collection path and to ensure that the list web service of the current site collection root is used (you can't determine that just by the url):

function MenuInitialize()
     var clientContext = new SP.ClientContext();
        var siteColl = clientContext.get_site();
        myweb = siteColl.get_rootWeb();
        /* Execute async required*/
        clientContext.executeQueryAsync(Function.createDelegate(this, executeRequestAndRender), Function.createDelegate(this, getFailed));
 function executeRequestAndRender() {
  var siteCollectionUrl = myweb.get_serverRelativeUrl();
  var listWebServiceUrl = siteCollectionUrl + "/_vti_bin/lists.asmx"
  //When at the root, siteCollectionUrl is '/', but other sites have /sitecollection/sites
  listWebServiceUrl = listWebServiceUrl.replace('//', '/');
  /* When complete, we can render requests */

For the SharePoint Client Object model to work of course, you should always tell Script on demand (SOD) to process the code that references the SP client object model only when the SP.js file has been loaded:

 /*Ensure that the Sp.js is loaded (using Script on Demand (SOD) SharePoint library) so the client object model functions correctly */
 SP.SOD.executeOrDelayUntilScriptLoaded(MenuInitialize, 'SP.js');


No comments: