SharePoint 2013 REST expand and filter issue

Question

I've been using REST for a lot of tasks, and with the recently added $batch endpoint, it's more fun than ever.

There is one thing bugging me though, and I'd like to share it with you, perhaps someone has some input they'd like to share with me.

As we all know we can lookup an item from a list and we can expand the field (if it's a lookup field) to contain the actual value, instead of just the ID of the target item.

//sharepoint/sites/home/_api/Web/Lists(guid'...')/Items
    ?$select=Title, EntityPropertyName/Title
    &$expand=EntityPropertyName
    &$filter=EntityPropertyName/Title eq 'Value'

Basically you can get the items, expand their lookup field(s) and even filter by specific items.

You can even take it one step further, let's go one level above and query all the lists:

//sharepoint/sites/home/_api/Web/Lists
    ?$select=Title, Items/Title, Items/Author/Title
    &$expand=Items, Items/Author
    &$filter=BaseTemplate eq 100

We now get all the lists matching our filter, we select the list title, all the items with their title and author/title values.

Like the first example, one would assume we could perform something like this:

//sharepoint/sites/home/_api/Web/Lists(guid'...')
    ?$select=Title, Items/Title, Items/Author/Title
    &$expand=Items, Items/Author
    &$filter=Items/Author/Title eq 'Jon Smith'

This query will work, but the $filter doesn't do anything when you try to filter on author title.

Let's take it one step further and query all the lists:

//sharepoint/sites/home/_api/Web/Lists
    ?$select=Title, Items/Title, Items/Author/Title
    &$expand=Items, Items/Author
    &$filter=BaseTemplate eq 100 and Items/Author/Title eq 'Jon Smith'

But sadly this query fails because apparently the Author field doesn't exist. Note that this only errors if I try to filter on it, otherwise the query works fine.

The issue I am having is, how do I proceed querying expanded objects? Sometimes it works, sometimes it doesn't, and sometimes it doesn't error. I guess one issue is that the Lists(guid'...') returns one specific list, while when querying the Lists you query a collection. There might be some differences how these are handled.

The only solution I see to this issue, regarding amount of http requests your client has to send, is by using $batch endpoint and use that to bundle the queries, as you would then be able to make a query at a level that works, then append subsequent queries.

You still have the issue where you would have to first query the Lists then map out what you wish to do with the lists returned, then start a new batch job with additional queries. Still better than one http request per query, but not an ideal solution.

I might be missing something, feel free to enlighten me. Other than that I hope someone gets some use out of this post in their own projects.

Edit

I felt like adding a more concrete example. This is a situation I am facing right now.

Here is a large query. The idea is simple, narrow down the results and get everything relevant in one query.

//sharepoint/sites/home/_api/Web
    ?$select=
        Title,
        WebTemplate,
        AppTiles/AppType,
        AppTiles/Target,
        AppTiles/Title,
        Lists/Title,
        Lists/Items/Id,
        Webs/ServerRelativeUrl,
        Webs/Title,
        Webs/WebTemplate,
        Webs/Lists/BaseTemplate,
        Webs/Lists/Title,
        Webs/Lists/Items/Id
    &$expand=
        AppTiles,
        Lists/Items,
        Webs,
        Webs/Lists/Items
    &$filter=
        WebTemplate eq 'STS'
        and AppTiles/AppType eq 3
        and Webs/WebTemplate eq 'STS'
        and Webs/Lists/BaseTemplate eq 100

Sadly the issue is that the filters that try to narrow down on expanded object won't work, but data is still returned. So they are basically ignored. Usually you'd expect an error that the field you reference is invalid. On the other hand it looks logical and you might expect it to work.

I solve this by sending multiple requests so I have to split up where I try to filter "too deep" and do that in the next http request. Not ideal, but works.

If someone knows how you can achieve one big query like what I attempted at, please reply how you managed to do that. Thanks!

Answer 1

Perhaps you could try to retrevie all items in the list first -> store the result as a global variable,

using Javascript you can run a filter:

for example lets call the returned array "returnedArray"

let = returnedArray
let = filteredArray

$(document).ready(function(){
    getData();
})

function GetData(){
$.ajax({
    url: "https://example.example.com/sites/Example/_api/web/lists/GetByTitle('CM System')/items?$top=5000",
    type: "GET",
    headers: {
        accept: "application/json",
    },
    success: function(data){
        returnedArray = data.value
        console.log(returnedArray);
        //Then fun filter data
        filterData();

    },
    error: function(data, errorCode, errorMessage){


        //Output error details to the console
        console.log(errorCode);
        console.log(errorMessage);
    }
})
}

function filterData(){

filteredArray = returnedArray.filter(function(data){ return data.appType == 
'3')})

}

although this may seem long winded due to having to define the filter, im certain that this will work for you.

Once you have filtered your array enough you can then append it into a nice little data table perhaps? using table.draw(filteredArray). - just a thought.

Hope this helps!!

Answer 2

This may sound silly and it is at best a stab in the dark, but as AJAX queries are handled asynchronous, it is possible that the information is not grabbed. This would go some way to explaining why it sometimes works and sometimes does not work.

probably worth trying to implement something like:

function callback(data,status) {setTimeout(function() {
$("#wait").hide();
$("#ajaxdiv").html(data); }, 1500); }

to slow down the call. This will make everything a touch slower... but hopefully may address the issue.

Answer 3

$.ajax(
    {
        url: "/_api/web/lists/getbytitle('List Name')/items?$select=Title,ID,Body,Expires,Modified&$filter=FieldName eq 'Value'&$orderby=Modified desc",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        async: true,
        success: function (data) {
        },

        failure: function (err) {
        }
    });