CAML Query Join Lists

I was in a situation where I had a parent list with some details and then a separate list for child items. The child list had a lookup to the parent list to make the association between the two. I needed a query which allowed me to search on some fields in the parent list and some fields in the child list.

Approach

When I was thinking about this I came up with a few different ideas which are each discussed below:

  1. Multiple Caml Queries. One option was I could run a Caml query against the parent list to get the items which meet the parent item criteria and then do another Caml query looking for items in the child list which are associated to the items returned in the parent Caml query but also meet the filtering on the child list. Obviously this is not idea as it would involve a decent amount of code to do but would also be fairly inefficient as well.
  2. Linq to SharePoint. While this would probably have been the easiest approach as you can very quickly join two lists using Linq, once you have your entry classes generated,  but I decided against this approach. My reasoning was based around a solution design perspective. I decided that while Linq to SharePoint would prove beneficial in this situation it would require adding and maintain another element in the solution thus increasing the complexity.
  3. Join Caml Query. This was something I had never done before but I was aware it was one of the new features in SharePoint. I done some research and it seemed like a fairly straightforward approach so I decided to go with this.

 

Method

There are various articles out there on how to join lists via a Caml query so I won’t go into a great deal of detail only the points I found interesting.

The first thing is the Caml query will be created as you normally would be it needs to be run against the child list not the parent list.

In order to map the lists together you use the Joins property of the SPQuery object. As I mentioned there is a lot of information out there for more complicated joins but in my situation it was simply joining parent and child lists together, see example below.

Join Lists
  1. StringBuilder joinDetails = new StringBuilder();
  2.         joinDetails.Append("<Join Type='INNER' ListAlias='ParentListName'><Eq><FieldRef Name='ChildListLookupColumnName' RefType='Id'/><FieldRef List='ParentListName' Name='ID'/></Eq></Join>");           

 

With the join created the next step is to set up the projected fields, these are the fields from the parent list which you want to display or filter against in the where clause, see below. The Name element is any name you want to give it and this will be used in the view fields and query sections of the SPQuery object. As far as I can see the type is always Lookup. The ShowField element is the name of the field in the parent list you want to map to this new field.

Projected Fields
  1. StringBuilder projectedFields = new StringBuilder();
  2.         projectedFields.Append("<Field Name='ParentListField1' Type='Lookup' List='ParentListName' ShowField='Field1'/>");
  3.         projectedFields.Append("<Field Name='ParentListField2' Type='Lookup' List='ParentListName' ShowField='Field2'/>");
  4.         projectedFields.Append("<Field Name='ParentListField3' Type='Lookup' List='ParentListName' ShowField='Field3'/>");
  5.         projectedFields.Append("<Field Name='ParentListField4' Type='Lookup' List='ParentListName' ShowField='Field4'/>");
  6.         projectedFields.Append("<Field Name='ParentListField5' Type='Lookup' List='ParentListName' ShowField='Field5'/>");
  7.         projectedFields.Append("<Field Name='ParentListField6' Type='Lookup' List='ParentListName' ShowField='Field6'/>");

 

You must ensure all projected fields are listed in the view fields SPQuery property. Again anyone who has done basic Caml queries will have seen this before the only consideration is when using fields from the parent list you have to use the name set in the projected fields not the name of the column in the parent list, see below.

View Fields
  1. StringBuilder viewFields = new StringBuilder();
  2.         viewFields.Append("<FieldRef Name='ParentListField1'/><FieldRef Name='ParentListField2'/><FieldRef Name='ParentListField3'/>");
  3.         viewFields.Append("<FieldRef Name='ParentListField4'/><FieldRef Name='ParentListField5'/><FieldRef Name='ParentListField6'/>");

With the join between the lists done and the mapping for the fields in the parent list you can then write your Caml query as per normal and filter against details in the parent list, see below for a simple example Caml query.

Example Caml Query
  1. sb.Append("<Where><IsNull><FieldRef Name='ParentListField1' /></IsNull></Where>");

 

The final element is to associate all these with your SPQuery object and then pass this to the list GetItems method.

Associated with SPQuery
  1. var query = new SPQuery();
  2.         query.Joins = joinDetails.ToString();
  3.         query.ProjectedFields = projectedFields.ToString();
  4.         query.ViewFields = viewFields.ToString();
  5.         query.Query = sb.ToString();

 

Getting to this point did take some changing of the various Caml query properties and it was slightly frustrating but I don’t think this is particularly related to joins within Caml but more of a general issue with Caml.

Issues

There was one issue which seems particularly related to joins and this was when trying to get a DataTable of the results instead of a SPListItemCollection, see example below. This throws a NullReferenceException, see stack trace below, and it seems the only way around this is to work with the SPListItemCollection and not a DataTable

Get DataTable
  1. var queryResults = list.GetItems(query).GetDataTable();  

 

Stack Trace

The error was System.NullReferenceException: Object reference not set to an instance of an object.     at Microsoft.SharePoint.SPFieldMap.EnsureFieldArray()     at Microsoft.SharePoint.SPFieldMap.GetFieldObject(Int32 columnNumber)     at Microsoft.SharePoint.SPListItemCollection.GetVisibleFieldIndices(Boolean isJsGrid, Int32[]& arrVisibleFieldIndices, Int32& iVisibleFieldCount)     at Microsoft.SharePoint.SPListItemCollection.GetDataTableCore(Boolean isJsGrid)

Conclusion

I think that the join functionality in SharePoint 2010 is a useful feature, however given the fiddly nature of this along with how easy it is to accomplish the same thing using Linq to SharePoint will mean a lot of people won’t use this approach. In my circumstances it works very well and with a few extra lines of code, compared to a normal Caml Query, gives me a lot of extra functionality.

Advertisements

9 Responses to CAML Query Join Lists

  1. Excellent Post! I am trying to do this using XmLDocument via the Lists.asmx Web Service but cant seem to get the Join to work properly. Do I put the Join in the Query or Query Options?

  2. Swapnill says:

    Fantastic Post… by the way can you tell me , If i use the same caml query in calendar view… will it work? …coz I ve done some caml query having multiple joins and where clause involves condition on projected field. and it works in simple list view. but it is not working in calendar list view. it says…One or more field types are not installed properly. Go to the list settings page to delete these fields. …but in simple list view it is working perfect. please help me out

    • raymondlittle1000 says:

      What do you mean on calendar view? are you running the query against a calendar list? The code should work on any lists which are joined by a lookup.

      The obvious one to check with the error you’re getting is the fields are referenced by the internal name and not the display name.

      Alternatively you could be referencing fields in the where clause which are named in the project fields and view fields.

      Hope this helps let me know if you are still having issues

      • Swapnill says:

        basically there are multiple types of views on list in sf 2010 r8. so calendar view is one of them. and the query which i am using onto calendar view runs perfectly in case of simple list view.

      • raymondlittle1000 says:

        Hi,

        Sorry for the delay in getting back to you.

        The type of view shouldn’t make a difference to the CAML query. When you are executing your query are you specifically targeting a view or just running it against the list as a whole?

        If you can it would be good to see an example of the code.

        Cheers,

        Ray

  3. Derrick says:

    Asking questions are genuinely good thing if you
    are not understanding something completely, however this post provides
    nice understanding even.
    Derrick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: