rest - SharePoint Online : REST API for List, 调用筛选器并限制返回项

Question

我正在尝试使用以下 REST API 为列表调用 REST API

https://myweb.sharepoint.com/teams/sites/subwebs/_api/web/lists/GetByTitle('MyList')/Items?
$top=1
&$orderby=ID
&$select=ID,FName,LName,Title
&$filter=Title eq 'Female'

我需要 $filter 可以处理限制为 $top 的记录数。如果未应用 $filter，则 $top 有效。

好吧，我的列表包含超过 5000 个项目。我在对上述 URL 发出 GET 请求时收到以下错误消息

{
"readyState": 4,
"responseText": "{\"odata.error\":{\"code\":\"-2147024860, Microsoft.SharePoint.SPQueryThrottledException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\"}}}",
"responseJSON": {
    "odata.error": {
        "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
        "message": {
            "lang": "en-US",
            "value": "The attempted operation is prohibited because it exceeds the list view threshold enforced by the `enter code here`administrator."
        }
    }
},
"status": 500,
"statusText": "Internal Server Error"
}

Answer 1

异常 Microsoft.SharePoint.SPQueryThrottledException 被抛出，因为 $filter=Title eq 'Female' 查询导致遍历整个列表并检查每一行以查看是否它匹配。

根据 MSDN :

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database.

这就是为什么 $top 查询选项在这里不适用的原因。

避免该问题的一个方法是索引 Title 字段。

转到 List Settings -> Indexed Columns -> Create a new index -> select Title as a Primary Column:

一旦 Title 字段被索引，下面的查询应该成功:

https://site/_api/web/lists/GetByTitle('<list title>')/Items?$top=1&$orderby=ID&$select=ID,Title&$filter=Title eq '<value>'