6.5 Komplexere Abfragen

In den bisherigen Beispielen sind lediglich sehr simple Einschränkungen in den gezeigten Abfragen vorgekommen, namentlich die Überprüfung auf die Gleichheit eines Werts einer einzelnen Spalte. Die ezcDbExpressions-Klasse ermöglicht daneben noch komplexere Einschränkungen wie multiple Verknüpfungen durch aussagenlogisches AND und OR. Auch andere SQL-spezifische Vergleichsoperatoren wie IN() sind möglich. Eine vollständige Liste findet sich in der API-Dokumentation zur Database-Komponente. [http://ezcomponents.org/docs/api/latest/Database/ezcQueryExpression.html ]

Komplexere Einschränkungen lassen den Aufbau der Abfragen über die Fluent-Interfaces immer mehr den weiter oben beschriebenen Fluent-Interfaces zum Rechnen mit komplexen Zahlen ähneln. Beispielhaft sei die Abfrage auf Kommentare zu einem bestimmten Blog-Eintrag von zwei Autoren nach einem bestimmten Zeitpunkt beschränkt. In reinem SQL sähe die Einschränkung in etwa folgendermaßen aus:

WHERE
    entry_id = 1
    AND (
        name = 'kore'
        OR name = 'toby'
    )
    AND date > 123456789

Listing 6.10 Einschränkung über WHERE

Mittels ezcDbExpression gestaltet sich der Code, um diese Einschränkung zu erzeugen, äußerst ähnlich:

$e = $query->expr;
$query->where(
    $e->eq(
        $db->quoteIdentifier( 'entry_id' ),
        $query->bindValue( 1 ) ),
    $e->lOr(
        $e->eq(
            $db->quoteIdentifier( 'name' ),
            $query->bindValue( 'kore' ) ),
        $e->eq(
            $db->quoteIdentifier( 'name' ),
            $query->bindValue( 'toby' ) )
    ),
    $e->gt(
        $db->quoteIdentifier( 'date' ),
        $query->bindValue( 123456789 ) )
);

Listing 6.11 Eingeschränkte Query mit dem Query-Builder

+------+--------------+----------------------------------------+
| name | email        | body                                   |
+------+--------------+----------------------------------------+
| kore | kore@php.net | Schoen, dass Blog nun gestartet ist.   |
+------+--------------+----------------------------------------+
| toby | toby@php.net | Freut mich auch.                       |
+------+--------------+----------------------------------------+

Listing 6.12 Ergebnis der eingeschränkten Query

Wie das Beispiel deutlich macht, werden mehrere Parameter bei dem Aufruf von where() automatisch im SQL-Statement mit dem logischen Operator AND verknüpft. Die Vergleiche auf Gleichheit sind äquivalent zum obigen Beispiel, nur der vierte Vergleich unterscheidet sich durch die Verwendung von gt() (englisch: greater then, größer als). Die Namen der beiden Kommentarautoren sind durch ein logisches Oder verknüpft, da aber die Namen OR und AND in PHP reservierte Begriffe sind und damit nicht für Methoden- oder Funktionsnamen verwendet werden können, heißen die entsprechenden Methoden lOr() und lAnd(), wobei das »l« für logical steht, die logische, nicht bitweise Verknüpfung charakterisierend.

6.5.1 Manuelle Queries

Das letzte Listing hat gezeigt, wie die Unabhängigkeit von den Eigenheiten der RDBMS auf Kosten der Komplexität des Programmcodes geht. Falls diese Abstraktion nicht benötigt wird oder plattformspezifische Optimierungen notwendig werden, können Sie über die Database-Komponente natürlich auch manuell aufgebaute Abfragen absetzen, die dennoch die Vorteile von Prepared-Statements nutzen können.

$db = ezcDbInstance::get();
$statement = $db->prepare( '
    SELECT name, email, body
    FROM comment
    WHERE
        entry_id = :id
        AND (
            name = :person_0 OR name = :person_1
        )
        AND date > :date' );
$statement->execute( array(
    ':id' => 1,
    ':person_0' => 'kore',
    ':person_1' => 'toby',
    ':date' => 123456789,
) );

Listing 6.13 Manuelle Queries ausführen

Der Methode prepare() wird in diesem Fall direkt die Abfrage in reinem SQL als String übergeben, der Platzhalter für die nachher einzufügenden Werte enthält. Diese Werte können der Abfrage nun, wie schon vorher durch den Aufruf von bindValue() auf $statement, assoziiert oder aber auch direkt im Aufruf von execute() als Array übergeben werden. Das Ergebnis dieser Abfrage gleich exakt der obigen Abfrage über den Query-Builder.

PDO ermöglicht zwei Varianten der Referenzierung von Daten in Prepared-Statements: Über benannte Schlüssel wie im letzten Beispiel oder mittels ? in der Query. Die zugewiesenen Werte werden den Platzhaltern dann in der Reihenfolge ihres Vorkommens zugewiesen.

Zu beachten ist bei diesem Beispiel natürlich, dass es nicht mehr unabhängig vom RDBMS funktioniert. In einigen Datenbanksystemen kann es nötig sein, Spaltennamen wie date zu maskieren, wobei sich die Art der Maskierung stark zwischen den verschiedenen Systemen unterscheidet.

6.5.2 Sub-Selects

Zu den komplexeren SQL-Features, die der Query-Builder unterstützt, gehören Sub-Selects, die seit Version 4.1 auch von MySQL unterstützt werden und dadurch mit jedem der getesteten RDBMS funktionieren.

$db = ezcDbInstance::get();
$query = $db->createSelectQuery();
$subquery = $query->Sub-Select();
$subquery
    ->selectDistinct( $db->quoteIdentifier( 'entry_id' ) )
    ->from( $db->quoteIdentifier( 'entry_tag' ) );

$query
    ->select( $db->quoteIdentifier( 'title' ) )
    ->from( $db->quoteIdentifier( 'entry' ) )
    ->where(
        $query->expr->in(
            $db->quoteIdentifier( 'id' ),
            (string) $subquery )
    );

$statement = $query->prepare();
$statement->execute();

Listing 6.14 Subselect mit dem Query-Builder

+---------------------+
| title               |
+---------------------+
| Erster Blogeintrag  |
+---------------------+
| Kapitel 6: Database |
+---------------------+

Listing 6.15 Ergebnis des Subselects mit dem Query-Builder

Diese Abfrage listet die Titel aller Blog-Einträge auf, die mindestens einen Tag assoziiert haben. Dazu wird wie gewohnt aus dem DB-Handler eine SELECT-Query erzeugt, auf deren Basis ein Subquery-Objekt erzeugt wird. Seit der Version 1.3 (die diesem Buch zugrunde liegt) kann die Database-Komponente mit DISTINCT-Queries umgehen, sodass die aufgebaute Subquery eine Liste von einmaligen IDs aller Blog-Eintrage zurückgibt, die eine Tag-Assoziation besitzen. Die SELECT-Query verwendet die entsprechende Subquery mit IN(), um damit die Liste der Blog-Einträge, deren Titel angezeigt werden soll, zu begrenzen.

6.5.3 Joins

Eine anderes, in komplexeren Abfragen häufig verwendetes SQL-Konstrukt, sind Joins, die ebenfalls von allen verwendeten RDBMS in deren aktuellen Versionen unterstützt werden.

$db = ezcDbInstance::get();
$query = $db->createSelectQuery();
$query
    ->select(
        $db->quoteIdentifier( 'title' ),
        $db->quoteIdentifier( 'tag' )
    )
    ->from( $db->quoteIdentifier( 'entry' ) )
    ->rightJoin(
        $db->quoteIdentifier( 'entry_tag' ),
        $query->expr->eq(
            $db->quoteIdentifier( 'entry_tag' ) . '.' .
                $db->quoteIdentifier( 'entry_id' ),
            $db->quoteIdentifier( 'entry' ) . '.' .

                $db->quoteIdentifier( 'id' )
        )
    )
    ->leftJoin(
        $db->quoteIdentifier( 'tag' ),
        $query->expr->eq(
            $db->quoteIdentifier( 'tag' ) . '.' .
                $db->quoteIdentifier( 'id' ),
            $db->quoteIdentifier( 'entry_tag' ) . '.' .
                $db->quoteIdentifier( 'tag_id' ) )
    );
$statement = $query->prepare();
$statement->execute();

Listing 6.16 Joins mit dem Query-Builder

Diese Abfrage listet alle Blog-Eintrage inklusive ihrer assoziierten Tags auf. Wie das Listing zeigt, werden die verschiedenen JOIN-Typen vom Query-Builder unterstützt, und der RIGHT JOIN stellt an dieser Stelle sicher, dass nur Blog-Einträge mit Tags in der Liste auftauchen. Die Methoden, um Joins in die Abfrage einzubauen, erwarten als ersten Parameter die Tabelle, mit denen das Daten-Produkt gebildet werden soll und als zweiten Parameter die Bedingung, unter der der Join durchgeführt werden soll.

+---------------------+----------+
| title               | tag      |
+---------------------+----------+
| Erster Blogeintrag  | galileo  |
+---------------------+----------+
| Erster Blogeintrag  | ezc      |
+---------------------+----------+
| Erster Blogeintrag  | blog     |
+---------------------+----------+
| Kapitel 6: Database | ezc      |
+---------------------+----------+
| Kapitel 6: Database | database |
+---------------------+----------+
| Kapitel 6: Database | blog     |
+---------------------+----------+

Listing 6.17 Resultat Queries

6.5.4 LIMIT und ORDER BY

Das LIMIT-Statement zur Begrenzung (Limitierung) der Anzahl der zurückgegebenen Datensätze wurde ursprünglich gerade für das Durchblättern vieler Datensätze in Webapplikationen bereitgestellt. Die einzelnen Treiber stellen jeweils eine spezifische Implementierung für dieses Konstrukt bereit. Natürlich steht es damit auch im Query-Builder zur Verfügung und wird zum Beispiel für das Oracle-Datenbanksystem entsprechend emuliert.

$query
    ->select( $db->quoteIdentifier( 'tag' ) )
    ->from( $db->quoteIdentifier( 'tag' ) )
    ->limit( 2, 1 )
    ->orderBy( $db->quoteIdentifier( 'tag' ) );

Listing 6.18 Limit und Order-By im Query-Builder

+----------+
| tag      |
+----------+
| database |
+----------+
| ezc      |
+----------+

Listing 6.19 Ergebnis Limit und Order-By im Query-Builder

Die Beispielabfrage gibt einen Teil der sortierten Liste der in der Datenbank enthaltenen Tags aus. Mit limit() wird als erster Parameter die maximale Anzahl der Datensätze spezifiziert, während der zweite Parameter den Offset für das SELECT angibt, also die Nummer des Datensatzes, mit dem zu beginnen ist. Die oderBy()-Methode akzeptiert wie gewohnt mindestens eine Spalte, nach der die Ausgabe sortiert wird, kann aber auch mit mehreren Spalten umgehen oder mehrfach aufgerufen werden.

Ihr Kommentar