5

I have a Sharepoint app and I use a caml query to filter the data I get back:

    var caml = "<View><Query><Where><And><Contains><FieldRef Name='PublishingPageLayout' /><Value Type='URL'>" + pageLayoutName + "</Value></Contains><Neq><FieldRef Name='NewsCategory' /><Value Type='Lookup'>Memo mailer</Value></Neq></And></Where><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy></Query></View>";
    var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems@target='" + hostWebUrl + "'";
    var requestData = '{"query": { "__metadata": { "type": "SP.CamlQuery" }, "ViewXml": "' + caml + '"}}';
    return $.ajax({
        url: url,
        type: "POST",
        data: requestData,
        headers: {
            "Accept": "application/json; odata=verbose",
            "X-RequestDigest": formDigest,
            "Content-Type": "application/json; odata=verbose"
        },
        dataType: "json"
    });

This works and I get data back. Now the issue I have is that I'm not getting any lookup data back, I only get the ID's of the lookups I want. To accomplish this I have tried using oData combined with my caml query:

var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems\?$select=*,Author/Title\&$expand=Author\&@target='" + hostWebUrl + "'";

var url = appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists/getbytitle('pages')/GetItems?$select=*,Author/Title&$expand=Author&@target='" + hostWebUrl + "'";

This results in a bad request error:

{"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The $expand query is not valid for field 'Author'."}}}

To overcome this error I removed my caml query and tried using a get request using odata $filter (I need to filter by the PublishingPageLayout field) $filter=substringof(PublishingPageLayout, "NewsLayout.aspx") But again I keep getting an error:

The field 'PublishingPageLayout' of type 'URL' cannot be used in the query filter expression.

I've had a look at oData and I think it only lets you filter on specific fields?

My question is how can I use caml and odata together to get back all of my data including my lookup fields in one call?

If I can't combine them both, is there another way using the rest api to get back all my data and all my lookup data?

I don't want to make other ajax calls to get the lookup data back as I have at least 5-6 lookup fields on each item I'm querying against and I don't want to make 6 more ajax calls just to get all my data back!

Thanks for any input.

1 Answer 1

10

As far as I know it is not supported to apply $filter or $select expression for the endpoint:

http://<sitecollection>/<site>/_api/web/lists(listid)/getItems(query)

To summarize:

CAML and OData query could not be combined.

From another hand, since expanding of User Fields is not supported in CSOM/REST i would suggest to construct your query using OData query operations.

Example that demonstrates how to retrieve several lookup fields (Author and Editor titles):

 /_api/web/lists/getbytitle('pages')/items?$select=*,Author/Title,Editor/Title&$expand=Author,Editor

Regarding filtering expression, PublishingPageLayout field cannot be used in substringof function since field of type 'URL' is not supported.

But you could consider the following approach, to filter by ContentType field instead as demonstrated below:

/_api/web/lists/getbytitle('pages')/items?$select=*,Author/Title,Editor/Title&$expand=Author,Editor&$filter=ContentTypeId eq '<contenttypeid>'
2
  • 1
    Thanks mate thats worked. I also need to filter the list by created date. I've tried a few things but none of them are working. Is it even possible?
    – Paddy1990
    Mar 26, 2015 at 16:43
  • 2
    Is there a way to use CAML with REST API ? What I am interested in is to get data with REST API by passing CAML. I can not use $filter because my query is complex and at the same time I do not want to return all columns so need some way so that I can request desired columns. Sep 17, 2015 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.