Joins and Projections

Joins between tables are well known in relational databases. The complexity and power of SharePoint lists overcome the need for frequent usage of joins. However, when working with views, you will find that it’s occasionally desirable to add the odd field from another list. Lookups are a good way to do this, but they require navigating from the parent list to the related list in order to view fields. If you want to create a view that spans several lists and show the results in just one list, you need to use joins. Direct joins are a new feature in SharePoint 2010. Projections define the fields that become part of the result set’s field list. This is comparable with the mapping of fields in the SELECT clause in SQL.

NOTE

Though this may look like a replacement for lookups, it does not eliminate the need for them. A join uses a field reference previously defined by a lookup. Thus, you have to create a lookup field first.
Regular lookups can be multilookups—meaning that you can form a many-to-many relationship. Using joins, this is not possible; you must use a single-value lookup.

Joins and projections have a direct representation in views and queries. This includes the corresponding properties—Joins and ProjectedField—of the SPQuery and SPView objects. While both objects use CAML, the XML snippets do not become part of a regular query, and they are not child elements of the element. Instead, the root elements are and , respectively. The XML is assigned to the properties of the SPQuery and SPView objects without their respective root elements

Joins

From SQL you may know that there are different kinds of joins. In CAML you can define two kinds of joins, INNER and LEFT, using the Type attribute:

<Joins>
<Join Type="INNER" ListAlias="Authors">
<Eq>
<FieldRef Name="FullName" RefType="Id" />
<FieldRef List="Authors" RefType="Id" />
</Eq>
</Join>
</Joins>

An inner join links two lists using an existing lookup. The result is a combination of both result sets. A left join (known in SQL as a LEFT OUTER JOIN) will return the same combination but includes the result of the parent list even if the related list does not have a matching entry. The common right join available in SQL is not supported by CAML.

Projected Fields in Views

The ProjectedFields element enables fields from the foreign lists to be viewed in the list view. The fields must also be identified in the ViewFields child element of the View element. The foreign lists are identified by their aliases, as defined in the Joins element. Again, if there is only one join, then there is no alias that is different from the list’s internal name. In this case, the reference in ViewFields uses the actual list name.

The ShowField attribute identifies which field from the foreign list is used in the view. This must be the internal name. The Type attribute always has the value Lookup—it does not indicate the data type of the field. The source type of a projected field is limited to a number of simple types, such as Counter, Currency, Integer, and Text.

Using Joins and Projected Fields

The following example shows how to use joins and projections. It is a console application that uses two lists: Books and Authors. The Books list relates to Authors through a field called LeadAuthor. The Authors list derives from Contacts and has an additional field called FullName.
The application (Listing 4-31) retrieves data from these two lists.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Xml;

namespace Apress.SP2010.CAMLJoins
{
class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://sharepointserve"))
{
using (SPWeb web = site.OpenWeb())
{
SPList bookList = web.Lists["Books"];
SPList authList = web.Lists["Authors"];
SPField la = bookList.Fields["LeadAuthor"];
SPField fa = authList.Fields["Full Name"];
string join = @"<join Type='LEFT' ListAlias='Authors'>
<eq>
<fieldref Name='" + la.InternalName
+ @"' RefType='ID' />
<fieldref List='Authors' Name='ID' />
</Eq>
</Join>";
string projField = @"<field Name='Fullname' Type='Lookup'
List='Authors'
ShowField='" + fa.InternalName
+ "' />";
SPQuery query = new SPQuery();
query.Query = "";
query.Joins = join;
query.ProjectedFields = projField;
query.ViewFields = @"<fieldref Name='Fullname' />
<fieldref Name='Title' />";
SPListItemCollection items = bookList.GetItems(query);
foreach (SPListItem item in items)
{
Console.WriteLine("{0} has these lead authors: ",
item.Title);
if (item["Fullname"] == null)
{
Console.WriteLine(" no authors assigned");
continue;
}
SPFieldLookupValue sc =
new SPFieldLookupValue(item["Fullname"].ToString());
Console.WriteLine(" -{0}", sc.LookupValue);
}
}
}
Console.ReadLine();
}
}
}

The join is from the parent list Books to the child list Authors. This is defined by List='Authors' in the join’s second FieldRef element. The ListAlias attribute must echo this, as it is the only join. The first FieldRef refers to the parent list’s lookup field, using the internal name. It’s read from the SPField object that has a property InternalName.

The variable pfld stores the projected fields. The declaration contains one field, FullName, from the Authors list. ShowField is also defined using the internal name. The name defined in the Name attribute is used in the ViewField definition. The Title field is also added to the result set. The XML snippets are assigned to the appropriate properties of SPQuery.

The GetItems method executes the query. Because item["Fullname"] returns a lookup, the value it returns is in the form of “#1;Joerg Krause.” To process it, we create an SPFieldLookupValue object, passing the data as a string into the constructor to re-create the lookup value. From this object the LookupValue returns the full name.