Database Explorer

Praca z Explorerem zaczyna się od wywołania metody table() na obiekcie Nette\Database\Explorer (szczegóły ustawiania połączenia z bazą danych znajdziesz w rozdziale Połączenie i konfiguracja):

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

Metoda zwraca obiekt Selection, który reprezentuje zapytanie SQL. Do tego obiektu można doklejać kolejne metody filtrujące i sortujące wyniki. Zapytanie jest składane i wykonywane dopiero w momencie, gdy zażądamy danych, na przykład iterując przez foreach. Każdy wiersz reprezentuje obiekt ActiveRow:

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

Explorer zasadniczo upraszcza pracę z relacjami między tabelami. Poniższy przykład pokazuje, jak łatwo wypiszemy dane z powiązanych tabel (książki i ich autorzy). Zauważ, że nie trzeba pisać żadnych zapytań JOIN, Nette wygeneruje je za nas:

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

foreach ($books as $book) {
	echo 'Książka: ' . $book->title;
	echo 'Autor: ' . $book->author->name; // tworzy JOIN do tabeli 'author'
}

Nette Database Explorer optymalizuje zapytania tak, żeby były maksymalnie efektywne. Powyższy przykład wykonuje tylko dwa zapytania SELECT, niezależnie od tego, czy przetwarzamy 10, czy 10 000 książek.

Poza tym Explorer śledzi, które kolumny są w kodzie używane, i pobiera z bazy tylko je, oszczędzając dalej wydajność. To zachowanie jest w pełni automatyczne i adaptacyjne. Jeśli później zmodyfikujesz kod tak, żeby używał kolejnych kolumn, Explorer automatycznie dostosuje zapytania. Nie musisz nic ustawiać ani myśleć o tym, jakie kolumny będą potrzebne, zostaw to Nette.

Filtrowanie i sortowanie

Klasa Selection udostępnia metody do filtrowania i sortowania wyboru danych.

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

W tych metodach możesz też używać specjalnych zapisów do dostępu do danych z powiązanych tabel.

Escapowanie i identyfikatory

Metody automatycznie escapują parametry i cytują identyfikatory (nazwy tabel i kolumn), zapobiegając SQL injection. Żeby wszystko działało poprawnie, trzeba przestrzegać kilku zasad:

• Słowa kluczowe, nazwy funkcji, procedur itd. pisz wielkimi literami.
• Nazwy kolumn i tabel pisz małymi literami.
• Ciągi zawsze przekazuj przez parametry.

where('name = ' . $name);         // KRYTYCZNA LUKA: SQL injection
where('name LIKE "%search%"');    // ŹLE: komplikuje automatyczne cytowanie
where('name LIKE ?', '%search%'); // POPRAWNIE: wartość przekazana jako parametr

where('name like ?', $name);     // ŹLE: wygeneruje: `name` `like` ?
where('name LIKE ?', $name);     // POPRAWNIE: wygeneruje: `name` LIKE ?
where('LOWER(name) = ?', $value);// POPRAWNIE: LOWER(`name`) = ?

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

Filtruje wyniki za pomocą warunków WHERE. Jej siła tkwi w inteligentnej obsłudze różnych typów wartości i automatycznym wyborze odpowiednich operatorów SQL.

Podstawowe użycie:

$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 obsługiwać różnych przypadków szczególnych, Nette rozwiąże 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)
// Możesz też użyć zastępnika ? bez operatora:
$table->where('id ?', 1);        // WHERE `id` = 1

Metoda poprawnie obsługuje warunki negatywne i puste tablice:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- nic nie znajdzie
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- znajdzie wszystko
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- znajdzie wszystko
// $table->where('NOT id ?', $ids); // UWAGA: ta składnia nie jest wspierana

Jako parametr możesz przekazać także wynik zapytania z innej tabeli, 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 możesz przekazać także jako tablicę, której elementy łączone są operatorem AND:

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

W tablicy możesz używać par klucz ⇒ wartość, a Nette znów automatycznie wybierze poprawne operatory:

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

W tablicy możesz łączyć wyrażenia SQL z zastępnikami i wieloma parametrami. Nadaje się to do złożonych warunków z precyzyjnie określonymi operatorami:

// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // dwa parametry przekazujemy jako tablicę
]);

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

whereOr (array $parameters): static

Podobnie jak where() dodaje warunki, ale łączy je operatorem OR:

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

Można tu również używać 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 na klucz główny 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), przekaż 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ść, w jakiej zwracane są wiersze. Możesz sortować według jednej albo wielu kolumn, rosnąco albo malejąco, albo według własnego 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. Metody select() użyj wtedy, gdy potrzebujesz pobrać konkretne 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 przez AS są potem 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 pozwala ustawić offset:

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

Do stronicowania bardziej odpowiednia jest metoda page().

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

Ułatwia stronicowanie wyników. Przyjmuje numer strony (liczony od 1) i liczbę pozycji na stronie. Opcjonalnie możesz przekazać referencję do zmiennej, w której zostanie zapisana łączna liczba stron:

$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, numOfPages: $numOfPages);
echo "Łącznie stron: $numOfPages";

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

Grupuje wiersze według podanych kolumn (GROUP BY). Używa się jej zwykle w połączeniu z funkcjami agregującymi:

// Liczy 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żna jej używać w połączeniu z metodą group() i funkcjami agregującymi:

// Znajduje kategorie, które mają więcej niż 100 produktów
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Odczyt danych

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

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

foreach – iterowanie przez wszystkie wiersze

Najprostszym sposobem wykonania zapytania i pobrania wierszy jest iterowanie w pętli foreach. Automatycznie wykonuje ona zapytanie SQL.

$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key to wartość klucza głównego, $book to ActiveRow
	echo "$book->title ({$book->author->name})";
}

get ($key): ?ActiveRow

Wykonuje zapytanie SQL i zwraca wiersz według klucza głównego albo null, jeśli nie istnieje.

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

fetch(): ?ActiveRow

Zwraca bieżący wiersz i przesuwa wewnętrzny wskaźnik na kolejny. Jeśli nie ma już 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 używanej jako klucz tablicy, drugi argument nazwę kolumny używanej jako wartość:

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

Jeśli podany jest tylko pierwszy parametr, wartością będzie cały wiersz, czyli obiekt ActiveRow:

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

W przypadku zduplikowanych kluczy używana jest wartość z ostatniego wiersza. Przy użyciu null jako klucza tablica będzie indeksowana liczbowo od zera (wtedy nie dochodzi do kolizji):

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

fetchPairs (Closure $callback): array

Alternatywnie możesz jako parametr przekazać callback, który dla każdego wiersza zwróci albo pojedynczą wartość, albo parę klucz-wartość.

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

// Callback może zwrócić także tablicę z parą klucz i wartość:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['First Book' => 'John Novak', ...]

fetchAll(): array

Zwraca wszystkie wiersze jako tablicę asocjacyjną obiektów ActiveRow, gdzie kluczami są wartości kluczy głównych.

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

count(): int

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

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

Uwaga: count() z parametrem wykonuje w bazie danych funkcję agregującą COUNT, patrz niżej.

ActiveRow::toArray(): array

Konwertuje obiekt ActiveRow na tablicę asocjacyjną, gdzie kluczami są nazwy kolumn, a wartościami odpowiadające im dane.

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

Agregacja

Klasa Selection udostępnia metody do łatwego wykonywania funkcji agregujących (COUNT, SUM, MIN, MAX, AVG itd.).

count (string $expr): int

Wykonuje zapytanie SQL z funkcją COUNT i zwraca wynik. Metody używa się do ustalenia, ile wierszy odpowiada danemu warunkowi:

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

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

min (string $expr) i max(string $expr)

Metody min() i max() zwracają minimalną i maksymalną wartość w podanej kolumnie albo wyrażeniu:

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

sum (string $expr): mixed

Zwraca sumę wartości w podanej kolumnie albo 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

Pozwala wykonać dowolną funkcję agregującą.

// średnia cena produktów w kategorii
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

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

Jeśli potrzebujemy zagregować wyniki, które same są wynikiem jakiejś funkcji agregującej i grupowania (np. SUM(value) po zgrupowanych wierszach), jako drugi argument podajemy funkcję agregującą, która ma zostać zastosowana do tych wyników pośrednich:

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

W tym przykładzie najpierw wyliczamy łączną cenę produktów w każdej kategorii (SUM(price * stock) AS category_total) i grupujemy wyniki według category_id. Następnie za pomocą aggregation('SUM(category_total)', 'SUM') sumujemy te pośrednie sumy category_total. Drugi argument 'SUM' określa, że do wyników pośrednich ma zostać zastosowana funkcja SUM.

Insert, Update i Delete

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

Selection::insert (iterable $data)

Wstawia do tabeli nowe rekordy.

Wstawienie jednego rekordu:

Nowy rekord przekazujemy jako tablicę asocjacyjną albo obiekt iterowalny (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 wczytywany z bazy danych, żeby uwzględnić zmiany dokonane na poziomie bazy (triggery, domyślne wartości kolumn, wyliczenie kolumn auto-increment). Zapewnia to spójność danych, a obiekt zawsze zawiera aktualne dane z bazy. Jeśli tabela nie ma klucza głównego, nie ma identyfikowalnego wiersza i metoda zwraca null.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row jest instancją ActiveRow i zawiera kompletne dane wstawionego wiersza,
// włącznie z automatycznie wygenerowanym ID i ewentualnymi zmianami dokonanymi przez triggery
echo $row->id; // Wypisuje ID nowo wstawionego użytkownika
echo $row->created_at; // Wypisuje czas utworzenia, jeśli ustawia go trigger

Wstawienie wielu rekordów naraz:

Metoda insert() pozwala wstawić wiele rekordów jednym zapytaniem SQL. W takim 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 2

Jako parametr można przekazać także 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:

Jako wartości możemy przekazać także pliki, obiekty DateTime albo literały SQL:

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

Selection::update (iterable $data): int

Aktualizuje wiersze w tabeli według podanego filtra. Zwraca liczbę faktycznie zmienionych wierszy.

Zmieniane kolumny przekazujemy jako tablicę asocjacyjną albo obiekt iterowalny (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

Do zmiany wartości liczbowych możesz użyć operatorów += i -=:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // zwiększa wartość kolumny 'points' 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 według podanego filtra. Zwraca liczbę usuniętych wierszy.

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

Przy wywoływaniu update() albo delete() nie zapomnij użyć where(), żeby określić wiersze, które mają zostać zmienione albo 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. Przyjmuje iterowalne dane do aktualizacji (klucze to nazwy kolumn). Do zmiany wartości liczbowych możesz użyć operatorów += i -=:

Po wykonaniu aktualizacji ActiveRow jest automatycznie ponownie wczytywany z bazy danych, żeby uwzględnić zmiany dokonane na poziomie bazy (np. triggery). Metoda zwraca true tylko wtedy, gdy doszło do faktycznej zmiany danych.

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

Ta metoda aktualizuje tylko jeden konkretny wiersz w bazie danych. Do masowej aktualizacji wielu wierszy użyj metody Selection::update().

ActiveRow::delete(): int

Usuwa z bazy danych wiersz reprezentowany przez obiekt ActiveRow. Zwraca liczbę usuniętych wierszy, która powinna wynosić 1.

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

Ta metoda usuwa tylko jeden konkretny wiersz w bazie danych. Do masowego usuwania wielu wierszy użyj metody Selection::delete().

Relacje między tabelami

W bazach relacyjnych dane podzielone są na wiele tabel i powiązane ze sobą kluczami obcymi. Nette Database Explorer oferuje rewolucyjny sposób pracy z tymi relacjami: bez pisania zapytań JOIN i bez potrzeby czegokolwiek konfigurowania czy generowania.

Do zilustrowania pracy z relacjami użyjemy przykładowej bazy danych książek (znajdziesz ją na GitHubie). W bazie mamy tabele:

• author – pisarze 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 łącząca książki i tagi (kolumny book_id, tag_id)

W naszej przykładowej bazie książek znajdziemy kilka typów relacji (choć model jest uproszczony względem rzeczywistości):

• Jeden do wielu (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ć kontynuację.
• Wiele do wielu (M:N) – książka może mieć kilka tagów, a tag może być przypisany do kilku książek.

W tych relacjach zawsze istnieje tabela nadrzędna i tabela podrzędna. Na przykład w relacji między autorami a książkami tabela author jest nadrzędna, a tabela book podrzędna: możesz o tym myśleć tak, że książka zawsze “należy” do autora. Odzwierciedla to również struktura bazy danych: tabela podrzędna book zawiera klucz obcy author_id odwołujący się do tabeli nadrzędnej author.

Jeśli potrzebujemy wypisać książki wraz z nazwiskami ich autorów, mamy dwie możliwości. Albo pobrać dane jednym zapytaniem SQL z użyciem JOIN:

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

Albo pobrać dane w dwóch krokach, najpierw książki, potem ich autorów, a następnie złożyć je w PHP:

SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3);  -- ID autorów z wybranych książek

Drugie podejście jest w rzeczywistości efektywniejsze, choć może to zaskakiwać. Dane pobierane są tylko raz i można je lepiej wykorzystać w cache. Dokładnie tak działa Nette Database Explorer: wszystkim zajmuje się pod maską i oferuje Ci eleganckie API:

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

Dostęp do tabeli nadrzędnej

Dostęp do tabeli nadrzędnej jest prosty. Chodzi o relacje typu książka ma autora albo książka może mieć tłumacza. Powiązany rekord uzyskujemy przez właściwość obiektu ActiveRow, której nazwa odpowiada nazwie kolumny klucza obcego bez przyrostka _id:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // znajduje autora na podstawie kolumny author_id
echo $book->translator?->name; // znajduje tłumacza na podstawie kolumny translator_id

Przy dostępie do właściwości $book->author Explorer szuka w tabeli book kolumny, której nazwa zawiera ciąg author (czyli author_id). Na podstawie wartości w tej kolumnie wczytuje odpowiadający rekord z tabeli author i zwraca go jako ActiveRow. Podobnie $book->translator używa kolumny translator_id. Ponieważ kolumna translator_id może zawierać null, używamy w kodzie operatora nullsafe ?->.

Alternatywne podejście oferuje metoda ref(), która przyjmuje dwa argumenty, nazwę tabeli docelowej i nazwę kolumny łączącej, i zwraca instancję ActiveRow albo null:

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

Metoda ref() przydaje się wtedy, gdy nie można użyć dostępu przez właściwość, na przykład dlatego, że tabela zawiera kolumnę o tej samej nazwie (czyli author). W pozostałych przypadkach zalecane jest użycie dostępu przez właściwość ze względu na lepszą czytelność.

Explorer automatycznie optymalizuje zapytania do bazy danych. Gdy przechodzimy książki w pętli i sięgamy po ich powiązane rekordy (autorów, tłumaczy), Explorer nie generuje zapytania dla każdej książki osobno. Zamiast tego wykonuje tylko jedno zapytanie SELECT dla każdego typu relacji, co znacząco zmniejsza obciążenie bazy. 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 te trzy błyskawiczne zapytania do bazy danych:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- ID z kolumny author_id wybranych książek
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- ID z kolumny translator_id wybranych książek

Logikę szukania kolumny łączącej określa implementacja Conventions. Zalecamy użycie DiscoveredConventions, które analizuje klucze obce i pozwala łatwo pracować z istniejącymi relacjami między tabelami.

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 albo jakie książki przetłumaczył ten tłumacz. Do tego typu zapytań służy metoda related(), która zwraca Selection z powiązanymi rekordami. Spójrzmy na przykład:

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

// Wypisuje wszystkie książki autora
foreach ($author->related('book.author_id') as $book) {
	echo "Napisał: $book->title";
}

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

Metoda related() przyjmuje opis połączenia jako jeden argument z notacją kropkową albo jako dwa osobne argumenty:

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

Explorer potrafi automatycznie wykryć właściwą kolumnę łączącą na podstawie nazwy tabeli nadrzędnej. W tym przypadku łączy przez kolumnę book.author_id, bo nazwa tabeli źródłowej to author:

$author->related('book');  // używa book.author_id

Jeśli istnieje wiele możliwych połączeń, Explorer rzuci AmbiguousReferenceKeyException.

Metody related() możemy oczywiście używać przy przechodzeniu wielu rekordów w pętli, a Explorer i w tym przypadku automatycznie zoptymalizuje zapytania:

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

Ten kod wygeneruje tylko dwa błyskawiczne zapytania SQL:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- ID wybranych autorów

Relacja wiele do wielu

Dla relacji wiele do wielu (M:N) potrzebna jest tabela łącząca (w naszym przypadku book_tag) zawierająca dwie kolumny kluczy obcych (book_id, tag_id). Każda z tych kolumn odwołuje się do klucza głównego jednej z powiązanych tabel. Żeby pobrać powiązane dane, najpierw uzyskujemy rekordy z tabeli łączącej za pomocą related('book_tag'), a potem przechodzimy do danych docelowych:

$book = $explorer->table('book')->get(1);
// wypisuje nazwy tagów przypisanych do książki
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name;  // wypisuje nazwę tagu przez tabelę łączącą
}

$tag = $explorer->table('tag')->get(1);
// albo odwrotnie: wypisuje nazwy książek oznaczonych tym tagiem
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // wypisuje tytuł książki
}

Explorer znów optymalizuje zapytania SQL do efektywnej postaci:

SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- ID wybranych książek
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- ID tagów znalezionych w book_tag

Zapytania przez powiązane tabele

W metodach where(), select(), order() i group() możesz używać specjalnych zapisów do dostępu do kolumn z innych tabel. Explorer automatycznie utworzy potrzebne JOIN-y.

Notacja kropkowa (tabela_nadrzedna.kolumna) używana jest dla relacji 1:N z perspektywy tabeli podrzędnej:

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

// Znajduje książki, których autor ma nazwisko zaczynające się od 'Jon'
$books->where('author.name LIKE ?', 'Jon%');

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

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

Notacja z dwukropkiem (:tabela_podrzedna.kolumna) używana jest 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%');

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

W powyższym przykładzie z notacją z dwukropkiem (:book.title) nie jest podana kolumna klucza obcego. Explorer automatycznie wykrywa właściwą kolumnę na podstawie nazwy tabeli nadrzędnej. W tym przypadku łączy przez kolumnę book.author_id, bo nazwa tabeli źródłowej to author. Jeśli istnieje wiele możliwych połączeń, Explorer rzuci AmbiguousReferenceKeyException.

Kolumnę łączącą można jawnie podać w nawiasach:

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

Zapisy można łączyć w łańcuch, żeby sięgać po dane w wielu tabelach:

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

Rozszerzanie warunków dla JOIN

Metoda joinWhere() rozszerza warunki podawane przy łączeniu tabel w SQL po słowie kluczowym ON.

Powiedzmy, że chcemy znaleźć książki przetłumaczone przez konkretnego 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żesz używać tych samych konstrukcji co w metodzie where(): operatorów, zastępników, tablic wartości czy wyrażeń SQL.

Dla bardziej złożonych zapytań z wieloma JOIN-ami możesz 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)

Zwróć uwagę, że podczas gdy metoda where() dodaje warunki do klauzuli WHERE, metoda joinWhere() rozszerza warunki w klauzuli ON przy łączeniu tabel.