Advanced SharePoint Queries! :)

In the previous post I described two methods where you could fetch any data you needed from a SharePoint list, but sometimes we need more than a simple fetch from a list. One of the situations that I had to deal with in the most recent project I was involved in was fetching data from multiple lists scattered across different sub-sites and this had to be done in a performance optimized method (no recursively looping through all sub sites and searching each list separately!).

Let's consider a concrete example:

Assume we have multiple SharePoint blogs setup under a sub-site called 'blogs.' Now as we all know SharePoint blogs are sub-sites in themselves and posts/comments made to a blog are stored in lists under that sub-site. So if we have multiple blogs setup under the imaginary 'blogs' sub-site we will have a site hierarchy similar to this:

/blogs

/blogs/My Blog

• Posts list
  
• Comments list
  

/blogs/Some Other Blog

• Posts list
  
• Comments list
  

/blogs/XYZ Blog

• Posts list
  
• Comments list
  

…

Now suppose we want to display a 'master moderation list' where an administrator can view all posts/comments that haven't been approved yet (are pending) and then do whatever he/she would do with them (we only care about the first part).

This is where SharePoint Cross List Queries can come to the rescue. By creating a Cross List Query you can retrieve all items from all lists under a specific sub-site in one data table and then perform all needed operations on the result:

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

CrossListQueryInfo qi = new CrossListQueryInfo();

qi.ViewFields = "<FieldRef Name=\"Title\" />" +
"<FieldRef Name=\"Body\" />" +
"<FieldRef Name=\"ID\" />" +
"<FieldRef Name=\"PublishedDate\" />";
qi.Query = "<Where><Eq>" +
"<FieldRef Name=\"_ModerationStatus\" />" +
"<Value Type=\"ModStat\">" +
"Pending" +
"</Value>" +
"</Eq></Where>" +
"<OrderBy>" +
"<FieldRef Name=\"Modified\" Ascending=\"FALSE\" />" +
"</OrderBy>";
qi.RowLimit = 100;
qi.Webs = "<Webs Scope=\"Recursive\" />";
//101 is the id for blog lists. Obviously you have to change this
//for any other list type that you are targeting
qi.Lists = "<Lists ServerTemplate=\"101\" >";
qi.WebUrl = "/blogs";
qi.ShowUntargetedItems = false;

CrossListQueryCache qCache = new CrossListQueryCache(qi);
DataTable dt = qCache.GetSiteData(web);

foreach (DataRow dr in dt.Rows)
{
//do whatever you need to do with each item
}

A couple of useful hints:

• The CrossListQueryInfo class is located in the Microsoft.SharePoint.Publishing namespace so don't forget to add a using for it and a reference to the micrososft.sharepoint.publishing.dll
  
• Cross list queries are very limited in which fields can be fetched or used in the where clause. I've had problems fetching the author of a list item or filtering based on similar fields. A solution that I had to use once was to handle events on the list so when items were added I would copy all the data that I needed into custom hidden fields and then use those fields in the cross list query.
  
• Adding indexed fields will help with the performance of cross list queries.