List Joins and Projections
Applies to: SharePoint Foundation 2010
This topic describes the use of list joins and field projections in Collaborative Application Markup Language (CAML) defined views and queries.
Joins and Projections in List Views
A list view can include fields from other lists that have been joined to the primary list. The CAML View element implements this functionality by means of its child elements Joins and ProjectedFields, which are represented in the object model by the Joins and ProjectedFields properties of the SPView object. (The SPQuery object has properties with the same names. See Joins and Projections in Queries for more information.)
List Joins in Views
The Joins element contains one or more Join elements. Each of these creates an inner join or left outer join between two lists. At least one of these joins must be from the parent list of the view, called the primary list, and some other list, called the foreign list. But there can be additional joins from that foreign list to another foreign list, and so on. There is no limit to how many links there can be in a chain of joins, but the total number of Join elements, whether in chains or not, cannot exceed the value of the MaxQueryLookupFields property for the SPWebApplication object that contains the primary list. The system default value is eight. A list can be joined to itself directly or as a chain of joins.
Important
There are requirements to keep in mind when creating list joins. You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the "join on" pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists.
The following example markup envisions a SharePoint Foundation Web site that hosts a club of parents who sell one another used children’s clothing. There is a need for a view of the Orders list that shows the buying member’s (customer’s) city and state and the selling member’s (shipper’s) city. To implement this, there are two chains of left outer joins:
Orders to Members to Cities to States
Orders to Members to Cities
Note the following about the Joins markup:
The Type attribute of each Join element can be "LEFT" or "INNER".
Because there are two joins from Orders to Members, they must be distinguished. This is facilitated by the ListAlias attribute, which assigns the Members list the alias "customer’ in the first join, but assigns it the alias "shipper" in the second join.
There are also two joins from Members to Cities and they are disambiguated in the same way.
There is no place where any list alias is explicitly mapped to a list. A mapping is not necessary because each join parallels an existing lookup field relation and the definition of the lookup field identifies the foreign list.
The "join on" fields are identified by a pair of FieldRef elements. The first represents the Lookup field in the primary list and identifies it by internal name. It must have a RefType attribute set to "Id". If the primary list of the join is not the parent list of the view, then it, too, is identified with a List attribute set to its alias. The second FieldRef element of each pair identifies the foreign list, by alias again, and the foreign key field, which must always be the Id field.
<Joins>
<Join Type='LEFT' ListAlias='customer'>
<Eq>
<FieldRef Name='CustomerName' RefType='Id'/>
<FieldRef List='customer' Name='ID'/>
</Eq>
</Join>
<Join Type='LEFT' ListAlias='customer_city'>
<Eq>
<FieldRef List='customer' Name='CityName' RefType='Id'/>
<FieldRef List='customer_city' Name='Id'/>
</Eq>
</Join>
<Join Type='LEFT' ListAlias='customer_city_state'>
<Eq>
<FieldRef List='customer_city' Name='StateName' RefType='Id'/>
<FieldRef List='customer_city_state' Name='Id'/>
</Eq>
</Join>
<Join Type='LEFT' ListAlias='shipper'>
<Eq>
<FieldRef Name='ShipperName' RefType='Id'/>
<FieldRef List='shipper' Name='ID'/>
</Eq>
</Join>
<Join Type='LEFT' ListAlias='shipper_city'>
<Eq>
<FieldRef List='shipper' Name='CityName' RefType='Id'/>
<FieldRef List='shipper_city' Name='Id'/>
</Eq>
</Join>
</Joins>
Projected Fields in Views
The ProjectedFields element creates fields from the foreign lists so that they can be used in the list view. The fields must also then be identified in the ViewFields child element of View element.
To continue the example of the parent’s club, the following ProjectedFields creates fields for the customer’s city, customer’s state, and shipper’s city. Note the following about this markup:
The foreign lists are identified by their aliases, as defined in the Joins element.
The ShowField attribute identifies which field from the foreign list is used in the view.
The Type attribute always has the value "Lookup". For this reason, the Type attribute does not indicate the data type of the field as it usually does in a Field element. When a Field element is a child of a ProjectedFields, Type simply indicates whether the Join (in the Joins element on which the ProjectedFields element depends) is based on an existing lookup relation between the lists. All joins must be based on an existing lookup relation. See below for a list of the CAML field types that can be projected fields.
<ProjectedFields>
<Field
Name='CustomerCity'
Type='Lookup'
List='customer_city'
ShowField='Title'/>
<Field
Name='CustomerCityState'
Type='Lookup'
List='customer_city_state'
ShowField='Title'/>
<Field
Name='ShipperCity'
Type='Lookup'
List='shipper_city'
ShowField='Title'/>
</ProjectedFields>
Only the following types of fields can be included in a ProjectedFields element:
Calculated (treated as plain text)
ContentTypeId
Counter
Currency
DateTime
Guid
Integer
Note (one-line only)
Number
Text
As noted above, it is also necessary that the fields created in the ProjectedFields element be specified in the ViewFields element. The following markup continues the example.
<ViewFields>
<FieldRef Name='CustomerCity'/>
<FieldRef Name='CustomerCityState'/>
<FieldRef Name='ShipperCity'/>
</ViewFields>
Joins and Projections in Queries
Joins and projected fields can also be used in CAML queries. In this use, also, the joins and projected fields are defined with Joins and ProjectedFields elements. However, the elements are not children of the Query element. They are independent XML markups that form the value of the SPQuery.Joins and SPQuery.ProjectedFields properties of the SPQuery object that represents the query.
It is usually best to use the LINQ to SharePoint Provider to query SharePoint Foundation lists with server code. Because CAML has the Joins and ProjectedFields elements, the LINQ to SharePoint Provider, which translates LINQ queries into CAML queries, can fully support the LINQ join (Join in Visual Basic) and select (Select in Visual Basic) operators. If your code is intended to run on clients, we recommend that you query by using SharePoint to ADO.NET Data Service.
If you want to create CAML queries directly and set the relevant properties of an SPQuery object explicitly, you should consider using a tool to generate the queries. To locate such tools, navigate to www.bing.com and search for "CAML query tool" without the quotation marks. It may be some time after the release of Microsoft SharePoint Foundation 2010 before there are any tools available that support the generation of Joins and ProjectedFields elements.
The following example shows a query that returns all orders from an Orders list where the customer’s city is London. The example assumes that the Orders list has a CustomerName field that looks up to a Customers list and that the latter list has a CityName field that looks up to a Cities list.
The query requires a join of Orders to Customers and from Customers to Cities, so the value of the Joins property would be the following.
<Joins>
<Join Type=’LEFT’ ListAlias=’customers’>
<Eq>
<FieldRef Name=’CustomerName’ RefType=’Id’ />
<FieldRef List=’customers’ Name=’ID’ />
</Eq>
</Join>
<Join Type=’LEFT’ ListAlias=’customerCities’>
<Eq>
<FieldRef List=’customers’ Name=’CityName’ RefType=’Id’ />
<FieldRef List=’customerCities’ Name=’ID’ />
</Eq>
</Join>
</Joins>
Because the Where section of our query is going to test the customer’s city, we need to create a CustomerCity field. Hence, the value of the ProjectedFields is the following.
<ProjectedFields>
<Field
Name=’CustomerCity’
Type=’Lookup’
List=’customerCities’
ShowField=’Title’ />
</ProjectedFields>
Next, we need to make the field available by adding a reference to it to the ViewFields element. Therefore, the value of the ViewFields property is the following.
<ViewFields>
<FieldRef Name='CustomerCity'/>
</ViewFields>
Finally, the Query property is set to the following.
<Query>
<Where>
<Eq>
<FieldRef Name='CustomerCity'/>
<Value Type='Text'>London</Value>
</Eq>
</Where>
</Query>
Implicit Joins without a Joins Element
We recommend that you use an explicit Joins element when your CAML query involves a join of lists for purposes of maximizing the readability of your markup. Doing so will also maximize the chance that your query markup will be compatible with future versions of SharePoint Foundation. There is, however, a way to support an implicit join of two lists without using a Joins element. You can simply create a ProjectedFields element as described above, except that the child Field element has a FieldRef attribute in place of the List attribute. The FieldRef simply identifies the Lookup column in the source list. Also, when the ProjectedFields element has a FieldRef attribute instead of a List attribute, its Name attribute should be given some arbitrary value that is not the same as any column in the source list. (Again, in this markup, there is no need to identify the target list because it is specified in the configuration of the Lookup relation.)
For example, suppose, given the same lists and lookup relations as in the preceding section, you have the following query and ViewFields element.
<Query>
<Where>
<Eq>
<FieldRef Name='CustomerName'/>
<Value Type='Text'>Hicks, Cassie</Value>
</Eq>
</Where>
</Query>
<ViewFields>
<FieldRef Name='CustomerName'/>
</ViewFields>
Notice that the Where element makes an implicit join between the Orders and Customers lists. You could support this query with only the following ProjectedFields element. There would be no need for a Joins element. (Note that the Name attribute has been given an arbitrary name that is different from the actual lookup column name that is specified with the FieldRef attribute.)
<ProjectedFields>
<Field
Name=’OrderingCustomer’
Type=’Lookup’
FieldRef=’CustomerName’
ShowField=’Title’ />
</ProjectedFields>
Even with this technique it is still a requirement that there be a lookup relation between the source column and the target list. Also, with this technique, you may not chain joins. You could not, for example, support the Query element shown at the end of the preceding section. That query makes an implicit double join from Orders to Customers to Cities. A chain of joins requires an explicit Joins element.