Finding pages that are pending approval across an entire site collection

Question

I am in the process of creating an administrative view which allows our intranet authors to view all the pages across the entire site collection that are awaiting their approval.

As our site collection is very large (about 1,100 webs with >12,000 documents and pages) I would prefer to cycle over the fewer SPWeb objects rather than each individual document or page as this would greatly reduce the time taken to display the data.

I eagerly dived in expecting the SPWeb.Workflows member to be populated with all the workflows that are running in that web but the collection is empty! When I access the listitem of the page that requires approval, I can see the workflow there.

What would be the recommended approach to get the info of the pages pending approval along with their workflow, but not by going through each item across the entire site?

Answer 1

My first thought here is: use the SharePoint Search.

If there are documents/pages waiting on approval, there usually is a task assigned to an author for approving. Use SharePoint Search to search for Content Type Task and then filter for the current user. This way you use the info already stored by search. Easy and fast!

Ans you can create a very powerful webpart for each author to see his/her pending tasks...

Please see this video by Scott Hillier: http://channel9.msdn.com/Blogs/matthijs/Creating-Search-Based-Solutions-with-SharePoint-2010

Look about half way during the video. You can practically copy the code of the screen to get it to work :)

And very nice: this works across sites, sitecollections, etc.

Oh, and if you don't wanna go by Tasks, you might take the same approach, but then looking for documents/pages in draft or something like that. The principle still counts: use SharePoint Search!

Hope this helps you on your way.

Answer 2

I ended up using a CrossListQuery to instantly get the data and it works perfect. Here's the code I used:

var query = new CrossListQueryInfo();
query.Lists = "<Lists ServerTemplate=\"107\" MaxListLimit=\"0\" />";
query.Query = "<Where>" +
              "<And>" +
              "<And>" +
              "<Eq>" +
              "<FieldRef Name='WorkflowName' />" +
              "<Value Type='Text'>Page Approval</Value>" +
              "</Eq>" +
              "<Eq>" +
              "<FieldRef Name='Status' />" +
              "<Value Type='Text'>Not Started</Value>" +
              "</Eq>" +
              "</And>" +
              "<Or>" +
              "<Membership Type='CurrentUserGroups'>" +
              "<FieldRef Name='AssignedTo'/>" +
              "</Membership>" +
              "<Eq>" +
              "<FieldRef Name='AssignedTo'></FieldRef>" +
              "<Value Type='Integer'>" +
              "<UserID/>" +
              "</Value>" +
              "</Eq>" +
              "</Or>" +
              "</And>" +
              "</Where>";
query.Webs = "<Webs Scope=\"Recursive\" />";

var clqc = new CrossListQueryCache(query);

DataTable rawResultSet =
    clqc.GetSiteDataResults(SPContext.Current.Site, CrossListQueryCache.ContextUrl(), true).Data;
return rawResultSet.Rows.Cast<DataRow>().Select(row => new SearchResult(row));

The SearchResult object I use is just a custom one to record the information quickly and easily:

public struct SearchResult
{
    public int ItemId;
    public Guid ListId;
    public Guid WebId;

    public SearchResult(DataRow row)
    {
        ListId = new Guid(row.Field<string>("ListId"));
        WebId = new Guid(row.Field<string>("WebId"));
        ItemId = int.Parse(row.Field<string>("ID"));
    }
}