SharePoint REST API Paging Returns 0 results

[dsm0880]

Category

• Question
• Typo
• Bug
• Additional article idea

Expected or Desired Behavior

I'm calling the SharePoint REST API using the RenderListDataAsStream endpoint. We use the endpoint and then pass in the body a CAML query that looks like this:

<View Scope="Recursive">
    <ViewFields>
        <FieldRef Name="ID" />
        <FieldRef Name="Title" />
        <FieldRef Name="FileRef" />
        <FieldRef Name="Date1" />
        <FieldRef Name="MultiLineText1" />
        <FieldRef Name="Person1" />
        <FieldRef Name="Choice1" />
        <FieldRef Name="Text1" />
        <FieldRef Name="Number1" />
        <FieldRef Name="Lookup1" />
        <FieldRef Name="Choice2" />
        <FieldRef Name="Lookup2" />
        <FieldRef Name="Lookup3" />
    </ViewFields>
    <RowLimit Paged="TRUE">10</RowLimit>
    <Query>
        <Where>
            <And>
                <Contains>
                    <FieldRef Name="Text1" />
                    <Value Type="Text">477</Value>
                </Contains>
            </And>
        </Where>
        <OrderBy>
            <FieldRef Name="DueDate1" />
        </OrderBy>
    </Query>
</View>

We currently have a list that exceeds the list view threshold and have therefore placed in the appropriate indexes on the list columns. In this case Text1 and DueDate1. When paging through the results I expect to get back the RowLimit that was placed in the call or until I have reached the end of the list results.

Observed Behavior

Instead of receiving the desired results I get back the following 200 response (not 500 list view threshold exceeded response)

{
    "Row": [],
    "FirstRow": 1,
    "FolderPermissions": "0x7fffffffffffffff",
    "LastRow": 0,
    "RowLimit": 10,
    "NextHref": "?Paged=TRUE&ix_Paged=TRUE&ix_ID=1030979&PageFirstRow=1&View=00000000-0000-0000-0000-000000000000",
    "FilterLink": "?",
    "ForceNoHierarchy": "1",
    "HierarchyHasIndention": "",
    "CurrentFolderSpItemUrl": ""
}

Which is super weird as it is giving me a next page but did not actually get any items on the first page. If I OrderBy Ascending things work, but descending does not.

Steps to Reproduce

So how to reproduce :) You can see that the ID in the returned result is quite high (in the millions). We had an issue where items were creating and deleting for a number of weeks, this has finally been stopped but I'm afraid this messed up the SharePoint Online lists somehow. I've tried removing and adding the indexes back but that did not fix it. Ultimately to reproduce someone would need to create items and then delete them, check if this affects their paging. Maybe someone at Microsoft can help as I'm hoping a timer job or something we can't see on our end needs to run to fix things but thought I would submit a ticket. Hope someone can help.

Daniel

[msft-github-bot]

Thank you for reporting this issue. We will be triaging your incoming issue as soon as possible.

[dsm0880]

@andrewconnell I'm now getting this same issue on additional sites. Should I go another route than through ticketing in GitHub?

[andrewconnell]

Dunno... hard to repro if you're looking at a list that big. @JeremyKelley any suggestions on the best approach to get help on this?

[dsm0880]

@andrewconnell @JeremyKelley Ok in looking at this more it appears that the indexes may be broken. We re-created the site and content in a new site and everything works as expected. If that is the case then is deleting the affected columns and re-creating the only option? How does this kind of thing happen in the first place?

[louis-atvero]

Hi, I'm getting a problem like this on a list with 13,000 +- items, and a filter that should return 31 items. We're using RenderListDataAsStream with ViewXml, like OP's example, because this is what the SP List view does, and indexed our relevant fields before adding any items to the list.

In my case, I apply a sort and filter, and I'm paging 100 items at a time.
The first page gets one result, second page gets 0 results, and the third page gets the remaining 30 results.

It indicates to me that under the hood, even though I'm only asking for 100 items, each paged query does something like this:

• SP Gets the first block of 5000 items
• Orders / Filters it
• Returns any matches from within that 5000 items

Whereas on the SP List view, all matches are loaded properly on the first attempt, with no next page, as though it loaded all 13k rows at the same time. Which is the behaviour we need.

Is this correct?

@dsm0880
My suggestion was to progressively make repeated requests until I have at least my page length of items or more, then stop requesting until the user wants to load more, which is pretty easy using React.js.
Maybe this can help you

Though instead I've been asked to do more research and try to implement the SP way of doing it.
However the docs for RenderListDataAsStream are not sufficient, pnpjs doesn't make it obvious, I'm poking around in the dark here, story of my life working with SPO.

Can anyone help?

[dsm0880]

Hey @ng-lboyle, so we ended up going with the repeated queries until the row limit is hit which you can see is how SharePoint handles such situations. Just to let you know, I was able to escalate this to the development team within Microsoft through premier support and got the response that they are not going to fix it given the LOE and the fact that it is an edge case. There is no "SharePoint" way in my mind, but instead a javascript approach as you indicated of checking that your total returned items matches your row limit, if not then re-query for the next page. If others make noise then maybe Microsoft will re-consider trying to address. @andrewconnell

[dsm0880]

@ng-lboyle Also, now a couple of things that may help your query that Microsoft told me:

1. Don't use Or statements where possible
2. Sorting by date descending can be very intensive on the server

[louis-atvero]

@dsm0880 Thanks for your reply, that'll be very helpful

[JackHu88]

Can anyone help me？
renderListDataAsStream can not work when filter by date field, no results return. see below:
{Row: [], FirstRow: 1, FolderPermissions: "0x7fffffffffffffff", LastRow: 0, RowLimit: 5000,…} CurrentFolderSpItemUrl: "" FilterLink: "?" FirstRow: 1 FolderPermissions: "0x7fffffffffffffff" ForceNoHierarchy: "1" HierarchyHasIndention: "" LastRow: 0 NextHref: "?Paged=TRUE&ix_Paged=TRUE&ix_ID=5072&PageFirstRow=1&View=00000000-0000-0000-0000-000000000000" Row: [] RowLimit: 5000

My Code:

        let firstPageResult = await pnp.sp.web.lists.getByTitle("Construction Permit").renderListDataAsStream({
          ViewXml:  `<View><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
            <Where>
            <Geq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='FALSE'><Today OffsetDays ='-3' /></Value></Geq>
            </Where>
          </Query>
          <ViewFields>
          <FieldRef Name='Author'/><FieldRef Name='Status'/><FieldRef Name='Created'/><FieldRef Name='ID'/>
</ViewFields><RowLimit Paged='TRUE'>5000</RowLimit></View> `
        });


                let nextPageResult = await pnp.sp.web.lists.getByTitle("Construction Permit").renderListDataAsStream({
                  ViewXml: `<View><Query><OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
        <Where>
            <Geq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='FALSE'><Today OffsetDays ='-3' /></Value></Geq>
            </Where>
          </Query>
          <ViewFields>
          <FieldRef Name='Author'/><FieldRef Name='Status'/><FieldRef Name='Created'/><FieldRef Name='ID'/>
        </ViewFields><RowLimit Paged='TRUE'>5000</RowLimit></View> `,
                  Paging: firstPageResult.NextHref.substring(1)
                });`