List-Joins und Projected Fields

In einem älteren Beitrag habe ich gezeigt, was mit CAML-Abfragen grundsätzlich möglich ist. Als Ergänzung dazu soll dieser Beitrag zeigen, wie man mit solchen Abfragen zwei oder mehr Listen verbinden kann, analog zum SQL JOIN-Statement. Man erhält dadurch mit einer einzigen Abfrage eine Ergebnisliste, die Daten aus mehreren Listen enthält. Der Mechanismus funktioniert allerdings nur, wenn die Listen durch Nachschlagefelder miteinander verbunden sind.

Motivation

Bei einem Nachschlagefeld kann man zwar weitere Felder der Nachschlageliste auswählen, die dann ebenfalls in Ansichten der Liste dargestellt werden, aber das funktioniert nur mit einer Ebene. Es funktioniert nicht, wenn Daten aus mehr als zwei Listen benötigt werden.

Und es funktioniert nur mit Standard-Nachschlagespalten. Es gibt im Web mehrere Implementierungen eigener Feldtypen, die Nachschlagefelder erweitern, z.B. um ein gefiltertes oder kaskadierendes Nachschlagen zu ermöglichen oder um das Nachschlagefeld mit einem Picker-Dialog auszustatten. Die meisten dieser Implementierungen sind vom Standard-Nachschlagefeld abgeleitet und bieten von Haus aus nicht mehr die Möglichkeit weitere Felder darzustellen. Weil die Daten aber genau wie bei den Standardfeldern gespeichert werden, können diese Felder für CAML List-Joins herangezogen werden.

Umgebung

Für die Demonstration habe ich folgende Umgebung eingerichtet: eine Liste mit Projekten und den Feldern Projektname, Projektnummer und geplantes Projektende. Und eine weitere Liste Projektzeiten, in der die Benutzer ihre Arbeitszeiten eintragen können, die sie jeweils für ein Projekt aufgewendet haben. Diese Liste hat die Felder Datum, Stunden, ein Bemerkungsfeld und natürlich ein Nachschlagefeld auf die Projekte.

Das ist die Projektliste:

Und das die Liste mit den Projektzeiten:

Für eine Übersicht sollen jetzt alle erfaßten Zeiten mit zusätzlichen Informationen aus der Projektliste dargestellt werden.

So geht’s

Man legt für die Liste Projektzeiten eine neue Ansicht an und öffnet die Website in SharePoint Designer. Wenn man über Listen und Bibliotheken auf die Liste navigiert, findet man rechts die Ansichten dieser Liste und kann diese neue Ansicht öffnen. Man markiert in der Entwurfsansicht die Tabelle und schaltet in die Codeansicht um. Jetzt sieht man die CAML-Abfrage, die dieser Ansicht zugrunde liegt. Sie sieht leicht gekürzt ungefähr so aus:

<View>
  <Query>
    <OrderBy>
      <FieldRef Name=“Projekt“/>
    </OrderBy>
  </Query>
  <ViewFields>
    <FieldRef Name=“Projekt“/>
    <FieldRef Name=“Datum“/>
    <FieldRef Name=“Stunden“/>
    <FieldRef Name=“Author“/>
  </ViewFields>
  <RowLimit Paged=“TRUE“>100</RowLimit>
  <Aggregations Value=“Off“/>
  <Toolbar Type=“Standard“/>
</View>

Diese Abfrage wird jetzt editiert. Zunächst verbindet man die Listen durch ein Join-Element, das direkt als Kind des View-Elements notiert wird:

<Joins>
<Join Type=“INNER“ ListAlias=“Projekte“>
    <Eq>
      <FieldRef Name=“Projekt“ RefType=“Id“/>
      <FieldRef List=“Projekte“ Name=“ID“/>
    </Eq>
  </Join>
</Joins>

Als Join-Type kann dabei INNER oder LEFT angegeben werden. Das Ganze funktioniert analog zum bekannten JOIN-Statement in SQL.

Als ListAlias kann ein beliebiger Name gewählt werden. Dieser Name wird später bei den ProjectedFields verwendet (s.u.). Der ListAlias ist außerdem zur Unterscheidung wichtig, falls eine Liste mehrere Nachschlagefelder auf dieselbe Liste hat. Man denke z.B. an eine Liste mit Adressen, aus der sowohl eine Rechnungs- als auch eine Lieferadresse ausgewählt werden soll. Die Liste selbst muß nicht explizit angegeben werden. Sie wird über die Definition der verknüpfenden Felder identifiziert (s.u.).

Als Vergleichsoperator muß immer Eq angegeben werden.

Der Vergleichsoperator Eq enthält immer zwei FieldRef-Elemente, die die Verknüpfung definieren. Das erste stellt dabei immer das Nachschlagefeld der primären Liste dar, das (wie immer in CAML) durch seinen internen Namen referenziert wird. Als RefType wird immer Id angegeben. Falls die primäre Liste der Verknüpfung nicht die eigentliche Liste ist, für die diese Ansicht erstellt wird, z.B. weil es eine weitere Verbindung der Projekte zu einer Kundenliste gibt, muß in einem zusätzlichen List-Attribut auch die Liste angegeben werden. Das zweite FieldRef-Element gibt die Liste an, auf die nachgeschlagen wird. Als Name wird immer ID angegeben.

Damit ist die Verbindung der Listen grundsätzlich definiert. Als nächstes müssen alle Felder der verknüpften Listen deklariert werden, die in irgendeiner Form in der Abfrage verwendet werden sollen, egal ob zur Anzeige, Sortierung oder als Filter. Das geschieht in einem ProjectedFields-Element:

<ProjectedFields>
<Field Name=“Projektname“ Type=“Lookup“ List=“Projekte“ ShowField=“Title“/>
  <Field Name=“Projektnummer“ Type=“Lookup“ List=“Projekte“ ShowField=“Projektnr“/>
  <Field Name=“Projektende“ Type=“Lookup“ List=“Projekte“ ShowField=“GeplEnde“/>
</ProjectedFields>

Jedes Field-Element gibt im Name-Attribut einen Namen an, unter dem das Feld an anderen Stellen der Abfrage identifiziert wird. Der Name wird außerdem bei angezeigten Feldern als Spaltenüberschrift verwendet. Leider muß dieser Name XML-konform sein, so daß das einzige verwendbare Sonderzeichen der Unterstrich ist. Alle anderen nicht-alphanumerischen Zeichen wie Leerzeichen, Punkt oder Komma sind nicht erlaubt.

Das Type-Attribut wird immer mit Lookup belegt.

Das List-Attribut enthält den ListAlias, der oben beim Join (s.o.) angegeben wurde.

Das ShowField-Attribut enthält wiederum den internen Namen des Feldes aus der Nachschlageliste.

Damit stehen die Felder aus der Nachschlageliste in der Abfrage zur Verfügung und können z.B. angezeigt werden. Man kann sie auch problemlos zum Sortieren oder als Filter in der Where-Bedingung verwenden. Sie werden dabei wie gewohnt als FieldRef-Elemente notiert. Als Name wird dabei der oben bei den ProjectedFields angegebene Name verwendet.

Zur Anzeige der Felder werden diese bei den ViewFields notiert:

<ViewFields>
  <FieldRef Name=“Projekt“/>
  <FieldRef Name=“Projektnummer“/>
  <FieldRef Name=“Projektende“/>
  <FieldRef Name=“Datum“/>
  <FieldRef Name=“Stunden“/>
  <FieldRef Name=“Author“/>
</ViewFields>

Die Ansicht der Projektzeiten sieht dann so aus:

Erweiterungen

Wie oben bereits angedeutet, ist dieser Mechanismus nicht auf zwei Listen beschränkt. Es lassen sich damit Daten aus vielen Listen in einem Rutsch abfragen, solange die Listen über Nachschlagefelder verbunden sind.

In unserem Beispiel wäre es denkbar, daß die Projektliste ein Nachschlagefeld auf eine weitere Liste Kunden enthält, mit dem jedes Projekt einem Kunden zugeordnet wird. Man kann dann in einer Ansicht sowohl die erfaßten Projektzeiten als auch die Daten des Projekts und des zugehörigen Kunden (z.B. seine Adresse) anzeigen.

CAML-Abfragen bei wiederkehrenden Kalendereinträgen

Dieser Beitrag zeigt, wie man wiederkehrende Ereignisse in SharePoint-Kalendern über eine CAML-Abfrage per Code auslesen kann. Der Beitrag ist eine Ergänzung zu einem allgemeinen Beitrag über CAML, den ich hier geschrieben habe.

Wiederkehrende Ereignisse werden in einer Kalenderliste durch ein einziges Element repräsentiert. Man bekommt sie also nicht über eine Anfrage mit einer normalen Where-Klausel. Wenn man über eine Anfrage alle Ereignisse zu einem bestimmten Zeitpunkt oder zu einem bestimmten Zeitraum haben möchte, muß man eine bestimmte Syntax in CAML notieren. Das sieht dann so aus:

<Where>
  <DateRangesOverlap>
    <FieldRef Name=’EventDate’/>
    <FieldRef Name=’EndDate’/>
    <FieldRef Name=’RecurrenceID’/>
    <Value Type=’DateTime‘><Today/></Value>
  </DateRangesOverlap>
</Where>

Damit diese Anfrage funktioniert, muß die ExpandRecurrence-Eigenschaft des SPQuery-Objekts auf true festgelegt werden. Wenn man sonst nichts weiter angibt, bekommt man durch diese Abfrage alle Ereignisse für heute. Falls ein anderes Datum gewünscht ist, muß außerdem die CalendarDate-Eigenschaft des SPQuery-Objekts auf das gewünschte Datum festgelegt werden. Der gesamte Code sieht dann so aus:

SPQuery query = new SPQuery();
query.ExpandRecurrence = true;
query.CalendarDate = new DateTime(2012, 12, 4);
query.Query = @"<Where>
                  <DateRangesOverlap>
                    <FieldRef Name=’EventDate’/>
                    <FieldRef Name=’EndDate’/>
                    <FieldRef Name=’RecurrenceID’/>
                    <Value Type=’DateTime‘><Today/></Value>
                  </DateRangesOverlap>
                </Where>";

Falls man nicht nur die Ereignisse für einen einzelnen Tag möchte, kann man bei <Value> auch <Month/> oder <Year/> verwenden. Alle anderen Angaben bei <Value> funktionieren nicht. Insbesondere das abzufragende Datum muß bei SPQuery.CalendarDate angegeben werden!

Achtung:

Die durch eine solche Abfrage erhaltenen Elemente müssen nochmal geprüft werden, ob sie auch wirklich zum gewünschten Datum oder Zeitraum gehören. Man bekommt meist auch angrenzende Ereignisse geliefert. Bei einer Abfrage für einen Monat bekommt man z.B. auch die Ereignisse des Vormonats und des Folgemonats, die in einer Monatsansicht des Kalenders angezeigt werden würden. Bug oder Feature?

CAML-Abfragen in SharePoint

In diesem Beitrag soll gezeigt werden, wie CAML-Abfragen in SharePoint gemacht werden können. Er gilt gleichermaßen für SharePoint 2007 und SharePoint 2010. Die offizielle Referenz zum verwendeten XML-Schema findet sich hier auf MSDN (für SharePoint 2010).

Die Motivation zu diesem Beitrag entstand dadurch, daß ich selbst etwas zum Nachschlagen brauchte. Die Methoden zum Abfragen unterschiedlicher Feldtypen und die verwendbaren Token und Vergleichsoperatoren habe ich auch nicht immer auswendig parat. Ich werde die Sammlung im Laufe der Zeit ergänzen, wenn mir selbst etwas Neues unterkommt oder wenn ich auf Fehlendes hingewiesen werde.

Als Entwickler kommt man mit CAML wahrscheinlich am Häufigsten beim Abfragen von Listenelementen per Code in Kontakt. Man gibt für die Abfrage ein oder mehrere Kriterien an, nach denen die Ergebnismenge gefiltert wird, und optional ein oder mehrere Felder, nach denen die Ergebnisse sortiert werden sollen.

Eine einfache Abfrage kann z.B. so aussehen:

SPQuery query = new SPQuery();
query.Query = @“<Where>
                  <Eq>
                    <FieldRef Name=’Title’/>
                    <Value Type=’Text‘>Hallo</Value>
                  </Eq>
                </Where>
                <OrderBy>
                  <FieldRef Name=’ID’/>
                </OrderBy>“;
SPListItemCollection items = myList.GetItems(query);

Diese Abfrage liefert alle Listenelemente, deren Titel Hallo entspricht, aufsteigend sortiert nach ID. Beim Zusammenstellen der Query kann man auch auf System.Xml.Linq zurückgreifen. Dieselbe Abfrage sieht dann so aus:

query.Query = new XElement(„Where“,
                new XElement(„Eq“,
                  new XElement(„FieldRef“, new XAttribute(„Name“, „Title“)),
                  new XElement(„Value“, new XAttribute(„Type“, „Text“), „Hallo“)
                )
              ).ToString(SaveOptions.DisableFormatting) +
              new XElement(„OrderBy“,
                new XElement(„FieldRef“, new XAttribute(„Name“, „ID“))
              ).ToString(SaveOptions.DisableFormatting);

FieldRef

Beim Name-Attribut der FieldRef-Elemente muß immer der interne Name der Felder angegeben werden. Der sichtbare Name kann nicht verwendet werden. Anstelle von Name kann auch ein ID-Attribut angegeben werden, das dann die GUID des Feldes enthalten muß. Eine Liste mit den internen Namen und IDs der Standardfelder findet man z.B. hier.

Sortierung

Wenn nichts anderes angegeben wird, wird immer in aufsteigender Reihenfolge sortiert. Wenn in absteigender Reihenfolge sortiert werden soll, wird ein zusätzliches Ascending-Attribut mit dem Wert FALSE angegeben:

<FieldRef Name=’ID‘ Ascending=’FALSE’/>

Natürlich kann auch nach mehreren Feldern sortiert werden, indem man mehrere FieldRef-Elemente angibt. Sie werden in der angegebenen Reihenfolge und in der jeweils angegebenen Sortierrichtung berücksichtigt.

Mehrere Werte abfragen

Wenn man mehrere Werte abfragen möchte, muß man die Einzelabfragen mit And– oder Or-Elementen klammern:

<Where>
  <Or>
    <Eq>
      <FieldRef Name=‘Title‘/>
      <Value Type=‘Text‘>Hallo</Value>
    </Eq>
    <Eq>
      <FieldRef Name=‘Title‘/>
      <Value Type=‘Text‘>Welt</Value>
    </Eq>
  </Or>
</Where>

Diese Abfrage liefert alle Elemente, deren Titel entweder Hallo oder Welt entspricht.

Es können immer nur zwei Einzelabfragen geklammert werden. Wenn man mehr Abfragen braucht, muß man eine neue Klammer verwenden:

<Where>
  <And>
    <Or>
      <Eq>
        <FieldRef Name=‘Title‘/>
        <Value Type=‘Text‘>Hallo</Value>
      </Eq>
      <Eq>
        <FieldRef Name=‘Title‘/>
        <Value Type=‘Text‘>Welt</Value>
      </Eq>
    </Or>
    <Gt>
      <FieldRef Name=‘ID‘/>
      <Value Type=‘Number‘>15</Value>
    </Gt>
  </And>
</Where>

Diese Abfrage liefert alle Elemente, deren Titel entweder Hallo oder Welt entspricht und deren ID größer als 15 ist.

Vergleichsoperatoren

Folgende Vergleichsoperatoren können verwendet werden:

Eq ist gleich (equals)
Neq ist nicht gleich (not equals)
Gt größer als (greater than)
Geq größer als oder gleich (greater or equals)
Lt kleiner als (less than)
Leq kleiner als oder gleich (less or equals)
IsNull ist null, d.h. leer
IsNotNull ist nicht null, d.h. nicht leer
BeginsWith beginnt mit (Zeichenfolge)
Contains enthält (Zeichenfolge)
Nachschlagefelder

Bei Nachschlagefeldern muß man aufpassen. Wenn nichts weiter beachtet wird, muß als Value der Texteintrag des Nachschlagefeldes angegeben werden. Wenn man stattdessen die nachgeschlagene ID abfragen möchte, muß es so aussehen:

<FieldRef Name=“Lookup“ LookupId=“True“/>
<Value Type=“Lookup“>15</Value>

Personenfelder

Personenfelder sind intern wie Nachschlagefelder aufgebaut (weil damit in der Benutzerliste nachgeschlagen wird) und verhalten sich deshalb ähnlich. Wenn man nichts weiter beachtet, muß man als Value den Anmeldenamen verwenden. Wenn man nach der ID eines bestimmten Benutzers filtern möchte, muß man bei Value Type=“Integer“ angeben.

Filtern nach dem aktuellen Benutzer, d.h. nach dem Benutzer, der die Abfrage ausführt, kann man so:

<Value Type=“Integer“><UserID/></Value>

Gruppenmitgliedschaft auflösen

Bei Elementen mit einem Personenfeld gibt es eine Möglichkeit alle Elemente zu erhalten, bei denen in diesem Feld Personen aus Gruppen stehen, bei denen auch der aktuelle Benutzer Mitglied ist. Das funktioniert so z.B. bei der vordefinierten Ansicht Nach meinen Gruppen bei Aufgabenlisten. Die Abfrage dazu sieht so aus:

<Where>
  <Membership Type=“CurrentUserGroups“>
<FieldRef Name=“AssignedTo“/>
</Membership>
</Where>

Ja/Nein Felder

Bei Ja/Nein-Feldern muß als Value 0 oder 1 angegeben werden. True oder false funktionieren nicht!

<Value Type=“Boolean“>0</Value>

Datumsfelder

Auch Datumsfelder müssen besonders beachtet werden. Ohne weiteres Zutun wird bei Datumsvergleichen nur der Tagesanteil beachtet und der Zeitanteil wird ignoriert. Wenn der Zeitanteil ebenfalls beachtet werden soll, muß dem Value-Element ein IncludeTimeValue-Attribut mit dem Wert True hinzugefügt werden. Das Datum selbst muß in folgendem Format angegeben werden:

<Value Type=“DateTime“ IncludeTimeValue=“True“>yyyy-MM-ddThh:mm:ssZ</Value>

Wie man wiederkehrende Ereignisse einer Kalenderliste abfragen kann, habe ich in einem separaten Beitrag hier beschrieben.

Token

Als Value können folgende Token verwendet werden:

<Today/> liefert das aktuelle Datum ohne Zeitanteil. Es kann ein Attribut Offset oder OffsetDays angegeben werden, das einen Offset in ganzen Tagen angibt (kann auch negativ sein):

<Today OffsetDays=“2″/>

<Now/> liefert das aktuelle Datum inklusive Zeitanteil.

<UserID/> liefert die ID des aktuellen Benutzers.

List-Joins und ProjectedFields

Wie man mehrere Listen in Abfragen verbinden kann, habe ich in einem separaten Beitrag gezeigt.

Was es sonst noch zu beachten gibt

Abfragen mit SPQuery liefern standardmäßig immer alle Felder der Listenelemente, auch alle versteckten. Wenn man die nicht alle braucht, kann man die gewünschten Felder angeben, was sich besonders bei Listen mit sehr vielen Feldern empfiehlt. Dazu belegt man die Eigenschaft ViewFields des SPQuery-Objekts mit ein oder mehreren FieldRef-Elementen. Die Abfrage liefert dann nur noch die angegebenen Felder.

Standardmäßig liefert SPQuery nur Elemente, die sich im Root-Ordner einer Liste befinden. Wenn man auch Elemente aus Unterordnern haben möchte, belegt man die Eigenschaft ViewAttributes mit Scope=“Recursive“. Wenn man auch die Ordner selbst haben möchte, kann man Scope=“RecursiveAll“ angeben.