Query 1 list from multiple sites using a single REST call

Question

My boss has been looking for a way to do this for a while, I figured I might as well ask around instead of telling him it can't be done without multiple queries to the server.

During a Project Server deployment it is common to add fields to the native Issues and Risks lists that stay inside each Project site. Project Server natively exports Risk and Issue information to its Reporting database, however, it does not take the fields that were added during the deployment.

We want to build a report using Excel Services containing this information without having to query each project site that exists on the server, using the REST API so that it can be used in SPO.

I am aware that I can use the REST query of

/PWA/ProjectSite/_api/web/lists/getbytitle('Risks')/Items 

But what I want would be the equivalent of

/PWA/ProjectSiteA/_api/web/lists/getbytitle('Risks')/Items 
UNION
/PWA/ProjectSiteB/_api/web/lists/getbytitle('Risks')/Items 
UNION
/PWA/ProjectSiteC/_api/web/lists/getbytitle('Risks')/Items 

For those not familiar with Project Server, all Project sites are subsites of the Project Server instance's site collection.

Is there a way (SPSiteDataQuery?) to query all these lists without iterating all the sites through the REST(or CSOM) api?

Answer 1

Unfortunately there is not a way to query multiple lists at the same time except for using search - but this is exactly what the search REST API is intended for.

The problem with the search REST API is that is daunting to look at the results that you get. But essentially all you would have to do is hit a URL like the following end point:

_api/search/query?querytext='contenttype:risk'

You might also want to include some select criteria to reduce the size of your returned payload.

&selectproperties='Author,Path,Title,Url'

The primary search results are in a "Data Table" object format in the following node:

Object.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

For the field title, you can just look at result.Cells.results[3].Value

And for the contained value you'd look at result.Cells.results[6].Value

You get a lot of flexibility with the returned results but those results are also amazing complex compared to what gets returned from the REST API for list items.

Here is an example of how you might do it using jQuery and Q.js. I've not tested this specific code (and I'm sure there are errors) but I have used this exact approach with the ListData.svc REST API from 2010. The problem with this is that pagination of the results becomes difficult unless you want to return every single item to the client for sorting and paging.

$.ajax({
        type: "GET",
        url: "https://domain/PWA/_api/webs?$expand=Webs",
        headers: headers
    }).done(function (data, textStatus, jqXHR) {
        getListData(data.Webs).then(function(response){
            //Do something with the response.
        });
    }).fail(function (jqXHR, textStatus, errorThrown) {
        //Handle errors.
    });


function getListData(webs){
    var promises = [];
    var riskItems = [];
    var defer = Q.defer();
    var i;
    for(i = 0; i < webs.length; i++){
        promises.push(
            $.ajax({
                type: "GET",
                url: webs[i].__metadata.uri + "/lists/GetByTitle('Risks')/items",
                headers: headers
            }).done(function (data, textStatus, jqXHR) {
                riskItems = riskItems.concat(data.d.results);
            }).fail(function (jqXHR, textStatus, errorThrown) {
                //Handle errors.
            })
        );
    }
    Q.all(promises).then(function(){ 
      defer.resolve(parItems); 
    });
    //I'm returning an object like what a list would return
    return defer.promise.then(function(data){
                var retValue = {
                    d: {
                        results: data
                    }
                };
                options.success(retValue);
            });
}