Datenbank-Explorer

Die Arbeit mit dem Explorer beginnt mit dem Aufruf der Methode table() auf dem Objekt Nette\Database\Explorer (Informationen zur Erstellung von Verbindungen und zur Konfiguration finden Sie auf der entsprechenden Seite):

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

Die Methode gibt ein Selection-Objekt zurück, das eine SQL-Abfrage darstellt. Zusätzliche Methoden können mit diesem Objekt verkettet werden, um die Ergebnisse zu filtern und zu sortieren. Die Abfrage wird nur zusammengestellt und ausgeführt, wenn die Daten angefordert werden, z. B. durch Iteration mit foreach. Jede Zeile wird durch ein ActiveRow-Objekt dargestellt:

foreach ($books as $book) {
	echo $book->title;        // gibt die Spalte "Titel" aus
	echo $book->author_id;    // gibt die Spalte "author_id" aus
}

Explorer vereinfacht die Arbeit mit Tabellenbeziehungen erheblich. Das folgende Beispiel zeigt, wie einfach wir Daten aus verbundenen Tabellen (Bücher und ihre Autoren) ausgeben können. Beachten Sie, dass keine JOIN-Abfragen geschrieben werden müssen; Nette generiert sie für uns:

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

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // erstellt einen JOIN zur Tabelle 'author'.
}

Nette Database Explorer optimiert die Abfragen für maximale Effizienz. Das obige Beispiel führt nur zwei SELECT-Abfragen 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 holt nur diese aus der Datenbank, um weitere Leistung zu sparen. Dieses Verhalten ist vollautomatisch und anpassungsfähig. Wenn Sie den Code später ändern, um zusätzliche Spalten zu verwenden, passt der Explorer die Abfragen automatisch an. Sie brauchen nichts zu konfigurieren oder darüber nachzudenken, welche Spalten benötigt werden – überlassen Sie das Nette.

Filtern und Sortieren

Die Klasse Selection bietet Methoden zum Filtern und Sortieren von Daten.

Methoden können verkettet werden (sogenannte fließende Schnittstelle): $table->where(...)->order(...)->limit(...).

Diese Methoden erlauben auch die Verwendung spezieller Notationen für den Zugriff auf Daten aus Bezugstabellen.

Escaping und Bezeichner

Die Methoden entschlüsseln automatisch Parameter und Bezeichner (Tabellen- und Spaltennamen), um SQL-Injection zu verhindern. Um einen ordnungsgemäßen Betrieb zu gewährleisten, müssen einige Regeln beachtet werden:

• Schreiben Sie Schlüsselwörter, Funktionsnamen, Prozeduren usw. in Großbuchstaben.
• Schreiben Sie Spalten- und Tabellennamen in Kleinbuchstaben.
• Übergeben Sie Zeichenketten immer mit Parametern.

where('name = ' . $name);         // **DISASTER**: anfällig für SQL-Injection
where('name LIKE "%search%"');    // **FALSCH**: erschwert die automatische Quotierung
where('name LIKE ?', '%search%'); // **CORRECT**: Wert wird als Parameter übergeben

where('name like ?', $name);     // **WRONG**: erzeugt: `Name` `wie` ?
where('name LIKE ?', $name);     // **KORREKT**: erzeugt: `Name` LIKE ?
where('LOWER(name) = ?', $value);// **RICHTIG**: LOWER(`name`) = ?

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

Filtert Ergebnisse mithilfe von WHERE-Bedingungen. Seine Stärke liegt in der intelligenten Handhabung verschiedener Wertetypen und der automatischen Auswahl von 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 geeigneter Operatoren müssen Sie sich nicht um Spezialfälle kümmern – Nette erledigt das 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)
// Der Platzhalter ? kann ohne Operator verwendet werden:
$table->where('id ?', 1);        // WHERE `id` = 1

Die Methode geht auch mit negativen Bedingungen und leeren Feldern korrekt um:

$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); // WARNUNG: Diese Syntax wird nicht unterstützt

Sie können auch das Ergebnis einer anderen Tabellenabfrage als Parameter übergeben und so eine Unterabfrage erstellen:

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

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

Bedingungen können auch als Array übergeben werden, wobei die Elemente mit AND kombiniert werden:

// WHERE (`Preis_endgültig` < `Preis_originell`) AND (`Bestand_Zahl` > `min_Bestand`)
$table->where([
	'price_final &lt; price_original',
	'stock_count > min_stock',
]);

In dem Array können Schlüssel-Wert-Paare verwendet werden, und Nette wählt wieder automatisch die richtigen Operatoren aus:

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

Wir können auch SQL-Ausdrücke mit Platzhaltern und mehreren Parametern mischen. Dies ist nützlich für komplexe Bedingungen mit genau definierten Operatoren:

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

Mehrere Aufrufe von where() kombinieren die Bedingungen automatisch mit AND.

whereOr (array $parameters): static

Ähnlich wie where(), kombiniert jedoch Bedingungen mit OR:

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

Es können auch komplexere Ausdrücke verwendet werden:

// WHERE (`Preis` > 1000) OR (`Preis_mit_Steuer` > 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), wird dieser als Array übergeben:

// 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

Legt die Reihenfolge fest, in der die Zeilen zurückgegeben werden. Sie können nach einer oder mehreren Spalten, in aufsteigender oder absteigender Reihenfolge oder nach einem benutzerdefinierten Ausdruck sortieren:

$table->order('created');                   // ORDER BY `erstellt`
$table->order('created DESC');              // ORDER BY `erstellt` DESC
$table->order('priority DESC, created');    // ORDER BY `priorität` DESC, `erstellt`
$table->order('status = ? DESC', 'active'); // ORDER BY `Status` = 'aktiv' DESC

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

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

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

Die mit AS definierten Aliase sind dann als Eigenschaften des ActiveRow Objekts zugänglich:

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 legt optional einen Offset fest:

$table->limit(10);        // LIMIT 10 (liefert die ersten 10 Zeilen)
$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

Vereinfacht die Seitennummerierung der Ergebnisse. Sie akzeptiert die Seitenzahl (beginnend mit 1) und die Anzahl der Elemente pro Seite. Optional können Sie einen Verweis auf eine Variable übergeben, in der die Gesamtzahl der Seiten gespeichert wird:

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

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

Gruppiert Zeilen nach den angegebenen Spalten (GROUP BY). Sie wird normalerweise in Kombination 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

Legt eine Bedingung für das Filtern von gruppierten Zeilen fest (HAVING). Sie kann in Kombination mit der Methode group() und Aggregatfunktionen verwendet werden:

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

Daten lesen

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

Das ActiveRow-Objekt ist schreibgeschützt. Das bedeutet, dass Sie die Werte seiner Eigenschaften nicht ändern können. Diese Einschränkung gewährleistet die Datenkonsistenz und verhindert unerwartete Nebeneffekte. Die Daten werden aus der Datenbank geholt, und alle Änderungen sollten ausdrücklich und kontrolliert vorgenommen werden.

foreach – Iterieren durch alle Zeilen

Der einfachste Weg, eine Abfrage auszuführen und Zeilen abzurufen, ist die Iteration mit der foreach -Schleife. Dabei wird die SQL-Abfrage automatisch ausgeführt.

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

get ($key): ?ActiveRow

Führt eine SQL-Abfrage aus und gibt eine Zeile anhand ihres Primärschlüssels oder null zurück, wenn sie nicht vorhanden ist.

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

fetch(): ?ActiveRow

Gibt eine Zeile zurück und rückt den internen Zeiger auf die nächste Zeile vor. 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 in Form eines assoziativen Arrays zurück. Das erste Argument gibt den Namen der Spalte an, die als Array-Schlüssel verwendet werden soll, und das zweite Argument gibt den Namen der Spalte an, die als Wert verwendet werden soll:

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

Wenn nur der erste Parameter angegeben wird, wird die gesamte Zeile als Wert verwendet und als ActiveRow Objekt dargestellt:

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

Wenn null als Schlüssel angegeben wird, wird das Array numerisch indiziert, beginnend bei Null:

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

fetchPairs (Closure $callback): array

Alternativ können Sie auch einen Callback als Parameter übergeben. Der Callback wird auf jede Zeile angewendet, um entweder einen einzelnen Wert oder ein Schlüssel-Wert-Paar zurückzugeben.

$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// [1 => 'Erstes Buch (Jan 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' => 'Jan Novak', ...]

fetchAll(): array

Gibt alle Zeilen als assoziatives Array von ActiveRow Objekten zurück, wobei die Schlüssel die Primärschlüsselwerte 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); // alternativ

Hinweis: count() mit einem Parameter führt die COUNT-Aggregationsfunktion in der Datenbank aus, wie unten beschrieben.

ActiveRow::toArray(): array

Konvertiert das Objekt ActiveRow in ein assoziatives Array, wobei die Schlüssel Spaltennamen und die Werte die entsprechenden Daten sind.

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

Aggregation

Die Klasse Selection bietet Methoden zur einfachen Durchführung von Aggregationsfunktionen (COUNT, SUM, MIN, MAX, AVG, etc.).

count (string $expr): int

Führt eine SQL-Abfrage mit der Funktion COUNT aus und gibt das Ergebnis zurück. Diese Methode wird verwendet, um festzustellen, wie viele Zeilen eine bestimmte Bedingung erfüllen:

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

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

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

Die Methoden min() und max() geben die Minimal- und Maximalwerte in der angegebenen Spalte oder dem Ausdruck zurück:

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

sum (string $expr): int

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

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

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

Erlaubt die Ausführung einer beliebigen Aggregationsfunktion.

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

// Kombiniert Produkt-Tags zu einer einzigen Zeichenfolge
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

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

// Berechnet den Gesamtpreis der auf Lager befindlichen Produkte für jede Kategorie und summiert dann diese Preise
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

In diesem Beispiel berechnen wir zunächst den Gesamtpreis der Produkte in jeder Kategorie (SUM(price * stock) AS category_total) und gruppieren die Ergebnisse nach category_id. Anschließend verwenden wir aggregation('SUM(category_total)', 'SUM'), um diese Zwischensummen zu summieren. Das zweite Argument 'SUM' gibt die Aggregationsfunktion an, die auf die Zwischenergebnisse anzuwenden ist.

Einfügen, Aktualisieren und Löschen

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

Selection::insert (iterable $data): static

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

Einfügen eines einzelnen Datensatzes:

Der neue Datensatz wird als assoziatives Array oder iterierbares Objekt (wie ArrayHash, das in Formularen verwendet wird) übergeben, wobei die Schlüssel mit den Spaltennamen in der Tabelle übereinstimmen.

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 alle auf Datenbankebene vorgenommenen Änderungen widerzuspiegeln (z. B. Trigger, Standardspaltenwerte oder automatische Inkrementberechnungen). Dadurch wird die Datenkonsistenz gewährleistet, und das Objekt enthält immer die aktuellen Datenbankdaten. Wenn ein Primärschlüssel nicht explizit definiert ist, gibt die Methode die Eingabedaten als Array zurück.

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

Einfügen mehrerer Datensätze auf einmal:

Mit der Methode insert() können Sie mit einer einzigen SQL-Abfrage mehrere Datensätze einfügen. 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 `Benutzer` (`Name`, `Jahr`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows wird 2 sein

Sie können auch ein Selection Objekt mit einer Auswahl von Daten als Parameter übergeben.

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

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

Einfügen spezieller Werte:

Die Werte können Dateien, DateTime Objekte oder SQL-Literale enthalten:

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

Selection::update (iterable $data): int

Aktualisiert Zeilen in einer Tabelle basierend auf einem angegebenen Filter. Gibt die Anzahl der tatsächlich geänderten Zeilen zurück.

Die zu aktualisierenden Spalten werden als assoziatives Array oder iterierbares Objekt (wie ArrayHash, das in Formularen verwendet wird) übergeben, wobei die Schlüssel mit den Spaltennamen in der Tabelle übereinstimmen:

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

Um numerische Werte zu ändern, können Sie die Operatoren += und -= verwenden:

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

Selection::delete(): int

Löscht Zeilen aus einer Tabelle basierend auf einem angegebenen Filter. Gibt die Anzahl der gelöschten Zeilen zurück.

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

Wenn Sie update() oder delete() aufrufen, müssen Sie where() verwenden, um die zu aktualisierenden oder zu löschenden Zeilen anzugeben. Wenn where() nicht verwendet wird, wird der Vorgang für die gesamte Tabelle durchgeführt!

ActiveRow::update (iterable $data): bool

Aktualisiert die Daten in einer Datenbankzeile, die durch das Objekt ActiveRow dargestellt wird. Es akzeptiert iterierbare Daten als Parameter, wobei die Schlüssel Spaltennamen sind. Um numerische Werte zu ändern, können Sie die Operatoren += und -= verwenden:

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

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

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

ActiveRow::delete()

Löscht eine Zeile aus der Datenbank, die durch das Objekt ActiveRow repräsentiert wird.

$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 von mehreren Zeilen verwenden Sie die Methode Selection::delete().

Beziehungen zwischen Tabellen

In relationalen Datenbanken sind die Daten in mehrere Tabellen aufgeteilt und durch Fremdschlüssel miteinander verbunden. Nette Database Explorer bietet eine revolutionäre Möglichkeit, mit diesen Beziehungen zu arbeiten – ohne JOIN-Abfragen zu schreiben oder irgendeine Konfiguration oder Entitätserzeugung zu benötigen.

Zur Demonstration verwenden wir die Beispieldatenbank(verfügbar auf GitHub). Die Datenbank enthält die folgenden Tabellen:

• author – Autoren 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)

Datenbankstruktur

In diesem Beispiel einer Buchdatenbank finden wir mehrere Arten von Beziehungen (vereinfacht im Vergleich zur Realität):

• Eine-zu-viele (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.
• Null-zu-Eins (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 zugewiesen werden.

In diesen Beziehungen gibt es immer eine Elterntabelle und eine Kindertabelle. In der Beziehung zwischen Autoren und Büchern ist zum Beispiel die Tabelle author die Elterntabelle und die Tabelle book das Kind – man kann sich das so vorstellen, dass ein Buch immer zu einem Autor “gehört”. Dies spiegelt sich auch in der Datenbankstruktur wider: Die untergeordnete Tabelle book enthält den Fremdschlüssel author_id, der auf die übergeordnete Tabelle author verweist.

Wenn wir die Bücher zusammen mit den Namen ihrer Autoren anzeigen wollen, haben wir zwei Möglichkeiten. Entweder wir rufen die Daten über eine einzige SQL-Abfrage mit einem JOIN ab:

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

Oder wir rufen die Daten in zwei Schritten ab – erst die Bücher, dann die Autoren – und stellen sie in PHP zusammen:

SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3); -- IDs of authors retrieved from books

Der zweite Ansatz ist überraschenderweise effizienter. Die Daten werden nur einmal abgerufen und können im Cache besser genutzt werden. Genau so funktioniert der Nette Database Explorer – er erledigt alles unter der Haube und bietet Ihnen eine saubere 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 ganz einfach. Es handelt sich um Beziehungen wie ein Buch hat einen Autor oder ein Buch kann einen Übersetzer haben. Auf den Bezugsdatensatz kann über die Objekteigenschaft ActiveRow zugegriffen werden – der Eigenschaftsname entspricht dem Spaltennamen des Fremdschlüssels ohne das Suffix id:

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

Beim Zugriff auf die Eigenschaft $book->author sucht der Explorer nach einer Spalte in der Tabelle book, die die Zeichenfolge author enthält (d. h. author_id). Basierend auf dem Wert in dieser Spalte ruft er den entsprechenden Datensatz aus der Tabelle author ab und gibt ihn als ActiveRow Objekt zurück. In ähnlicher Weise verwendet $book->translator die Spalte translator_id. Da die Spalte translator_id null enthalten kann, wird der Operator ?-> verwendet.

Einen alternativen Ansatz bietet die Methode ref(), die zwei Argumente akzeptiert – den Namen der Zieltabelle und die Verknüpfungsspalte – und eine ActiveRow Instanz oder null zurückgibt:

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

Die Methode ref() ist nützlich, wenn der eigenschaftsbasierte Zugriff nicht verwendet werden kann, z. B. wenn die Tabelle eine Spalte mit demselben Namen wie die Eigenschaft enthält (author). In anderen Fällen wird die Verwendung des eigenschaftsbasierten Zugriffs zur besseren Lesbarkeit empfohlen.

Der Explorer optimiert automatisch die Datenbankabfragen. Bei der Iteration durch Bücher und dem Zugriff auf die zugehörigen Datensätze (Autoren, Übersetzer) erstellt der Explorer nicht für jedes Buch eine eigene Abfrage. Stattdessen führt er nur eine SELECT-Abfrage für jede Art von Beziehung aus, was die Datenbankbelastung erheblich reduziert. Ein 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 drei optimierte Datenbankabfragen aus:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- IDs from 'author_id' column in selected books
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- IDs from 'translator_id' column in selected books

Die Logik zur Identifizierung der Verknüpfungsspalte wird durch die Implementierung von Conventions definiert. Wir empfehlen die Verwendung von DiscoveredConventions, das Fremdschlüssel analysiert und es Ihnen ermöglicht, nahtlos mit bestehenden Tabellenbeziehungen zu arbeiten.

Zugriff auf die Child-Tabelle

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

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

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

// Gibt alle vom Autor übersetzten Bücher aus
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}

Die Methode related() akzeptiert die Beziehungsbeschreibung als ein einziges Argument in Punktschreibweise oder als zwei separate Argumente:

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

Der Explorer kann automatisch die richtige Verknüpfungsspalte anhand des Namens der übergeordneten Tabelle erkennen. In diesem Fall erfolgt die Verknüpfung über die Spalte book.author_id, da der Name der Quelltabelle author lautet:

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

Wenn mehrere mögliche Verbindungen bestehen, löst der Explorer eine Ausnahme AmbiguousReferenceKeyException aus.

Wir können natürlich auch die Methode related() verwenden, wenn wir durch mehrere Datensätze in einer Schleife iterieren, und der Explorer wird die Abfragen auch in diesem Fall automatisch optimieren:

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

Dieser Code erzeugt nur zwei effiziente SQL-Abfragen:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- IDs of the selected authors

Many-to-Many-Beziehung

Für eine Many-to-Many-Beziehung (M:N) ist eine Verzweigungstabelle (in unserem Fall book_tag) erforderlich. Diese Tabelle enthält zwei Fremdschlüsselspalten (book_id, tag_id). Jede Spalte verweist auf den Primärschlüssel einer der verbundenen Tabellen. Um Bezugsdaten abzurufen, holen wir zunächst Datensätze aus der Verknüpfungstabelle mit related('book_tag') und fahren dann mit den Zieldaten fort:

$book = $explorer->table('book')->get(1);
// Gibt die Namen der dem Buch zugewiesenen Tags aus
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // holt sich den Tag-Namen über die Link-Tabelle
}

$tag = $explorer->table('tag')->get(1);
// Umgekehrte Richtung: gibt die Titel der Bücher mit diesem Tag aus
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // holt den Buchtitel
}

Der Explorer optimiert die SQL-Abfragen erneut in eine effiziente Form:

SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- IDs of the selected books
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- IDs of the tags found in book_tag

Abfragen über Bezugstabellen

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

Die Punktnotation (parent_table.column) wird für 1:N-Beziehungen aus der Perspektive der übergeordneten Tabelle verwendet:

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

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

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

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

Die Kolon-Notation wird für 1:N-Beziehungen aus der 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 für jeden Autor
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

Im obigen Beispiel mit Doppelpunkt-Notation (:book.title) wird die Fremdschlüsselspalte nicht explizit angegeben. Der Explorer erkennt automatisch die richtige Spalte anhand des Namens der übergeordneten Tabelle. In diesem Fall erfolgt die Verbindung über die Spalte book.author_id, da der Name der Quelltabelle author lautet. Wenn mehrere mögliche Verbindungen bestehen, löst Explorer die Ausnahme AmbiguousReferenceKeyException aus.

Die Verknüpfungsspalte kann explizit in Klammern angegeben werden:

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

Notationen können verkettet werden, um auf Daten über mehrere Tabellen hinweg zuzugreifen:

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

Bedingungen für JOIN verlängern

Die Methode joinWhere() fügt zusätzliche Bedingungen zu Tabellen-Joins in SQL nach dem Schlüsselwort ON hinzu.

Nehmen wir zum Beispiel 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 joinWhere() können Sie die gleichen Konstrukte wie in der Methode where() verwenden – Operatoren, Platzhalter, Wertefelder oder SQL-Ausdrücke.

Für komplexere Abfragen mit mehreren JOINs können Tabellen-Aliase definiert werden:

$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 (`buch_autor`.`geboren` < 1950)

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

Manuelles Erstellen von Explorer

Wenn Sie nicht den Nette-DI-Container verwenden, können Sie eine Instanz von Nette\Database\Explorer manuell erstellen:

use Nette\Database;

// $storage implementiert Nette\Caching\Storage, z. B.:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// Datenbankverbindung
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// verwaltet die Reflexion der Datenbankstruktur
$structure = new Database\Structure($connection, $storage);
// definiert Regeln für das Mapping von Tabellennamen, Spalten und Fremdschlüsseln
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);