REST and SharePoint 2010 Quick Start Guide: Multiple Columns, $expand, and $filter (almost a JOIN)

 

Table of Contents for this Series

 

This post addresses the issue of forming REST queries that work with ‘joined’ lists. Take note of the fact that these words are carefully chosen. As of this writing we are really just talking about working with lookup fields with multiple columns. That is to say, the lists are ‘linked/joined’ based on a ‘traditional’ lookup field but can also include additional columns from the lookup list, this last piece of information is important. The parent list does not physically include the child fields – but – using $expand in the URL allows these fields to be referenced from the child list and filtered as you want.

Using these multiple fields with $filter and $expand really starts to make me feel like I’m using a true ‘join’, its just that in this case the join is configured on the lists prior to using the REST query and filter syntax. Using REST to access lists using this technique is still limited (as compared to LINQ or CAML queries) yet still powerful. 

Here is a basic example,

I created two custom lists, one called Building and the other called Rooms. This will obviously form a parent/child relationship – here is how they look……   

Building,

image    

Rooms,

image

The building list uses a lookup field with child fields as shown here (see RED)

Building image

In the first image you see Rooms/Title is used as a lookup source with Capacity and Equipment as the additional child fields. The screen shot of the lookup column is in the second image

You can look back at [Part 2] to see how to bring back and $filter lists via REST. Lets bring back the Building List – we use the following query

http://win-dcbei0i59th/_vti_bin/ListData.svc/Building(1) to bring back the first Building on the list, the results are as follows

image

Notice that both Capacity and Equipment fields are missing – This is because that are not ‘physically’ tied to the list

Lets now $expand this REST call to include both Building and Rooms lists in the result – We use the following call

http://win-dcbei0i59th/_vti_bin/ListData.svc/Building(1)?$expand=Rooms

….with the results shown here (results have been visibly cropped). Essentially, this brings back two sets of content, one is the original Building list the other is the Rooms list with the combined fields.

   

image

Finally, we can now combine the $filter syntax to pull back specific rows based on reasonably complex criteria, for example, based on room Capacity. I issue the the following REST call using both $expand and $filter

 

http://win-dcbei0i59th/_vti_bin/ListData.svc/Building?$expand=Rooms&$filter=Rooms/Capacity eq 35  <== This is the punch line for this post. Future posts will contain additional samples

This REST call then pulls back the following ‘record’ (this result XML is complete from the browser) – note the bolded parameter

 

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
– <feed xml:base="http://win-dcbei0i59th/_vti_bin/ListData.svc/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
  <title type="text">Building</title>
  <id>http://win-dcbei0i59th/_vti_bin/ListData.svc/Building</id>
  <updated>2010-01-02T05:49:27Z</updated>
  <link rel="self" title="Building" href="Building" />
– <entry m:etag="W/"2"">
  <id>http://win-dcbei0i59th/_vti_bin/ListData.svc/Building(2)</id>
  <title type="text">Building 1</title>
  <updated>2010-01-01T19:34:38-08:00</updated>
– <author>
  <name />
  </author>
  <link rel="edit" title="BuildingItem" href="Building(2)" />
– <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Rooms" type="application/atom+xml;type=entry" title="Rooms" href="Building(2)/Rooms">
– <m:inline>
– <entry m:etag="W/"2"">
  <id>http://win-dcbei0i59th/_vti_bin/ListData.svc/Rooms(1)</id>
  <title type="text">Orange</title>
  <updated>2010-01-01T19:48:16-08:00</updated>
– <author>
  <name />
  </author>
  <link rel="edit" title="RoomsItem" href="Rooms(1)" />
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Attachments" type="application/atom+xml;type=feed" title="Attachments" href="Rooms(1)/Attachments" />
  <category term="Microsoft.SharePoint.DataService.RoomsItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
– <content type="application/xml">
– <m:properties>
  <d:ContentTypeID>0x01004A2B927D6EE32842A76976EBF5F2273F</d:ContentTypeID>
  <d:Title>Orange</d:Title>
  <d:Capacity m:type="Edm.Double">35</d:Capacity>
  <d:Equipment>Video Conf, White Board</d:Equipment>
  <d:ID m:type="Edm.Int32">1</d:ID>
  <d:ContentType>Item</d:ContentType>
  <d:Modified m:type="Edm.DateTime">2010-01-01T19:48:16</d:Modified>
  <d:Created m:type="Edm.DateTime">2010-01-01T18:58:40</d:Created>
  <d:CreatedByID m:type="Edm.Int32">1</d:CreatedByID>
  <d:ModifiedByID m:type="Edm.Int32">1</d:ModifiedByID>
  <d:Owshiddenversion m:type="Edm.Int32">2</d:Owshiddenversion>
  <d:Version>1.0</d:Version>
  <d:Path>/Lists/Rooms</d:Path>
  </m:properties>
  </content>
  </entry>
  </m:inline>
  </link>
  <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Attachments" type="application/atom+xml;type=feed" title="Attachments" href="Building(2)/Attachments" />
  <category term="Microsoft.SharePoint.DataService.BuildingItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
– <content type="application/xml">
– <m:properties>
  <d:ContentTypeID>0x0100814BADFB50A8E54A8693CBF027872749</d:ContentTypeID>
  <d:Title>Building 1</d:Title>
  <d:RoomsID m:type="Edm.Int32">1</d:RoomsID>
  <d:ID m:type="Edm.Int32">2</d:ID>
  <d:ContentType>Item</d:ContentType>
  <d:Modified m:type="Edm.DateTime">2010-01-01T19:34:38</d:Modified>
  <d:Created m:type="Edm.DateTime">2010-01-01T18:51:16</d:Created>
  <d:CreatedByID m:type="Edm.Int32">1</d:CreatedByID>
  <d:ModifiedByID m:type="Edm.Int32">1</d:ModifiedByID>
  <d:Owshiddenversion m:type="Edm.Int32">2</d:Owshiddenversion>
  <d:Version>1.0</d:Version>
  <d:Path>/Lists/Building</d:Path>
  </m:properties>
  </content>
  </entry>
  </feed>

If you want to display this in a browser client see [Part 2]

As you now see we’ve managed to (1) associate two lists, (2) select a group of fields to view, (3) create a REST call that brings back a something close to a ‘join’, almost, and then (4) allows us to filter on the results – NOT BAD!

That’s all for now

Cheers!

Advertisements

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