SharePoint Search – how to return all results

By default, when we use the Search API, SharePoint limits the number of items retrieved. If your query returns a number of items within this limit, then it is not a problem. However, if your query results in a larger set, then by default those items will not be present in the result set.

I noticed that when using KQL, by default SharePoint limits the results to 50 rows. We can explicitly set the RowLimit up to 500. When using the REST API using JavaScript, I noticed that the results were limited to 500. If our result set contains more items, then we need a mechanism to retrieve the other results.

We might want to include all the results in one single unit such as in a report. Or, we might want to show a initial set of results and then page through the remaining results. In either case, we need to be able to retrieve sections of the results at a time. In case we want to create a single report, we can merge them together.

This can be done by keeping the current row index value(which is the last row of the current result set), looping through the pages and updating the current row index after fetching the current page.

For this, lets set the RowLimit to 50. Then, if the TotalRows is greater than the row count of the returned results, get the additional rows.

KeywordQuery keywordQuery = new KeywordQuery(siteCollection);
SearchExecutor searchExecutor = new SearchExecutor();
keywordQuery.QueryText = "KQL Query String";
keywordQuery.StartRow = 0;
keywordQuery.RowLimit = 50;

//retrieve the first page of results
ResultTableCollection resultTableCollection = searchExecutor.ExecuteQuery(keywordQuery);
var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
var resultTable = resultTables.FirstOrDefault();
DataTable resultDataTable = resultTable.Table;

int currentRowIndex = 0;
//Iterate through the rest of the pages
while (resultTable.TotalRowsIncludingDuplicates > resultDataTable.Rows.Count)
{
    //Update the current row index
    currentRowIndex += resultDataTable.Rows.Count;
    resultTableCollection = GetSearchResults(keywordQuery, currentRowIndex, searchExecutor);
    var searchResults = resultTableCollection.FirstOrDefault();
    if (searchResults.RowCount <= 0)
        break;
    else
        resultDataTable.Merge(searchResults.Table);
}

private ResultTableCollection GetSearchResults(KeywordQuery keywordQuery, int startIndex, SearchExecutor searchExecutor)
{
  ResultTableCollection resultTableCollection = null;
  try
  {
    //keyword search using "Default Provider" .
    keywordQuery.ResultsProvider = SearchProvider.Default;
    keywordQuery.StartRow = startIndex;//gets or sets the first row of information from the search results

    // execute the query and load the results into a collection
    resultTableCollection = searchExecutor.ExecuteQuery(keywordQuery);
  }
  catch (Exception ex)
  {
  }
  return resultTableCollection;
}

After executing the above code the resultDataTable will have the complete results even if it is a large result set.

Doing this using JavaScript and REST API, is a bit more tricky. The key thing here is to make the recursive call from the success handler. This is because we want to merge the results of all the individual pages of information.

var allResults;
function yourFunction() {
  var searchUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/search/postquery";
  postData = {
    'request': {
      '__metadata': { 'type': 'Microsoft.Office.Server.Search.REST.SearchRequest' },
      'Querytext': "sharepoint",
      'RowLimit': '50',
      'SortList':
      {
        'results': [
            {
              'Property': 'Created',
              'Direction': '1'
            }
        ]
      }
    }
  };
  allResults = [];
  searchSharePoint(searchUrl, 0);
}

function searchSharePoint(searchUrl, startRow, allResults) {
  //initialize to empty array if it does not exist
  allResults = allResults || [];
  postData.request.StartRow = startRow;

  $.ajax(
  {
    type: "POST",
    headers: {
      "accept": "application/json;odata=verbose",
      "content-type": "application/json;odata=verbose",
      "X-RequestDigest": $("#__REQUESTDIGEST").val()
    },
    data: JSON.stringify(postData),
    url: searchUrl,
    success: onQuerySuccess,
    error: onQueryFail
  });
}

function onQuerySuccess(data) {
  var searchUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/search/postquery";
  var results = data.d.postquery.PrimaryQueryResult.RelevantResults;
  allResults = allResults.concat(results.Table.Rows.results);
  if (results.TotalRows > postData.request.StartRow + results.RowCount) {
    reportSearch(searchUrl, postData.request.StartRow + results.RowCount, allResults);
  }
  else if (allResults != null && allResults.length > 0) {
    //process allResults
  }
  else {
    if (reportDataTable != null) {
      reportDataTable = [];
    }
    // Show Message No results found
  }
}

function onQueryFail(data, errorCode, errorMessage) {
  //log error message
}

Hope this helps.

2 thoughts on “SharePoint Search – how to return all results”

Leave a Reply

Your email address will not be published. Required fields are marked *