20

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!

  • 1
    This doesn't work because List Author isn't exposed as a filterable parameter to REST api or CSOM. You can filter by Author but it'd require more work. The SchemaXml of the list (which you can get using REST) for eg. would return the List Author. You'd have to write JS to parse the XML and get the author. – Akhoy Dec 9 '15 at 11:55
  • 1
    Thanks for the response. Because comments are limited I had to attach the query examples here pastebin.com/zem5tWfy Modifying the first example to request data from Lists(guid'...')/Items, Lists(guid'...') and Lists you have three distinct outcomes. First one works as expected. Second one won't error but will ignore your filters, so I guess it finds the field but doesn't properly filter on it. The last one errors because it can't find the field. The havior is the same when using other fields as well, so it must be universal to all Lookup fields. - I will look into SchemaXml – Alex Pedersen Dec 9 '15 at 12:17
  • How deep do you need to filter? Ive been filtering on author on a component lately and that works fine: PT.Utils.GetRequestUrl() + "/_api/web/lists/getbytitle('FeedLikes')/items?$select=Id,FeedItem/ID,Author/Title,Author/Id&$expand=FeedItem/ID,Author/Id&$filter=FeedItem/ID eq '" + id + "'", – Pantani Feb 5 '16 at 7:35
0

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.

0

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

-1
$.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) {
        }
    });

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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