Sharepoint REST API - Using caml query and select/expand to get lookup data

Question

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.

Answer 1

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>'