(Migrated post from June 19th, 2007; Updated on July 12th (see comments))
Facing the challenge to write a custom query that spans multiple lists that contain subfolders and a rather complex set of metadata, we had to evaluate both methods of querying lists:
- SPQuery
- SPSiteDataQuery
Looking at the SPQuery, the implementation is a piece of cake. However, there is a known limitation that SPQuery will return only results of one folder, you'll never get search results from any of the subfolders contained in the library:
(from the WSS 3.0 SDK):
SPQuery can only get the items in the current folder; SPQuery will not get the items in subfolders. This is the design feature in SharePoint Portal Server 2003. To get the items in subfolders, it is necessary to change to the subfolder in SPQuery first. Also it is necessary to specify the format for using dates in the query.
OK, this is what I also knew SharePoint Portal Server 2003… But hey, aren't we in the WSS 3.0 SDK??? And thanks to the tip of "Nick" (see comments) I know know that it is absolutely possible to query also subfolders with the SPQuery: msdn2.microsoft.com/…/microsoft.sharepoint.spquery.viewattributes.aspx
query.ViewAttributes = "Scope="Recursive"";
OK, so far so good. But let's look now at SPSiteDataQuery which is new in WSS 3.0 – and you'll notice!
First of all, make sure that you really read and understand (and least try!) the documentation at http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx.
Only if you stay 100% within the given specification, you'll be able to obtain results, otherwise, you'll always get zero search results.
Here are some of my findings:
- You must write the CAML query without the “<Query>” Element.
- If writing the CAML Query as a string, you must not use any line breaks or additional whitespaces.
- Example of a working query:
"<Where><Eq><FieldRef Name='TestLookupMulti' /><Value Type='Text'>abc</Value></Eq></Where><OrderBy><FieldRef Ascending='False' Name='Title' /></OrderBy>" - Example of an “invalid” query (sorry, I've seen the comment, but this one did not work for me!):
@”<Where>
<Contains>
<FieldRef Name='TestLookupMulti' />
<Value Type='MultiLookup'>SubFolder</Value>
</Contains>
</Where>
<OrderBy>
<FieldRef Ascending='False' Name='Title' />
</OrderBy>"
- Example of a working query:
- In order to query for individual metadata fields (fields that not present in every list like “Title” or “ID”) you must include the “Nullable='true'” Attribute in the <fieldref>-Element of the ViewFields. However: This does not work for lookups and person-fields! If you are using custom lookups, you must restrict the lists that are searched using the <Lists> Element. Here is a working example:
spDataQuery.Lists = "<Lists><List ID='" + myTestListID + "' /></Lists>"; - You may use Lookup and even Multivalue-Lookups for your queries. Even the <Eq> Operator works on Multivalue-Lookups! However, up to my knowledge, it is not possible to show a Multivalue-Lookup in the ViewFields! Every attempt to do this will result in zero search results!
I will now provide a working example that uses bot: SPQuery and SPSiteDataQuery.
Create a PictureLibrary called “MyPictures” with some subfolders. Add the following metadata:
- “Test” … Simple Text
- “TestLookupSingle” … Single-Select Lookup
- “TestLookupMulti” … Multi-Select Lookup
Use the code below in a test windows application (running on the server!) and make sure you have the necessary privileges to execute the query on MOSS:
|
string myTestCAMLQuery = "<Where><Contains><FieldRef Name='TestLookupMulti' /><Value Type='Text'>TestValue</Value></Contains></Where><OrderBy><FieldRef Ascending='False' Name='Title' /></OrderBy>";
// Get test variables SPWeb myTestWeb = new SPSite("http://mho-dev07:8080/").OpenWeb(); // Add your site url/web url here SPList myTestList = myTestWeb.Lists["MyPictures"]; string myTestListID = myTestList.ID.ToString();
// Test 1: use SPQuery SPQuery spQuery = new SPQuery(); spQuery.Query = myTestCAMLQuery; SPListItemCollection spQueryResults = myTestList.GetItems(spQuery); Debug.WriteLine(spQueryResults.Count);
// Test 2: use SPSiteDataQuery SPSiteDataQuery spDataQuery = new SPSiteDataQuery(); spDataQuery.Query = myTestCAMLQuery; spDataQuery.Webs = "<Webs Scope='SiteCollection' />"; // could also use "Recursive"
// Most important: Must restrict to the target lists containing the fields // see http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.viewfields.aspx // for details. spDataQuery.Lists = "<Lists><List ID='" + myTestListID + "' /></Lists>"; // Make sure to include all fields wanted in ViewFields spDataQuery.ViewFields = "<FieldRef Name='TestLookupSingle' />"; spDataQuery.RowLimit = 100; System.Data.DataTable spSiteDataQueryResults = myTestWeb.GetSiteData(spDataQuery); Debug.WriteLine(spSiteDataQueryResults.Rows.Count); |
mhofer1976