Empty results when filtering a large list

[martelo]

First of all, thanks @koltyakov for the outstanding work here. Really appreciate it!

So far I've been using the library to retrieve items from lists within the 5k threshold. But recently I had to extend my app to retrieve data from a large list (~10k)... I'm using indexed fields to filter the results, tried a few different combinations, without success.

The code below returns a Microsoft.SharePoint.SPQueryThrottledException as expected, the filtered results surpasses the 5k threshold.

items, err := sp.Web().Lists().GetByTitle("List").Items().Select("*").Filter("Modified gt '2021-01-01'").Get()

However, if I tune the date to something more recent, say '2023-01-01', I get nothing... items.Data() comes empty. Even though I know for sure the list has numerous items modified within 2023...

I consistently get an empty result with any combination/variation of filter based on indexed fields that could narrow the results under the 5k threshold.

Am I missing something here?

Versions SharePoint Online github.com/koltyakov/gosip v0.0.0-20231003001958-007c8072d71c

[koltyakov]

Hi @martelo, thank you for a good feedback!

I believe you need to use datetime in front of your date in REST API, e.g. .Filter("Modified gt datetime'2021-01-01'").

Not sure, what's your scenario for processing a large list; interactively respond with data for a request or sync something on a background. If it's a background job you can actually paginate a list of literally any size of not applying a custom order and filter and using pagination tokens (some details can be found here https://github.com/koltyakov/spsync).

Also, for debugging you can use .ToURL() and checking if the REST API accept constructed query. In the end of the day 90% of the queries built under the hood of the library are just SharePoint REST API calls.

[martelo]

I originally tried with the datetime in front of the date, but for me it leads to a Microsoft.SharePoint.Client.InvalidClientQueryException. I'm using this great cheatsheet as a reference for my Filter queries: https://tomriha.com/wp-content/uploads/2021/03/FilterQuery-Cheatsheet.pdf

Apologies, I failed to provide enough context about my use-case... I have an API with a few GET endpoints in front of Sharepoint... Access to this Sharepoint site and these specific lists is retrict to one particular team, but there are fragments of information we should make available for other teams. My most common scenario is looking for a particular item (i.e., .Filter("IndexedField eq 'StringValue'"). And that should return a single value every time.

I'll give it a try with the .ToURL method and let you know how it goes!

Thanks for the fast response!

[martelo]

I've tried on Postman using the output of .ToURL() and the response comes empty as well... Anything else I might be missing? i.e., specific configuration detail on this list that is preventing/limiting the results to come?

One thing I noticed is the fact that the default view on this particular large list has pre-defined filters. Wondering if .Filter() would build on top of the existing filters or it would clear existing filters... perhaps? Would it be possible to change the default view within this single line command?

Something like: sp.Web().Lists().GetByTitle("List").Views().GetByTitle("AnotherView").Items().Select("*").Filter("...").Get()

[koltyakov]

REST API doesn't use default view's filters in requests as defaults.

It's possible to get a view's CAML (XML) definition and grab a filter condition from it. Yet, it will be usable in methods which supports ViewXml. It's easier though just to reproduce same filters manually.

[koltyakov]

Just to make sure, I seeded a list with 6K random items in SharePoint Online and used:

items, err := sp.Web().Lists().GetByTitle("Orders").Items().Select("ID,Title").Filter("OrderDate gt '2024-01-01'").Get()
if err != nil {
  log.Fatalf("unable to get items: %s", err)
}
fmt.Printf("Items: %d\n", len(items.Data()))

It worked.

Maybe an issue with the list itself?

[martelo]

Maybe an issue with the list itself?

Yep, I'm thinking it could be the case here. I'll close this issue here since we established there is nothing wrong with the API client. I'll update this issue if I can make it to work. Thanks @koltyakov !