Combine REST query "GetItems" action with OData $filter parameter

Question

I'm able to call GetItems through REST by using an example by Erik C. Jordan.
Now I'm trying to combine this with a simple OData $filter parameter.

I have a REST query like this (jQuery on SharePoint Online):

var query = {
    ViewXml: "<View><Query><Where><Contains><FieldRef Name='Title'/>\
        <Value Type='Text'>hello</Value></Contains></Where></Query></View>"
};

var params = $.param({
    $top: 5,
    $filter: "substringof('sdf',Title)"
});

params = params + '&@q=' + JSON.stringify(query);

$.ajax({
    type: "POST",
    dataType: "JSON",
    headers: {
        "X-RequestDigest": $("#__REQUESTDIGEST").val()
    },
    url: "/_api/Web/Lists/GetByTitle('Test')/GetItems(query=@q)?" + params
});

This query will fail with the message Field or property "Title" does not exist..
If I remove the parameter $filter it works fine. Other parameters such as $select and $top works fine.
I have tried to add <ViewFields><FieldRef Name='Title'/></ViewFields> to my View Xml without luck.

Any suggestions?

Ashwin Kheta · Accepted Answer · 2016-12-13 08:04:48Z

If u Do want to mix the OData with CAML then I would suggest that First Make the CAML query and later attach the $filter as the filter operates at last and needs a set of Results which are mentioned in your query from which the filtering process would be performed and not vise versa. Well Its simple logic and I don't think I need to provide further explanation to this matter still if all the querying and filtering process is done by OData method only then it doesn't matter whether you mention $filter first and condition later as it itself identifies which operation needs to be performed first irrespective to order in which you specify it but since you are using two different methods it is your responsibility to order your operations in a just manner.

var params = $.param({
     $top: 5
});
params = params + '&@q=' + JSON.stringify(query);
var params1 = $.param({
     params + '$filter': "substringof('sdf',Title)"
});

Or how ever u wish but make sure that $filter remains after the query.
Thanks

Hi. Thank you for your answer! The order does, as far as I can see, make no difference. But combining CAML and $filter does seem to work now on O365. So thanks for bringing this one up again! I think what has happened is simply that Microsoft have added the support. I tested putting $filter at the end on an older 2013 install and it does not work. Here is a full example which will run in console on O365 (swapping order does nothing, but the combining does work): gist.github.com/eirikb/a247078160d9ca4349a3d0bd29eff642 — eirikb
– eirikb, Commented Dec 13, 2016 at 9:59

Vadim Gremyachev · Accepted Answer · 2014-10-30 21:30:15Z

It does not seem possible to specify $filter query option for /_api/web/lists/getbytitle(listtitle)/getItems(query) endpoint.

But the specified REST query parameters could be defined using plain CAML query:

$top=5 -> <RowLimit>5</RowLimit>
$filter=substringof('Orders',Title) -> <Where><Contains><FieldRef Name='Title'/><Value Type='Text'>Orders</Value></Contains></Where>

There is another approach that allows to specify CAML query via payload of request body instead of query string as demonstrated below:

function postJson(endpointUrl,payload,success,failure)
{
    $.ajax({
      type: "POST", 
      headers: { 
            "accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
      }, 
      data: JSON.stringify(payload),
      url: endpointUrl, 
      success: success,
      failure: failure 
   });
}

function getListItems(listTitle, queryViewXml,success,failure)
{
   var queryPayload = { 
       'query':{ 
           '__metadata': { 'type': 'SP.CamlQuery' },
           'ViewXml': queryViewXml
        } 
   }; 
   var endpointUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems";
   postJson(endpointUrl,queryPayload,
     function(data){
        success(data.d.results);    
     },failure);
}

Usage

getListItems('Tasks',"<View><Query><Where><Contains><FieldRef Name='Title'/><Value Type='Text'>Orders</Value></Contains></Where></Query><ViewFields><FieldRef Name='Title'/></ViewFields><RowLimit>1</RowLimit></View>",
  function(items){
      //print info  
      for(var i = 0; i < items.length; i++) {
          console.log(items[i].Title);   
      }
  },
  function(error){
     console.log(JSON.stringify(error)); 
  });

Sorry but this doesn't answer my question. I know I can rewrite CAML to $filter, but I don't have any tool to magically convert ViewQuery to $filter. Note that, as I said, $top actually works, no need to "convert" that. I'm very tempted to upvote just because you say that query can be moved to POST data instead of querystring, which is very important, but it's more a comment than an answer IMHO. — eirikb
– eirikb, Commented Oct 30, 2014 at 22:07
This is probably as close as you're going to get to a good answer -- for some reason (maybe it's an actual bug even) you don't seem to be able to specify filtering on fields with the getItems endpoint. Vadim's suggestion to put all of the logic into the CAML query is probably the easiest path to do what you're trying to do, otherwise try bringing all of the logic back out into the URL with OData operators...not exactly what you want, but seems to be what works currently. — John-M
– John-M, Commented Oct 31, 2014 at 20:08
@John-o I probably misunderstood a bit. Using only CAML is what I do today, I combine view query with my own custom query (see my answer below). I thought Vadim meant to not use view query but just make my own CAML from ground up. This is the most correct answer then :) — eirikb
– eirikb, Commented Nov 1, 2014 at 18:23

eirikb · Accepted Answer · 2014-11-01 18:21:20Z

I will accept the answer by @vadim-gremyachev based on the statement;

It does not seem possible ...

And the suggestion to use only CAML.

This is actually what I do today and so I decided to write an additional answer to my own question, with the approach I'm already using (just with the query-as-body fix), where I combine view query with my own custom one. Example below is from real world code where I use Angular, and jQuery is not included. I have removed some parts, but the approach is simple; given three variables listId, viewId and an additional CAML query additinalQuery the example code will return a promise with data filtered by both view query and a custom one.

var apiUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/";
var listUrl = apiUrl + "Web/Lists('" + listId + "')";
var viewUrl = listUrl + "/Views('" + viewId + "')";
var digest = angular.element(document.getElementById("__REQUESTDIGEST")).val();

return $http.get(viewUrl, {
  params: { 
    $select: "ViewFields,ViewQuery",
    $expand: "ViewFields"
  }
}).then(function(res) {
  return {
    query: res.data.ViewQuery,
    fields: res.data.ViewFields.SchemaXml
  };
}).then(function(res) {
  var query = res.query.match(/<where>(.*)<\/where>/i);
  query = query ? ("<And>" + query[1] + additionalQuery + "</And>") : additionalQuery;
  return "<View><Query><Where>" + query + "</Where></Query><ViewFields>" + res.fields + "</ViewFields></View>";
}).then(function(viewXml) {
  return $http.post(listUrl + "/GetItems", {
    query: {
      ViewXml: viewXml
    }
  }, {
    headers: {
      "X-RequestDigest": digest
    }
  });
});

Not only is the "combining" of CAML queries (as seen above) crude and ugly, it is also much more cumbersome to build (think about query lookup with $filter vs CAML).

Fox · Accepted Answer · 2014-10-30 00:22:45Z

1

You should not be using CAML in your OData Query. You are confusing CAML and Odata.

Your URL should look something like this:

[/_api/Web/Lists/GetByTitle('Test')/items?$select=Title&$filter=Title eq 'Test']

Also note you can test it straight in your browser

http://siteurl/_api/Web/Lists/GetByTitle('Test')/items?$select=Title&$filter=Title eq 'Test'

answered Oct 30, 2014 at 0:22

Fox

2,6761 gold badge19 silver badges30 bronze badges

3

I should definitely be mixing CAML and OData. My example code let you query a list based on a view, in my experience users often wants data from their views. Imagine the possibilities if you could mix the users list view with your own custom filtering

eirikb
– eirikb

2014-10-30 06:07:45 +00:00
Commented Oct 30, 2014 at 6:07

Add a comment |

Stack Exchange Network

Combine REST query "GetItems" action with OData $filter parameter

4 Answers 4

Your Answer

Linked

Hot Network Questions

Combine REST query "GetItems" action with OData $filter parameter

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions