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