Database Explorer

Mit dem Explorer beginnen Sie, indem Sie die Methode table() des Objekts Nette\Database\Explorer aufrufen (Details zum Einrichten der Datenbankverbindung finden Sie unter Verbindung und Konfiguration):

$books = $explorer->table('book'); // 'book' ist der Tabellenname

Die Methode gibt ein Objekt Selection zurück, das eine SQL-Query repräsentiert. An dieses Objekt lassen sich weitere Methoden zum Filtern und Sortieren der Ergebnisse anhängen. Die Query wird erst dann zusammengesetzt und ausgeführt, wenn die Daten angefordert werden, zum Beispiel beim Durchlaufen mit foreach. Jede Zeile wird durch ein Objekt ActiveRow repräsentiert:

foreach ($books as $book) {
	echo $book->title;        // Ausgabe der Spalte 'title'
	echo $book->author_id;    // Ausgabe der Spalte 'author_id'
}

Der Explorer erleichtert die Arbeit mit Beziehungen zwischen Tabellen ganz erheblich. Das folgende Beispiel zeigt, wie einfach wir Daten aus verknüpften Tabellen ausgeben können (Bücher und ihre Autoren). Beachten Sie, dass wir keine JOIN-Queries schreiben müssen, Nette erzeugt sie für uns:

$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'Buch: ' . $book->title;
	echo 'Autor: ' . $book->author->name; // erzeugt einen JOIN auf die Tabelle 'author'
}

Nette Database Explorer optimiert die Queries, damit sie möglichst effizient sind. Das obige Beispiel führt nur zwei SELECT-Queries aus, unabhängig davon, ob wir 10 oder 10 000 Bücher verarbeiten.

Darüber hinaus verfolgt der Explorer, welche Spalten im Code verwendet werden, und lädt nur diese aus der Datenbank, was weitere Leistung spart. Dieses Verhalten ist vollständig automatisch und anpassungsfähig. Wenn Sie den Code später ändern und weitere Spalten verwenden, passt der Explorer die Queries automatisch an. Sie müssen nichts konfigurieren und auch nicht darüber nachdenken, welche Spalten Sie brauchen werden – überlassen Sie das Nette.

Filterung und Sortierung

Die Klasse Selection stellt Methoden zum Filtern und Sortieren der Datenauswahl bereit.

Die Methoden lassen sich verketten (das sogenannte Fluent Interface): $table->where(...)->order(...)->limit(...).

In diesen Methoden können Sie außerdem spezielle Notationen für den Zugriff auf Daten aus verwandten Tabellen verwenden.

Escaping und Bezeichner

Die Methoden escapen Parameter automatisch und setzen Bezeichner (Tabellen- und Spaltennamen) in Anführungszeichen, wodurch SQL-Injection verhindert wird. Damit das richtig funktioniert, müssen einige Regeln eingehalten werden:

• Schreiben Sie Schlüsselwörter, Funktions- und Prozedurnamen usw. in Großbuchstaben.
• Schreiben Sie Spalten- und Tabellennamen in Kleinbuchstaben.
• Setzen Sie Strings immer über Parameter ein.

where('name = ' . $name);         // KRITISCHE SICHERHEITSLÜCKE: SQL-Injection
where('name LIKE "%search%"');    // FALSCH: erschwert das automatische Quoting
where('name LIKE ?', '%search%'); // RICHTIG: Wert über einen Parameter eingesetzt

where('name like ?', $name);     // FALSCH: erzeugt: `name` `like` ?
where('name LIKE ?', $name);     // RICHTIG: erzeugt: `name` LIKE ?
where('LOWER(name) = ?', $value);// RICHTIG: LOWER(`name`) = ?

where (string|array $condition, …$parameters): static

Filtert die Ergebnisse mit WHERE-Bedingungen. Ihre Stärke liegt im intelligenten Umgang mit verschiedenen Wertetypen und in der automatischen Wahl der passenden SQL-Operatoren.

Grundlegende Verwendung:

$table->where('id', $value);     // WHERE `id` = 123
$table->where('id > ?', $value); // WHERE `id` > 123
$table->where('id = ? OR name = ?', $id, $name); // WHERE `id` = 1 OR `name` = 'Jon Snow'

Dank der automatischen Erkennung passender Operatoren müssen Sie sich nicht um verschiedene Sonderfälle kümmern – Nette löst sie für Sie:

$table->where('id', 1);          // WHERE `id` = 1
$table->where('id', null);       // WHERE `id` IS NULL
$table->where('id', [1, 2, 3]);  // WHERE `id` IN (1, 2, 3)
// Sie können auch das Fragezeichen ohne Operator verwenden:
$table->where('id ?', 1);        // WHERE `id` = 1

Die Methode verarbeitet auch negative Bedingungen und leere Arrays korrekt:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- findet nichts
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- findet alles
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- findet alles
// $table->where('NOT id ?', $ids); // ACHTUNG: Diese Syntax wird nicht unterstützt

Als Parameter können Sie auch das Ergebnis einer anderen Tabellenabfrage übergeben – dabei entsteht eine Unterabfrage:

// WHERE `id` IN (SELECT `id` FROM `tableName`)
$table->where('id', $explorer->table($tableName));

// WHERE `id` IN (SELECT `col` FROM `tableName`)
$table->where('id', $explorer->table($tableName)->select('col'));

Bedingungen lassen sich auch als Array übergeben, dessen Elemente mit AND verknüpft werden:

// WHERE (`price_final` < `price_original`) AND (`stock_count` > `min_stock`)
$table->where([
	'price_final < price_original',
	'stock_count > min_stock',
]);

Im Array können Sie Paare aus Schlüssel ⇒ Wert verwenden, und Nette wählt wieder automatisch die richtigen Operatoren:

// WHERE (`status` = 'active') AND (`id` IN (1, 2, 3))
$table->where([
	'status' => 'active',
	'id' => [1, 2, 3],
]);

Im Array können Sie SQL-Ausdrücke mit Fragezeichen und mehreren Parametern kombinieren. Das eignet sich für komplexe Bedingungen mit genau festgelegten Operatoren:

// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // zwei Parameter werden als Array übergeben
]);

Mehrfache Aufrufe von where() verknüpfen die Bedingungen automatisch mit AND.

whereOr (array $parameters): static

Fügt ähnlich wie where() Bedingungen hinzu, verknüpft sie aber mit OR:

// WHERE (`status` = 'active') OR (`deleted` = 1)
$table->whereOr([
	'status' => 'active',
	'deleted' => true,
]);

Auch hier lassen sich komplexere Ausdrücke verwenden:

// WHERE (`price` > 1000) OR (`price_with_tax` > 1500)
$table->whereOr([
	'price > ?' => 1000,
	'price_with_tax > ?' => 1500,
]);

wherePrimary (mixed $key): static

Fügt eine Bedingung für den Primärschlüssel der Tabelle hinzu:

// WHERE `id` = 123
$table->wherePrimary(123);

// WHERE `id` IN (1, 2, 3)
$table->wherePrimary([1, 2, 3]);

Wenn die Tabelle einen zusammengesetzten Primärschlüssel hat (z. B. foo_id, bar_id), übergeben Sie ihn als Array:

// WHERE `foo_id` = 1 AND `bar_id` = 5
$table->wherePrimary(['foo_id' => 1, 'bar_id' => 5])->fetch();

// WHERE (`foo_id`, `bar_id`) IN ((1, 5), (2, 3))
$table->wherePrimary([
	['foo_id' => 1, 'bar_id' => 5],
	['foo_id' => 2, 'bar_id' => 3],
])->fetchAll();

order (string $columns, …$parameters): static

Bestimmt die Reihenfolge, in der die Zeilen zurückgegeben werden. Sie können nach einer oder mehreren Spalten sortieren, aufsteigend oder absteigend, oder nach einem eigenen Ausdruck:

$table->order('created');                   // ORDER BY `created`
$table->order('created DESC');              // ORDER BY `created` DESC
$table->order('priority DESC, created');    // ORDER BY `priority` DESC, `created`
$table->order('status = ? DESC', 'active'); // ORDER BY `status` = 'active' DESC

select (string $columns, …$parameters): static

Gibt an, welche Spalten aus der Datenbank zurückgegeben werden sollen. Standardmäßig gibt Nette Database Explorer nur die Spalten zurück, die im Code tatsächlich verwendet werden. Die Methode select() verwenden Sie also dann, wenn Sie bestimmte Ausdrücke zurückgeben müssen:

// SELECT *, DATE_FORMAT(`created_at`, "%d.%m.%Y") AS `formatted_date`
$table->select('*, DATE_FORMAT(created_at, ?) AS formatted_date', '%d.%m.%Y');

Mit AS definierte Aliase sind dann als Properties des ActiveRow-Objekts verfügbar:

foreach ($table as $row) {
	echo $row->formatted_date;   // Zugriff auf den Alias
}

limit (?int $limit, ?int $offset = null): static

Begrenzt die Anzahl der zurückgegebenen Zeilen (LIMIT) und erlaubt optional das Setzen eines Offsets:

$table->limit(10);        // LIMIT 10 (gibt die ersten 10 Zeilen zurück)
$table->limit(10, 20);    // LIMIT 10 OFFSET 20

Für die Paginierung ist es sinnvoller, die Methode page() zu verwenden.

page (int $page, int $itemsPerPage, &$numOfPages = null): static

Erleichtert die Paginierung der Ergebnisse. Sie nimmt die Seitennummer (beginnend bei 1) und die Anzahl der Einträge pro Seite entgegen. Optional können Sie eine Referenz auf eine Variable übergeben, in der die Gesamtzahl der Seiten gespeichert wird:

$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, numOfPages: $numOfPages);
echo "Seiten insgesamt: $numOfPages";

group (string $columns, …$parameters): static

Gruppiert die Zeilen nach den angegebenen Spalten (GROUP BY). Üblicherweise wird das in Verbindung mit Aggregatfunktionen verwendet:

// Zählt die Anzahl der Produkte in jeder Kategorie
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');

having (string $having, …$parameters): static

Setzt eine Bedingung zum Filtern gruppierter Zeilen (HAVING). Sie lässt sich in Verbindung mit der Methode group() und Aggregatfunktionen verwenden:

// Findet Kategorien mit mehr als 100 Produkten
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Daten lesen

Zum Lesen von Daten aus der Datenbank stehen mehrere nützliche Methoden zur Verfügung:

Das Objekt ActiveRow ist nur zum Lesen bestimmt. Das heißt, Sie können die Werte seiner Properties nicht ändern. Diese Einschränkung sichert die Konsistenz der Daten und verhindert unerwartete Nebenwirkungen. Die Daten werden aus der Datenbank geladen, und jede Änderung sollte explizit und kontrolliert erfolgen.

foreach – Iteration über alle Zeilen

Der einfachste Weg, eine Query auszuführen und die Zeilen zu erhalten, ist die Iteration in einer foreach-Schleife. Sie führt die SQL-Query automatisch aus.

$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key ist der Wert des Primärschlüssels, $book ist ActiveRow
	echo "$book->title ({$book->author->name})";
}

get ($key): ?ActiveRow

Führt die SQL-Query aus und gibt die Zeile anhand des Primärschlüssels zurück, oder null, wenn sie nicht existiert.

$book = $explorer->table('book')->get(123);  // gibt ActiveRow mit der ID 123 oder null zurück
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Gibt die aktuelle Zeile zurück und setzt den internen Zeiger auf die nächste. Wenn es keine weiteren Zeilen gibt, wird null zurückgegeben.

$books = $explorer->table('book');
while ($book = $books->fetch()) {
	$this->processBook($book);
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Gibt die Ergebnisse als assoziatives Array zurück. Das erste Argument bestimmt den Namen der Spalte, die als Schlüssel im Array verwendet wird, das zweite Argument den Namen der Spalte, die als Wert verwendet wird:

$authors = $explorer->table('author')->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Wenn nur der erste Parameter angegeben wird, ist der Wert die gesamte Zeile, also das Objekt ActiveRow:

$authors = $explorer->table('author')->fetchPairs('id');
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]

Bei doppelten Schlüsseln wird der Wert aus der letzten Zeile verwendet. Wird null als Schlüssel verwendet, ist das Array numerisch ab null indiziert (dann treten keine Kollisionen auf):

$authors = $explorer->table('author')->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Alternativ können Sie als Parameter einen Callback angeben, der für jede Zeile entweder einen einzelnen Wert oder ein Schlüssel-Wert-Paar zurückgibt.

$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// ['Erstes Buch (John Novak)', ...]

// Der Callback kann auch ein Array mit einem Schlüssel-Wert-Paar zurückgeben:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['Erstes Buch' => 'John Novak', ...]

fetchAll(): array

Gibt alle Zeilen als assoziatives Array von ActiveRow-Objekten zurück, wobei die Schlüssel die Werte der Primärschlüssel sind.

$allBooks = $explorer->table('book')->fetchAll();
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]

count(): int

Die Methode count() ohne Parameter gibt die Anzahl der Zeilen im Objekt Selection zurück:

$table->where('category', 1);
$count = $table->count();
$count = count($table); // Alternative

Achtung: count() mit einem Parameter führt die Aggregatfunktion COUNT in der Datenbank aus, siehe unten.

ActiveRow::toArray(): array

Wandelt das Objekt ActiveRow in ein assoziatives Array um, in dem die Schlüssel die Spaltennamen und die Werte die zugehörigen Daten sind.

$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray ist ['id' => 1, 'title' => '...', 'author_id' => ..., ...]

Aggregation

Die Klasse Selection stellt Methoden bereit, mit denen sich Aggregatfunktionen (COUNT, SUM, MIN, MAX, AVG usw.) leicht ausführen lassen.

count (string $expr): int

Führt eine SQL-Query mit der Funktion COUNT aus und gibt das Ergebnis zurück. Die Methode wird verwendet, um zu ermitteln, wie viele Zeilen einer bestimmten Bedingung entsprechen:

$count = $table->count('*');                 // SELECT COUNT(*) FROM `table`
$count = $table->count('DISTINCT column');   // SELECT COUNT(DISTINCT `column`) FROM `table`

Achtung: count() ohne Parameter gibt nur die Anzahl der Zeilen im Objekt Selection zurück.

min (string $expr) and max(string $expr)

Die Methoden min() und max() geben den kleinsten und den größten Wert in der angegebenen Spalte oder im angegebenen Ausdruck zurück:

// SELECT MAX(`price`) FROM `products` WHERE `active` = 1
$maxPrice = $products->where('active', true)
	->max('price');

sum (string $expr): mixed

Gibt die Summe der Werte in der angegebenen Spalte oder im angegebenen Ausdruck zurück:

// SELECT SUM(`price` * `items_in_stock`) FROM `products` WHERE `active` = 1
$totalPrice = $products->where('active', true)
	->sum('price * items_in_stock');

aggregation (string $function, ?string $groupFunction = null): mixed

Erlaubt die Ausführung einer beliebigen Aggregatfunktion.

// Durchschnittspreis der Produkte in einer Kategorie
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// verbindet die Tags eines Produkts zu einem einzigen String
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Wenn wir Ergebnisse aggregieren müssen, die selbst schon aus einer Aggregatfunktion und einer Gruppierung stammen (z. B. SUM(wert) über gruppierte Zeilen), geben wir als zweites Argument die Aggregatfunktion an, die auf diese Zwischenergebnisse angewendet werden soll:

// Berechnet den Gesamtpreis der Produkte auf Lager für die einzelnen Kategorien und summiert diese Preise anschließend.
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

In diesem Beispiel berechnen wir zuerst den Gesamtpreis der Produkte in jeder Kategorie (SUM(price * stock) AS category_total) und gruppieren die Ergebnisse nach category_id. Dann verwenden wir aggregation('SUM(category_total)', 'SUM'), um diese Zwischensummen category_total zu addieren. Das zweite Argument 'SUM' sagt, dass auf die Zwischenergebnisse die Funktion SUM angewendet werden soll.

Insert, Update & Delete

Nette Database Explorer vereinfacht das Einfügen, Aktualisieren und Löschen von Daten. Alle genannten Methoden werfen im Fehlerfall eine Nette\Database\DriverException.

Selection::insert (iterable $data)

Fügt neue Datensätze in die Tabelle ein.

Einfügen eines einzelnen Datensatzes:

Übergeben Sie den neuen Datensatz als assoziatives Array oder als iterierbares Objekt (etwa ArrayHash, das in Formularen verwendet wird), dessen Schlüssel den Spaltennamen in der Tabelle entsprechen.

Wenn die Tabelle einen definierten Primärschlüssel hat, gibt die Methode ein ActiveRow-Objekt zurück, das aus der Datenbank neu geladen wird, um eventuelle Änderungen auf Datenbankebene zu berücksichtigen (Trigger, Standardwerte von Spalten, Berechnung von Auto-Increment-Spalten). Damit ist die Konsistenz der Daten sichergestellt und das Objekt enthält immer die aktuellen Daten aus der Datenbank. Hat die Tabelle keinen Primärschlüssel, gibt es keine identifizierbare Zeile und die Methode gibt null zurück.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row ist eine Instanz von ActiveRow und enthält die vollständigen Daten der eingefügten Zeile,
// einschließlich der automatisch erzeugten ID und eventueller von Triggern vorgenommener Änderungen
echo $row->id; // Gibt die ID des neu eingefügten Benutzers aus
echo $row->created_at; // Gibt die Erstellungszeit aus, wenn sie von einem Trigger gesetzt wird

Einfügen mehrerer Datensätze auf einmal:

Die Methode insert() erlaubt das Einfügen mehrerer Datensätze mit einer einzigen SQL-Query. In diesem Fall gibt sie die Anzahl der eingefügten Zeilen zurück.

$insertedRows = $explorer->table('users')->insert([
	[
		'name' => 'John',
		'year' => 1994,
	],
	[
		'name' => 'Jack',
		'year' => 1995,
	],
]);
// INSERT INTO `users` (`name`, `year`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows ist 2

Als Parameter lässt sich auch ein Selection-Objekt mit einer Datenauswahl übergeben.

$newUsers = $explorer->table('potential_users')
	->where('approved', 1)
	->select('name, email');

$insertedRows = $explorer->table('users')->insert($newUsers);

Einfügen spezieller Werte:

Als Werte können wir auch Dateien, DateTime-Objekte oder SQL-Literale übergeben:

$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // wandelt in das Datenbankformat um
	'avatar' => fopen('image.jpg', 'rb'),   // fügt den binären Inhalt der Datei ein
	'uuid' => $explorer::literal('UUID()'), // ruft die Funktion UUID() auf
]);

Selection::update (iterable $data): int

Aktualisiert die Zeilen der Tabelle nach dem angegebenen Filter. Gibt die Anzahl der tatsächlich geänderten Zeilen zurück.

Übergeben Sie die zu ändernden Spalten als assoziatives Array oder als iterierbares Objekt (etwa ArrayHash, das in Formularen verwendet wird), dessen Schlüssel den Spaltennamen in der Tabelle entsprechen:

$affected = $explorer->table('users')
	->where('id', 10)
	->update([
		'name' => 'John Smith',
		'year' => 1994,
	]);
// UPDATE `users` SET `name` = 'John Smith', `year` = 1994 WHERE `id` = 10

Zum Ändern numerischer Werte können Sie die Operatoren += und -= verwenden:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // erhöht den Wert der Spalte 'points' um 1
		'coins-=' => 1,   // verringert den Wert der Spalte 'coins' um 1
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10

Selection::delete(): int

Löscht Zeilen aus der Tabelle nach dem angegebenen Filter. Gibt die Anzahl der gelöschten Zeilen zurück.

$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE `id` = 10

Vergessen Sie beim Aufruf von update() oder delete() nicht, mit where() die Zeilen anzugeben, die geändert bzw. gelöscht werden sollen. Wenn Sie where() nicht verwenden, wird die Operation auf der gesamten Tabelle ausgeführt!

ActiveRow::update (iterable $data): bool

Aktualisiert die Daten in der Datenbankzeile, die durch das Objekt ActiveRow repräsentiert wird. Die Methode nimmt ein Iterable mit den zu aktualisierenden Daten entgegen (die Schlüssel sind Spaltennamen). Zum Ändern numerischer Werte können Sie die Operatoren += und -= verwenden:

Nach der Aktualisierung wird das ActiveRow automatisch aus der Datenbank neu geladen, um eventuelle Änderungen auf Datenbankebene zu berücksichtigen (z. B. durch Trigger). Die Methode gibt nur dann true zurück, wenn eine tatsächliche Datenänderung stattgefunden hat.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // erhöht die Anzahl der Aufrufe
]);
echo $article->views; // Gibt die aktuelle Anzahl der Aufrufe aus

Diese Methode aktualisiert nur eine bestimmte Zeile in der Datenbank. Für die Massenaktualisierung mehrerer Zeilen verwenden Sie die Methode Selection::update().

ActiveRow::delete(): int

Löscht die Zeile aus der Datenbank, die durch das Objekt ActiveRow repräsentiert wird. Gibt die Anzahl der gelöschten Zeilen zurück, die 1 sein sollte.

$book = $explorer->table('book')->get(1);
$book->delete(); // Löscht das Buch mit der ID 1

Diese Methode löscht nur eine bestimmte Zeile in der Datenbank. Für das Massenlöschen mehrerer Zeilen verwenden Sie die Methode Selection::delete().

Beziehungen zwischen Tabellen

In relationalen Datenbanken sind die Daten auf mehrere Tabellen verteilt und über Fremdschlüssel miteinander verknüpft. Nette Database Explorer bringt eine revolutionäre Art, mit diesen Beziehungen zu arbeiten – ohne JOIN-Queries zu schreiben und ohne dass etwas konfiguriert oder generiert werden müsste.

Zur Veranschaulichung der Arbeit mit Beziehungen verwenden wir eine Beispieldatenbank mit Büchern (Sie finden sie auf GitHub). In der Datenbank haben wir die Tabellen:

• author – Schriftsteller und Übersetzer (Spalten id, name, web, born)
• book – Bücher (Spalten id, author_id, translator_id, title, sequel_id)
• tag – Tags (Spalten id, name)
• book_tag – Verknüpfungstabelle zwischen Büchern und Tags (Spalten book_id, tag_id)

In unserer Beispieldatenbank mit Büchern finden wir mehrere Arten von Beziehungen (auch wenn das Modell gegenüber der Realität vereinfacht ist):

• One-to-many (1:N) – Jedes Buch hat einen Autor; ein Autor kann mehrere Bücher schreiben.
• Zero-to-many (0:N) – Ein Buch kann einen Übersetzer haben; ein Übersetzer kann mehrere Bücher übersetzen.
• Zero-to-one (0:1) – Ein Buch kann eine Fortsetzung haben.
• Many-to-many (M:N) – Ein Buch kann mehrere Tags haben, und ein Tag kann mehreren Büchern zugeordnet sein.

In diesen Beziehungen gibt es immer eine übergeordnete Tabelle und eine untergeordnete Tabelle. Zum Beispiel ist in der Beziehung zwischen Autoren und Büchern die Tabelle author die übergeordnete und die Tabelle book die untergeordnete – Sie können sich das so vorstellen, dass ein Buch immer zu einem Autor “gehört”. Das zeigt sich auch in der Struktur der Datenbank: Die untergeordnete Tabelle book enthält den Fremdschlüssel author_id, der auf die übergeordnete Tabelle author verweist.

Wenn wir Bücher samt den Namen ihrer Autoren ausgeben müssen, haben wir zwei Möglichkeiten. Entweder holen wir die Daten mit einer einzigen SQL-Query per JOIN:

SELECT book.*, author.name FROM book LEFT JOIN author ON book.author_id = author.id;

Oder wir laden die Daten in zwei Schritten – zuerst die Bücher, dann ihre Autoren – und setzen sie anschließend in PHP zusammen:

SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3);  -- IDs der Autoren der ausgewählten Bücher

Der zweite Ansatz ist in Wirklichkeit effizienter, auch wenn das überraschen mag. Die Daten werden nur einmal geladen und lassen sich besser im Cache nutzen. Genau so arbeitet Nette Database Explorer – er löst alles unter der Oberfläche und bietet Ihnen eine elegante API:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author ist ein Datensatz aus der Tabelle 'author'
	echo 'translated by: ' . $book->translator?->name;
}

Zugriff auf die übergeordnete Tabelle

Der Zugriff auf die übergeordnete Tabelle ist geradlinig. Es geht um Beziehungen wie ein Buch hat einen Autor oder ein Buch kann einen Übersetzer haben. Den verwandten Datensatz erhalten wir über eine Property des ActiveRow-Objekts – ihr Name entspricht dem Namen der Fremdschlüsselspalte ohne das Suffix _id:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // findet den Autor anhand der Spalte author_id
echo $book->translator?->name; // findet den Übersetzer anhand der Spalte translator_id

Wenn wir auf die Property $book->author zugreifen, sucht der Explorer in der Tabelle book nach einer Spalte, deren Name die Zeichenfolge author enthält (also author_id). Anhand des Werts in dieser Spalte lädt er den entsprechenden Datensatz aus der Tabelle author und gibt ihn als ActiveRow zurück. Ebenso funktioniert $book->translator, das die Spalte translator_id nutzt. Weil die Spalte translator_id den Wert null enthalten kann, verwenden wir im Code den Nullsafe-Operator ?->.

Einen alternativen Weg bietet die Methode ref(), die zwei Argumente entgegennimmt, den Namen der Zieltabelle und den Namen der verbindenden Spalte, und eine ActiveRow-Instanz oder null zurückgibt:

echo $book->ref('author', 'author_id')->name;      // Beziehung zum Autor
echo $book->ref('author', 'translator_id')->name;  // Beziehung zum Übersetzer

Die Methode ref() ist nützlich, wenn sich der Zugriff über eine Property nicht verwenden lässt, etwa weil die Tabelle eine Spalte mit demselben Namen enthält (also author). In den übrigen Fällen wird der Zugriff über Properties empfohlen, weil er besser lesbar ist.

Der Explorer optimiert die Datenbank-Queries automatisch. Wenn wir Bücher in einer Schleife durchlaufen und auf ihre verwandten Datensätze (Autoren, Übersetzer) zugreifen, erzeugt der Explorer nicht für jedes Buch eine eigene Query. Stattdessen führt er nur eine SELECT-Query für jede Art von Beziehung aus, was die Last der Datenbank deutlich senkt. Zum Beispiel:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	echo $book->translator?->name;
}

Dieser Code führt nur diese drei blitzschnellen Queries an die Datenbank aus:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- IDs aus der Spalte author_id der ausgewählten Bücher
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- IDs aus der Spalte translator_id der ausgewählten Bücher

Die Logik zum Auffinden der verbindenden Spalte wird durch die Implementierung von Conventions bestimmt. Wir empfehlen die Verwendung von DiscoveredConventions, die Fremdschlüssel analysiert und Ihnen erlaubt, einfach mit den bestehenden Beziehungen zwischen Tabellen zu arbeiten.

Zugriff auf die untergeordnete Tabelle

Der Zugriff auf die untergeordnete Tabelle funktioniert in umgekehrter Richtung. Jetzt fragen wir, welche Bücher dieser Autor geschrieben oder welche Bücher dieser Übersetzer übersetzt hat. Für diese Art von Abfrage verwenden wir die Methode related(), die eine Selection mit den verwandten Datensätzen zurückgibt. Sehen wir uns ein Beispiel an:

$author = $explorer->table('author')->get(1);

// Gibt alle Bücher des Autors aus
foreach ($author->related('book.author_id') as $book) {
	echo "Geschrieben: $book->title";
}

// Gibt alle Bücher aus, die der Autor übersetzt hat
foreach ($author->related('book.translator_id') as $book) {
	echo "Übersetzt: $book->title";
}

Die Methode related() nimmt die Beschreibung der Verknüpfung als ein einziges Argument in Punktnotation oder als zwei getrennte Argumente entgegen:

$author->related('book.translator_id');  // ein Argument
$author->related('book', 'translator_id');  // zwei Argumente

Der Explorer kann die richtige verbindende Spalte automatisch anhand des Namens der übergeordneten Tabelle erkennen. In diesem Fall wird über die Spalte book.author_id verknüpft, weil der Name der Quelltabelle author lautet:

$author->related('book');  // verwendet book.author_id

Wenn mehrere mögliche Verknüpfungen existieren, wirft der Explorer eine AmbiguousReferenceKeyException.

Die Methode related() können wir natürlich auch beim Durchlaufen mehrerer Datensätze in einer Schleife verwenden, und der Explorer optimiert die Queries auch in diesem Fall automatisch:

$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' schrieb:';
	foreach ($author->related('book') as $book) {
		echo $book->title;
	}
}

Dieser Code erzeugt nur zwei blitzschnelle SQL-Queries:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- IDs der ausgewählten Autoren

Many-to-Many-Beziehung

Für eine Many-to-many-Beziehung (M:N) wird eine Verknüpfungstabelle benötigt (in unserem Fall book_tag), die zwei Fremdschlüsselspalten enthält (book_id, tag_id). Jede dieser Spalten verweist auf den Primärschlüssel einer der verknüpften Tabellen. Um die verwandten Daten zu erhalten, holen wir zuerst die Datensätze aus der Verknüpfungstabelle mit related('book_tag') und gehen dann weiter zu den Zieldaten:

$book = $explorer->table('book')->get(1);
// gibt die Namen der dem Buch zugeordneten Tags aus
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name;  // gibt den Namen des Tags über die Verknüpfungstabelle aus
}

$tag = $explorer->table('tag')->get(1);
// oder umgekehrt: gibt die Namen der mit diesem Tag markierten Bücher aus
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // gibt den Titel des Buches aus
}

Der Explorer optimiert die SQL-Queries wieder in eine effiziente Form:

SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- IDs der ausgewählten Bücher
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- IDs der in book_tag gefundenen Tags

Abfragen über verwandte Tabellen

In den Methoden where(), select(), order() und group() können Sie spezielle Notationen verwenden, um auf Spalten aus anderen Tabellen zuzugreifen. Der Explorer erzeugt die nötigen JOINs automatisch.

Punktnotation (übergeordnete_tabelle.spalte) wird für 1:N-Beziehungen aus Sicht der untergeordneten Tabelle verwendet:

$books = $explorer->table('book');

// Findet Bücher, deren Autorenname mit 'Jon' beginnt
$books->where('author.name LIKE ?', 'Jon%');

// Sortiert die Bücher absteigend nach dem Autorennamen
$books->order('author.name DESC');

// Gibt den Buchtitel und den Autorennamen aus
$books->select('book.title, author.name');

Doppelpunktnotation (:untergeordnete_tabelle.spalte) wird für 1:N-Beziehungen aus Sicht der übergeordneten Tabelle verwendet:

$authors = $explorer->table('author');

// Findet Autoren, die ein Buch mit 'PHP' im Titel geschrieben haben
$authors->where(':book.title LIKE ?', '%PHP%');

// Zählt die Anzahl der Bücher je Autor
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

Im obigen Beispiel mit der Doppelpunktnotation (:book.title) ist die Fremdschlüsselspalte nicht angegeben. Der Explorer erkennt die richtige Spalte automatisch anhand des Namens der übergeordneten Tabelle. In diesem Fall wird über die Spalte book.author_id verknüpft, weil der Name der Quelltabelle author lautet. Wenn mehrere mögliche Verknüpfungen existieren, wirft der Explorer eine AmbiguousReferenceKeyException.

Die verbindende Spalte lässt sich explizit in Klammern angeben:

// Findet Autoren, die ein Buch mit 'PHP' im Titel übersetzt haben
$authors->where(':book(translator_id).title LIKE ?', '%PHP%');

Die Notationen lassen sich verketten, um über mehrere Tabellen hinweg auf Daten zuzugreifen:

// Findet die Autoren von Büchern, die mit dem Tag 'PHP' markiert sind
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

Erweiterung der Bedingungen für JOIN

Die Methode joinWhere() erweitert die Bedingungen, die beim Verknüpfen von Tabellen in SQL hinter dem Schlüsselwort ON angegeben werden.

Nehmen wir an, wir wollen Bücher finden, die von einem bestimmten Übersetzer übersetzt wurden:

// Findet Bücher, die von einem Übersetzer namens 'David' übersetzt wurden
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')

In der Bedingung von joinWhere() können Sie dieselben Konstrukte verwenden wie in der Methode where() – Operatoren, Fragezeichen, Arrays von Werten oder SQL-Ausdrücke.

Für komplexere Queries mit mehreren JOINs können Sie Tabellenaliase definieren:

$tags = $explorer->table('tag')
	->joinWhere(':book_tag.book.author', 'book_author.born < ?', 1950)
	->alias(':book_tag.book.author', 'book_author');
// LEFT JOIN `book_tag` ON `tag`.`id` = `book_tag`.`tag_id`
// LEFT JOIN `book` ON `book_tag`.`book_id` = `book`.`id`
// LEFT JOIN `author` `book_author` ON `book`.`author_id` = `book_author`.`id`
//    AND (`book_author`.`born` < 1950)

Beachten Sie, dass die Methode where() Bedingungen zur WHERE-Klausel hinzufügt, während die Methode joinWhere() die Bedingungen in der ON-Klausel beim Verknüpfen der Tabellen erweitert.