Eksplorator baz danych

Praca z Explorerem rozpoczyna się od wywołania metody table() na obiekcie Nette\Database\Explorer (informacje na temat tworzenia połączeń i konfiguracji można znaleźć na dedykowanej stronie):

$books = $explorer->table('book'); // "book" to nazwa tabeli

Metoda ta zwraca obiekt Selection, który reprezentuje zapytanie SQL. Do tego obiektu można podłączyć dodatkowe metody filtrowania i sortowania wyników. Zapytanie jest składane i wykonywane tylko wtedy, gdy dane są wymagane, na przykład przez iterację z foreach. Każdy wiersz jest reprezentowany przez obiekt ActiveRow:

foreach ($books as $book) {
	echo $book->title;        // wyprowadza kolumnę "title
	echo $book->author_id;    // wyprowadza kolumnę "author_id
}

Explorer znacznie upraszcza pracę z relacjami tabel. Poniższy przykład pokazuje, jak łatwo możemy wyprowadzić dane z powiązanych tabel (książki i ich autorzy). Zauważ, że nie trzeba pisać żadnych zapytań JOIN; Nette generuje je dla nas:

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

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // tworzy JOIN do tabeli "author
}

Nette Database Explorer optymalizuje zapytania pod kątem maksymalnej wydajności. Powyższy przykład wykonuje tylko dwa zapytania SELECT, niezależnie od tego, czy przetwarzamy 10 czy 10 000 książek.

Dodatkowo, Explorer śledzi, które kolumny są używane w kodzie i pobiera tylko te z bazy danych, oszczędzając dalszą wydajność. Zachowanie to jest w pełni automatyczne i adaptacyjne. Jeśli później zmodyfikujesz kod, aby użyć dodatkowych kolumn, Explorer automatycznie dostosuje zapytania. Nie musisz niczego konfigurować ani zastanawiać się, które kolumny będą potrzebne – pozostaw to Nette.

Filtrowanie i sortowanie

Klasa Selection udostępnia metody filtrowania i sortowania danych.

Metody można łączyć w łańcuchy (tzw. płynny interfejs): $table->where(...)->order(...)->limit(...).

Metody te pozwalają również na użycie specjalnych notacji w celu uzyskania dostępu do danych z powiązanych tabel.

Ucieczka i identyfikatory

Metody automatycznie unikają parametrów i cytują identyfikatory (nazwy tabel i kolumn), zapobiegając wstrzyknięciu SQL. Aby zapewnić prawidłowe działanie, należy przestrzegać kilku zasad:

• Słowa kluczowe, nazwy funkcji, procedur itp. należy pisać wielkimi literami.
• Nazwy kolumn i tabel należy pisać małymi literami.
• Zawsze przekazuj ciągi znaków za pomocą parametrów.

where('name = ' . $name);         // **DISASTER**: podatny na wstrzyknięcie kodu SQL
where('name LIKE "%search%"');    // **WRONG**: komplikuje automatyczne cytowanie
where('name LIKE ?', '%search%'); // **CORRECT**: wartość przekazywana jako parametr

where('name like ?', $name);     // **WRONG**: generuje: `name` `like` ?
where('name LIKE ?', $name);     // **KOREKTA**: generuje: `name` LIKE ?
where('LOWER(name) = ?', $value);// **PRAWDA**: LOWER(`name`) = ?

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

Filtruje wyniki za pomocą warunków WHERE. Jego siła polega na inteligentnej obsłudze różnych typów wartości i automatycznym wybieraniu operatorów SQL.

Podstawowe zastosowanie:

$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'

Dzięki automatycznemu wykrywaniu odpowiednich operatorów nie musisz zajmować się specjalnymi przypadkami – Nette obsługuje je za Ciebie:

$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)
// Symbol zastępczy ? może być użyty bez operatora:
$table->where('id ?', 1);        // WHERE `id` = 1

Metoda obsługuje również poprawnie warunki ujemne i puste tablice:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- nie znajduje niczego
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- znajduje wszystko
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- znajduje wszystko
// $table->where('NOT id ?', $ids); // UWAGA: Ta składnia nie jest obsługiwana

Można również przekazać wynik innego zapytania do tabeli jako parametr, tworząc podzapytanie:

// 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'));

Warunki mogą być również przekazywane jako tablica, z elementami połączonymi za pomocą AND:

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

W tablicy można użyć par klucz-wartość, a Nette ponownie automatycznie wybierze odpowiednie operatory:

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

Możemy również mieszać wyrażenia SQL z symbolami zastępczymi i wieloma parametrami. Jest to przydatne w przypadku złożonych warunków z precyzyjnie zdefiniowanymi operatorami:

// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // dwa parametry są przekazywane jako tablica
]);

Wielokrotne wywołania where() automatycznie łączą warunki za pomocą AND.

whereOr (array $parameters): static

Podobne do where(), ale łączy warunki za pomocą OR:

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

Można również użyć bardziej złożonych wyrażeń:

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

wherePrimary (mixed $key): static

Dodaje warunek dla klucza głównego tabeli:

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

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

Jeśli tabela ma złożony klucz główny (np. foo_id, bar_id), przekazujemy go jako tablicę:

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

Określa kolejność zwracania wierszy. Można sortować według jednej lub kilku kolumn, w kolejności rosnącej lub malejącej, lub według niestandardowego wyrażenia:

$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

Określa kolumny, które mają zostać zwrócone z bazy danych. Domyślnie Nette Database Explorer zwraca tylko te kolumny, które są faktycznie używane w kodzie. Użyj metody select(), gdy chcesz pobrać określone wyrażenia:

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

Aliasy zdefiniowane za pomocą AS są następnie dostępne jako właściwości obiektu ActiveRow:

foreach ($table as $row) {
	echo $row->formatted_date;   // dostęp do aliasu
}

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

Ogranicza liczbę zwracanych wierszy (LIMIT) i opcjonalnie ustawia offset:

$table->limit(10);        // LIMIT 10 (zwraca pierwsze 10 wierszy)
$table->limit(10, 20);    // LIMIT 10 OFFSET 20

W przypadku paginacji bardziej odpowiednie jest użycie metody page().

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

Upraszcza paginację wyników. Akceptuje numer strony (zaczynając od 1) i liczbę elementów na stronie. Opcjonalnie można przekazać odwołanie do zmiennej, w której przechowywana będzie całkowita liczba stron:

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

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

Grupuje wiersze według określonych kolumn (GROUP BY). Zazwyczaj jest używana w połączeniu z funkcjami agregującymi:

// Zlicza liczbę produktów w każdej kategorii
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');

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

Ustawia warunek filtrowania zgrupowanych wierszy (HAVING). Może być używany w połączeniu z metodą group() i funkcjami agregującymi:

// Znajduje kategorie z ponad 100 produktami
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Odczytywanie danych

Do odczytu danych z bazy danych dostępnych jest kilka przydatnych metod:

Obiekt ActiveRow jest tylko do odczytu. Oznacza to, że nie można zmieniać wartości jego właściwości. Ograniczenie to zapewnia spójność danych i zapobiega nieoczekiwanym efektom ubocznym. Dane są pobierane z bazy danych, a wszelkie zmiany powinny być wprowadzane w sposób jawny i kontrolowany.

foreach – Iteracja przez wszystkie wiersze

Najprostszym sposobem na wykonanie zapytania i pobranie wierszy jest iteracja za pomocą pętli foreach. Powoduje ona automatyczne wykonanie zapytania SQL.

$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key = klucz główny, $book = ActiveRow
	echo "$book->title ({$book->author->name})";
}

get ($key): ?ActiveRow

Wykonuje zapytanie SQL i zwraca wiersz według klucza podstawowego lub null jeśli nie istnieje.

$book = $explorer->table('book')->get(123); // zwraca ActiveRow z ID 123 lub null
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Zwraca jeden wiersz i przesuwa wewnętrzny wskaźnik do następnego wiersza. Jeśli nie ma więcej wierszy, zwraca null.

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

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

Zwraca wyniki jako tablicę asocjacyjną. Pierwszy argument określa nazwę kolumny, która ma być użyta jako klucz tablicy, a drugi argument określa nazwę kolumny, która ma być użyta jako wartość:

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

Jeśli podany zostanie tylko pierwszy parametr, cały wiersz zostanie użyty jako wartość, reprezentowana jako obiekt ActiveRow:

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

Jeśli jako klucz podano null, tablica będzie indeksowana numerycznie począwszy od zera:

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

fetchPairs (Closure $callback): array

Alternatywnie można przekazać wywołanie zwrotne jako parametr. Wywołanie zwrotne zostanie zastosowane do każdego wiersza w celu zwrócenia pojedynczej wartości lub pary klucz-wartość.

$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// [1 => "Pierwsza książka (Jan Novak)", ...].

// Wywołanie zwrotne może również zwrócić tablicę zawierającą parę klucz-wartość:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['First Book' => 'Jan Novak', ...].

fetchAll(): array

Zwraca wszystkie wiersze jako tablicę asocjacyjną obiektów ActiveRow, gdzie klucze są wartościami klucza głównego.

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

count(): int

Metoda count() bez parametrów zwraca liczbę wierszy w obiekcie Selection:

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

Uwaga: count() z parametrem wykonuje funkcję agregacji COUNT w bazie danych, jak opisano poniżej.

ActiveRow::toArray(): array

Konwertuje obiekt ActiveRow na tablicę asocjacyjną, w której kluczami są nazwy kolumn, a wartościami odpowiednie dane.

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

Agregacja

Klasa Selection udostępnia metody łatwego wykonywania funkcji agregacji (COUNT, SUM, MIN, MAX, AVG itp.).

count (string $expr): int

Wykonuje zapytanie SQL z funkcją COUNT i zwraca wynik. Metoda ta służy do określenia, ile wierszy spełnia określony warunek:

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

Uwaga: count() bez parametru po prostu zwraca liczbę wierszy w obiekcie Selection.

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

Metody min() i max() zwracają minimalne i maksymalne wartości w określonej kolumnie lub wyrażeniu:

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

sum (string $expr): int

Zwraca sumę wartości w określonej kolumnie lub wyrażeniu:

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

Umożliwia wykonanie dowolnej funkcji agregacji.

// Oblicza średnią cenę produktów w kategorii
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// Łączy tagi produktów w jeden ciąg znaków
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Jeśli musimy zagregować wyniki, które same są wynikiem agregacji i grupowania (np. SUM(value) nad zgrupowanymi wierszami), określamy funkcję agregacji, która ma zostać zastosowana do tych wyników pośrednich jako drugi argument:

// Oblicza łączną cenę produktów w magazynie dla każdej kategorii, a następnie sumuje te ceny.
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

W tym przykładzie najpierw obliczamy całkowitą cenę produktów w każdej kategorii (SUM(price * stock) AS category_total) i grupujemy wyniki według category_id. Następnie używamy aggregation('SUM(category_total)', 'SUM') do zsumowania tych sum częściowych. Drugi argument 'SUM' określa funkcję agregacji, która ma być zastosowana do wyników pośrednich.

Wstawianie, aktualizacja i usuwanie

Nette Database Explorer upraszcza wstawianie, aktualizowanie i usuwanie danych. Wszystkie wymienione metody rzucają Nette\Database\DriverException w przypadku błędu.

Selection::insert (iterable $data): static

Wstawia nowe rekordy do tabeli.

Wstawianie pojedynczego rekordu:.

Nowy rekord jest przekazywany jako tablica asocjacyjna lub obiekt iterowalny (taki jak ArrayHash używany w formularzach), gdzie klucze odpowiadają nazwom kolumn w tabeli.

Jeśli tabela ma zdefiniowany klucz główny, metoda zwraca obiekt ActiveRow, który jest ponownie ładowany z bazy danych w celu odzwierciedlenia wszelkich zmian wprowadzonych na poziomie bazy danych (np. wyzwalaczy, domyślnych wartości kolumn lub obliczeń automatycznego zwiększania). Zapewnia to spójność danych, a obiekt zawsze zawiera aktualne dane bazy danych. Jeśli klucz główny nie jest jawnie zdefiniowany, metoda zwraca dane wejściowe jako tablicę.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row jest instancją ActiveRow zawierającą pełne dane wstawionego wiersza,
// w tym automatycznie wygenerowany identyfikator i wszelkie zmiany wprowadzone przez wyzwalacze.
echo $row->id;          // Wyświetla identyfikator nowo wstawionego użytkownika
echo $row->created_at;  // Wyświetla czas utworzenia, jeśli został ustawiony przez wyzwalacz

Wstawianie wielu rekordów jednocześnie:.

Metoda insert() umożliwia wstawianie wielu rekordów za pomocą jednego zapytania SQL. W tym przypadku zwraca liczbę wstawionych wierszy.

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

Jako parametr można również przekazać obiekt Selection z wyborem danych.

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

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

Wstawianie wartości specjalnych:.

Wartości mogą zawierać pliki, obiekty DateTime lub literały SQL:

$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // konwertuje do formatu bazy danych
	'avatar' => fopen('image.jpg', 'rb'),   // wstawia zawartość pliku binarnego
	'uuid' => $explorer::literal('UUID()'), // wywołuje funkcję UUID()
]);

Selection::update (iterable $data): int

Aktualizuje wiersze w tabeli na podstawie określonego filtra. Zwraca liczbę faktycznie zmodyfikowanych wierszy.

Kolumny do aktualizacji są przekazywane jako tablica asocjacyjna lub obiekt iterowalny (taki jak ArrayHash używany w formularzach), gdzie klucze odpowiadają nazwom kolumn w tabeli:

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

Aby zmienić wartości liczbowe, można użyć operatorów += i -=:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // zwiększa wartość kolumny "punkty" o 1
		'coins-=' => 1,   // zmniejsza wartość kolumny "coins" o 1
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10

Selection::delete(): int

Usuwa wiersze z tabeli na podstawie określonego filtra. Zwraca liczbę usuniętych wierszy.

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

Podczas wywoływania update() lub delete() należy użyć where() w celu określenia wierszy, które mają zostać zaktualizowane lub usunięte. Jeśli where() nie zostanie użyte, operacja zostanie wykonana na całej tabeli!

ActiveRow::update (iterable $data): bool

Aktualizuje dane w wierszu bazy danych reprezentowanym przez obiekt ActiveRow. Jako parametr przyjmuje dane iterowalne, gdzie kluczami są nazwy kolumn. Aby zmienić wartości liczbowe, można użyć operatorów += i -=:

Po wykonaniu aktualizacji obiekt ActiveRow jest automatycznie przeładowywany z bazy danych, aby odzwierciedlić wszelkie zmiany wprowadzone na poziomie bazy danych (np. wyzwalacze). Metoda zwraca true tylko wtedy, gdy nastąpiła rzeczywista zmiana danych.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // zwiększa liczbę wyświetleń
]);
echo $article->views; // Wyświetla bieżącą liczbę wyświetleń

Ta metoda aktualizuje tylko jeden określony wiersz w bazie danych. W przypadku zbiorczych aktualizacji wielu wierszy należy użyć metody Selection::update().

ActiveRow::delete()

Usuwa wiersz z bazy danych reprezentowany przez obiekt ActiveRow.

$book = $explorer->table('book')->get(1);
$book->delete(); // Usuwa książkę o identyfikatorze 1

Metoda ta usuwa tylko jeden określony wiersz w bazie danych. Do masowego usuwania wielu wierszy należy użyć metody Selection::delete().

Relacje między tabelami

W relacyjnych bazach danych dane są podzielone na wiele tabel i połączone za pomocą kluczy obcych. Nette Database Explorer oferuje rewolucyjny sposób pracy z tymi relacjami – bez pisania zapytań JOIN lub wymagających jakiejkolwiek konfiguracji lub generowania encji.

Do demonstracji użyjemy przykładowej bazy danych(dostępnej na GitHub). Baza danych zawiera następujące tabele:

• author – autorzy i tłumacze (kolumny id, name, web, born)
• book – książki (kolumny id, author_id, translator_id, title, sequel_id)
• tag – tagi (kolumny id, name)
• book_tag – tabela powiązań między książkami i tagami (kolumny book_id, tag_id)

Struktura bazy danych

W tym przykładzie bazy danych książek znajdujemy kilka rodzajów relacji (uproszczonych w porównaniu do rzeczywistości):

• One-to-many (1:N) – Każda książka ma jednego autora; autor może napisać wiele książek.
• Zero-do-wielu (0:N) – Książka może mieć tłumacza; tłumacz może przetłumaczyć wiele książek.
• Zero-do-jednego (0:1) – książka może mieć sequel.
• Many-to-many (M:N) – książka może mieć kilka tagów, a tag może być przypisany do wielu książek.

W tych relacjach zawsze istnieje tabela nadrzędna i tabela podrzędna. Na przykład, w relacji między autorami i książkami, tabela author jest rodzicem, a tabela book jest dzieckiem – można o tym myśleć jako o książce zawsze “należącej” do autora. Znajduje to również odzwierciedlenie w strukturze bazy danych: tabela podrzędna book zawiera klucz obcy author_id, który odwołuje się do tabeli nadrzędnej author.

Jeśli chcemy wyświetlić książki wraz z nazwiskami ich autorów, mamy dwie opcje. Albo pobierzemy dane za pomocą pojedynczego zapytania SQL z JOIN:

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

Albo pobieramy dane w dwóch krokach – najpierw książki, potem ich autorów – i łączymy je w PHP:

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

Drugie podejście jest, co zaskakujące, bardziej wydajne. Dane są pobierane tylko raz i mogą być lepiej wykorzystane w pamięci podręcznej. Dokładnie tak działa Nette Database Explorer – obsługuje wszystko pod maską i zapewnia czyste API:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author jest rekordem z tabeli "author".
	echo 'translated by: ' . $book->translator?->name;
}

Dostęp do tabeli nadrzędnej

Dostęp do tabeli nadrzędnej jest prosty. Są to relacje typu książka ma autora lub książka może mieć tłumacza. Dostęp do powiązanego rekordu można uzyskać za pośrednictwem właściwości obiektu ActiveRow – nazwa właściwości odpowiada nazwie kolumny klucza obcego bez przyrostka id:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // wyszukuje autora za pomocą kolumny "author_id
echo $book->translator?->name; // wyszukuje tłumacza za pomocą kolumny "translator_id

Podczas uzyskiwania dostępu do właściwości $book->author Explorer szuka kolumny w tabeli book, która zawiera ciąg author (tj. author_id). Na podstawie wartości w tej kolumnie pobiera odpowiedni rekord z tabeli author i zwraca go jako obiekt ActiveRow. Podobnie, $book->translator używa kolumny translator_id. Ponieważ kolumna translator_id może zawierać null, używany jest operator ?->.

Alternatywne podejście zapewnia metoda ref(), która przyjmuje dwa argumenty – nazwę tabeli docelowej i kolumnę łączącą – i zwraca instancję ActiveRow lub null:

echo $book->ref('author', 'author_id')->name;      // link do autora
echo $book->ref('author', 'translator_id')->name;  // link do tłumacza

Metoda ref() jest przydatna, jeśli nie można użyć dostępu opartego na właściwościach, na przykład, gdy tabela zawiera kolumnę o tej samej nazwie co właściwość (author). W innych przypadkach korzystanie z dostępu opartego na właściwościach jest zalecane dla lepszej czytelności.

Explorer automatycznie optymalizuje zapytania do bazy danych. Podczas przechodzenia przez książki i uzyskiwania dostępu do powiązanych z nimi rekordów (autorzy, tłumacze), Explorer nie generuje zapytania dla każdej książki z osobna. Zamiast tego wykonuje tylko jedno zapytanie SELECT dla każdego typu relacji, znacznie zmniejszając obciążenie bazy danych. Na przykład:

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

Ten kod wykona tylko trzy zoptymalizowane zapytania do bazy danych:

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

Logika identyfikacji kolumny łączącej jest definiowana przez implementację Conventions. Zalecamy użycie DiscoveredConventions, która analizuje klucze obce i pozwala na płynną pracę z istniejącymi relacjami tabel.

Dostęp do tabeli podrzędnej

Dostęp do tabeli podrzędnej działa w przeciwnym kierunku. Teraz pytamy jakie książki napisał ten autor lub jakie książki przetłumaczył ten tłumacz. Do tego typu zapytań używamy metody related(), która zwraca obiekt Selection z powiązanymi rekordami. Oto przykład:

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

// Wyświetla wszystkie książki napisane przez autora
foreach ($author->related('book.author_id') as $book) {
	echo "Wrote: $book->title";
}

// Wyświetla wszystkie książki przetłumaczone przez autora
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}

Metoda related() akceptuje opis relacji jako pojedynczy argument przy użyciu notacji kropkowej lub jako dwa oddzielne argumenty:

$author->related('book.translator_id');    // pojedynczy argument
$author->related('book', 'translator_id'); // dwa argumenty

Explorer może automatycznie wykryć prawidłową kolumnę łączącą na podstawie nazwy tabeli nadrzędnej. W tym przypadku łączy przez kolumnę book.author_id, ponieważ nazwa tabeli źródłowej to author:

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

Jeśli istnieje wiele możliwych połączeń, Explorer zgłosi wyjątek AmbiguousReferenceKeyException.

Oczywiście możemy również użyć metody related() podczas iteracji przez wiele rekordów w pętli, a Explorer automatycznie zoptymalizuje zapytania również w tym przypadku:

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

Ten kod generuje tylko dwa wydajne zapytania SQL:

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

Relacja wiele do wielu

W przypadku relacji wiele do wielu (M:N) wymagana jest tabela łącząca (w naszym przypadku book_tag). Tabela ta zawiera dwie kolumny klucza obcego (book_id, tag_id). Każda kolumna odwołuje się do klucza głównego jednej z połączonych tabel. Aby pobrać powiązane dane, najpierw pobieramy rekordy z tabeli łączącej za pomocą related('book_tag'), a następnie przechodzimy do danych docelowych:

$book = $explorer->table('book')->get(1);
// Wyświetla nazwy tagów przypisanych do książki
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // pobiera nazwę tagu poprzez tabelę linków
}

$tag = $explorer->table('tag')->get(1);
// W przeciwnym kierunku: wyświetla tytuły książek z tym tagiem
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // pobiera tytuł książki
}

Explorer ponownie optymalizuje zapytania SQL do wydajnej postaci:

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

Zapytania poprzez powiązane tabele

W metodach where(), select(), order() i group() można użyć specjalnych notacji, aby uzyskać dostęp do kolumn z innych tabel. Explorer automatycznie tworzy wymagane JOINy.

Notacja Dot (parent_table.column) jest używana dla relacji 1:N widzianych z perspektywy tabeli nadrzędnej:

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

// Wyszukuje książki, których nazwiska autorów zaczynają się na "Jon
$books->where('author.name LIKE ?', 'Jon%');

// Sortuje książki według nazwiska autora malejąco
$books->order('author.name DESC');

// Wyświetla tytuł książki i nazwisko autora
$books->select('book.title, author.name');

Notacja dwukropkowa jest używana dla relacji 1:N z perspektywy tabeli nadrzędnej:

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

// Znajduje autorów, którzy napisali książkę z "PHP" w tytule
$authors->where(':book.title LIKE ?', '%PHP%');

// Zlicza liczbę książek każdego autora
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

W powyższym przykładzie z notacją dwukropkową (:book.title), kolumna klucza obcego nie jest wyraźnie określona. Explorer automatycznie wykrywa prawidłową kolumnę na podstawie nazwy tabeli nadrzędnej. W tym przypadku łączy się przez kolumnę book.author_id, ponieważ nazwa tabeli źródłowej to author. Jeśli istnieje wiele możliwych połączeń, Explorer rzuca wyjątek AmbiguousReferenceKeyException.

Kolumna łącząca może być jawnie określona w nawiasach:

// Znajduje autorów, którzy przetłumaczyli książkę z "PHP" w tytule
$authors->where(':book(translator).title LIKE ?', '%PHP%');

Notacje można łączyć w łańcuchy, aby uzyskać dostęp do danych w wielu tabelach:

// Znajduje autorów książek oznaczonych tagiem "PHP
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

Warunki rozszerzające dla JOIN

Metoda joinWhere() dodaje dodatkowe warunki do złączeń tabel w SQL po słowie kluczowym ON.

Załóżmy na przykład, że chcemy znaleźć książki przetłumaczone przez określonego tłumacza:

// Znajduje książki przetłumaczone przez tłumacza o imieniu "David
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')

W warunku joinWhere() można użyć tych samych konstrukcji, co w metodzie where() – operatorów, symboli zastępczych, tablic wartości lub wyrażeń SQL.

W przypadku bardziej złożonych zapytań z wieloma JOIN można zdefiniować aliasy tabel:

$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)

Należy pamiętać, że podczas gdy metoda where() dodaje warunki do klauzuli WHERE, metoda joinWhere() rozszerza warunki w klauzuli ON podczas łączenia tabel.

Ręczne tworzenie eksploratora

Jeśli nie korzystasz z kontenera Nette DI, możesz utworzyć instancję Nette\Database\Explorer ręcznie:

use Nette\Database;

// $storage implementuje Nette\Caching\Storage, np:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// połączenie z bazą danych
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// zarządza odzwierciedleniem struktury bazy danych
$structure = new Database\Structure($connection, $storage);
// definiuje reguły mapowania nazw tabel, kolumn i kluczy obcych
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);