Провідник бази даних

Nette Database Explorer – це розширення низькорівневого шару Nette Database Core, яке додає зручний об'єктно-орієнтований підхід до управління базами даних.

Робота з Explorer починається з виклику методу table() на об'єкті Nette\Database\Explorer (як його отримати, описано тут):

$books = $explorer->table('book'); // 'book' - це ім'я таблиці

Метод повертає об'єкт Selection, який представляє собою SQL-запит. До цього об'єкта можна підключити додаткові методи для фільтрації та сортування результатів. Запит збирається і виконується тільки при запиті даних, наприклад, при ітерації за допомогою foreach. Кожен рядок представлений об'єктом ActiveRow:

foreach ($books as $book) {
	echo $book->title;        // виводить стовпець 'title'
	echo $book->author_id;    // виводить колонку 'author_id'
}

Explorer значно спрощує роботу з зв'язками таблиць. Наступний приклад показує, як легко ми можемо вивести дані з пов'язаних таблиць (книги та їх автори). Зверніть увагу, що ніяких JOIN-запитів писати не потрібно – Nette генерує їх за нас:

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

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // створює JOIN з таблицею 'author'
}

Nette Database Explorer оптимізує запити для досягнення максимальної ефективності. У наведеному вище прикладі виконується всього два запити SELECT, незалежно від того, чи обробляємо ми 10 або 10 000 книг.

Крім того, Explorer відстежує, які стовпці використовуються в коді, і витягує з бази даних тільки їх, що ще більше знижує навантаження. Ця поведінка повністю автоматична і адаптивна. Якщо згодом ви зміните код, щоб використовувати додаткові стовпці, Explorer автоматично скоригує запити. Вам не потрібно нічого налаштовувати або думати про те, які стовпці будуть потрібні – надайте це Nette.

Фільтрація та сортування

Клас Selection надає методи для фільтрації та сортування даних.

Методи можуть бути об'єднані в ланцюжок (так званий fluent-інтерфейс): $table->where(...)->order(...)->limit(...).

Ці методи також дозволяють використовувати спеціальні позначення для доступу до даних з пов'язаних таблиць.

Екранування та ідентифікатори

Методи автоматично екранують параметри і беруть в лапки ідентифікатори (імена таблиць і стовпців), запобігаючи SQL-ін'єкції. Щоб забезпечити правильну роботу, необхідно дотримуватися кількох правил:

• Записуйте ключові слова, імена функцій, процедур і т. д. в верхньому регістрі.
• Імена стовпців і таблиць пишіть в нижньому регістрі.
• Завжди передавайте рядки за допомогою параметрів.

where('name = ' . $name);         // **DISASTER**: вразливість до SQL-ін'єкцій
where('name LIKE "%search%"');    // **WRONG**: ускладнює автоматичне цитування
where('name LIKE ?', '%search%'); // **КОРЕКТНО**: значення передається як параметр

where('name like ?', $name);     // **WRONG**: генерує: `name` `like` ?
where('name LIKE ?', $name);     // **CORRECT**: генерує: `name` LIKE ?
where('LOWER(name) = ?', $value);// **CORRECT**: LOWER(`name`) = ?

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

Фільтрує результати за допомогою умов WHERE. Його сильною стороною є інтелектуальна обробка різних типів значень і автоматичний вибір операторів SQL.

Базове використання:

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

Завдяки автоматичному визначенню відповідних операторів вам не потрібно розбиратися з особливими випадками – Nette зробить це за вас:

$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)
// Заповнювач ? може використовуватися без оператора:
$table->where('id ?', 1);        // WHERE `id` = 1

Метод також коректно обробляє негативні умови і порожні масиви:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- не знаходить нічого
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- знаходить все
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- знаходить все
// $table->where('NOT id ?', $ids); // УВАГА: Цей синтаксис не підтримується

Ви також можете передати результат іншого запиту до таблиці як параметр, створивши підзапит:

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

Умови також можна передати у вигляді масиву, об'єднавши елементи за допомогою AND:

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

У масиві можна використовувати пари ключ-значення, і Nette знову автоматично вибере потрібні оператори:

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

Ми також можемо змішувати SQL-вирази із заповнювачами і декількома параметрами. Це корисно для складних умов з точно визначеними операторами:

// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // два параметри передаються у вигляді масиву
]);

Кілька викликів where() автоматично об'єднують умови за допомогою AND.

whereOr (array $parameters): static

Аналогічно where(), але об'єднує умови за допомогою OR:

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

Можна використовувати і більш складні вирази:

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

wherePrimary (mixed $key): static

Додає умову для первинного ключа таблиці:

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

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

Якщо таблиця має складений первинний ключ (наприклад, foo_id, bar_id), ми передаємо його у вигляді масиву:

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

Вказує порядок, в якому повертаються рядки. Ви можете сортувати по одному або декільком стовпцям, за зростанням або спаданням, або за користувацьким виразом:

$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

Вказує стовпці, які будуть повернуті з бази даних. За замовчуванням Nette Database Explorer повертає тільки ті стовпці, які дійсно використовуються в коді. Використовуйте метод select(), якщо вам потрібно отримати конкретні вирази:

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

Псевдоніми, визначені за допомогою AS, стають доступні як властивості об'єкта ActiveRow:

foreach ($table as $row) {
	echo $row->formatted_date;   // доступ до псевдоніму
}

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

Обмежує кількість рядків, що повертаються (LIMIT) і опціонально задає зміщення:

$table->limit(10);        // LIMIT 10 (повертає перші 10 рядків)
$table->limit(10, 20);    // LIMIT 10 OFFSET 20

Для пагінації доцільніше використовувати метод page().

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

Спрощує пагінацію результатів. Приймає номер сторінки (починаючи з 1) і кількість елементів на сторінці. Як опцію можна передати посилання на змінну, в якій буде зберігатися загальна кількість сторінок:

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

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

Групує рядки за вказаними стовпцями (GROUP BY). Зазвичай використовується в поєднанні з агрегатними функціями:

// Підраховує кількість товарів у кожній категорії
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');

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

Задає умову для фільтрації згрупованих рядків (HAVING). Може використовуватися в поєднанні з методом group() і агрегатними функціями:

// Знаходить категорії з більш ніж 100 товарами
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Читання даних

Для читання даних з бази даних існує кілька корисних методів:

Об'єкт ActiveRow доступний тільки для читання. Це означає, що ви не можете змінювати значення його властивостей. Це обмеження забезпечує узгодженість даних і запобігає несподіваним побічним ефектам. Дані беруться з бази даних, і будь-які зміни повинні проводитися явно і контрольованим чином.

foreach – Ітерація по всіх рядках

Найпростіший спосіб виконати запит і отримати рядки – це ітерація за допомогою циклу foreach. Він автоматично виконує SQL-запит.

$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key = первинний ключ, $book = ActiveRow
	echo "$book->title ({$book->author->name})";
}

get ($key): ?ActiveRow

Виконує SQL-запит і повертає рядок за первинним ключем або null, якщо він не існує.

$book = $explorer->table('book')->get(123); // повертає ActiveRow з ідентифікатором 123 або null
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Повертає один рядок і переводить внутрішній покажчик на наступний. Якщо рядків більше немає, повертається null.

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

fetchPairs(): array

Повертає результати у вигляді асоціативного масиву. У першому аргументі вказується ім'я стовпця, яке буде використовуватися в якості ключа масиву, а в другому – ім'я стовпця, яке буде використовуватися в якості значення:

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

Якщо вказано тільки ключовий стовпець, то значенням буде весь рядок, тобто об'єкт ActiveRow:

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

Якщо в якості ключа вказано null, то масив буде мати числовий індекс, починаючи з нуля:

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

В якості параметра можна також передати зворотний виклик, який поверне або саме значення, або пару ключ-значення для кожного ряду. Якщо зворотний виклик повертає тільки значення, то ключем буде первинний ключ рядка:

$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// [1 => 'Перша книга (Ян Новак)', ...].

// Зворотний виклик також може повертати масив з парою "ключ і значення":
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['Перша книга' => 'Ян Новак', ...].

fetchAll(): array

Повертає всі рядки у вигляді асоціативного масиву об'єктів ActiveRow, де ключами є значення первинного ключа.

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

count(): int

Метод count() без параметрів повертає кількість рядків в об'єкті Selection:

$table->where('category', 1);
$count = $table->count();
$count = count($table); // альтернатива

Примітка: count() з параметром виконує функцію агрегування COUNT в базі даних, як описано нижче.

ActiveRow::toArray(): array

Перетворює об'єкт ActiveRow в асоціативний масив, ключами якого є імена стовпців, а значеннями – відповідні дані.

$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray складатиметься з ['id' => 1, 'title' => '...', 'author_id' => ..., ...].

Агрегація

Клас Selection надає методи для зручного виконання функцій агрегування (COUNT, SUM, MIN, MAX, AVG і т. д.).

count (string $expr): int

Виконує SQL-запит з функцією COUNT і повертає результат. Цей метод використовується для визначення кількості рядків, що відповідають певній умові:

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

Примітка: функція count() без параметра просто повертає кількість рядків в об'єкті Selection.

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

Методи min() і max() повертають мінімальне і максимальне значення в зазначеному стовпці або виразі:

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

sum (string $expr): int

Повертає суму значень в зазначеному стовпці або виразі:

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

Дозволяє виконати будь-яку агрегатну функцію.

// Обчислює середню ціну товарів в категорії
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// Об'єднує теги товарів в один рядок
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Якщо нам потрібно агрегувати результати, які самі є результатом агрегування і групування (наприклад, SUM(value) над згрупованими рядками), то в якості другого аргументу ми вказуємо функцію агрегування, яка буде застосовуватися до цих проміжних результатів:

// Розраховує загальну ціну товарів на складі для кожної категорії, потім підсумовує ці ціни
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

У цьому прикладі ми спочатку обчислюємо загальну ціну товарів в кожній категорії (SUM(price * stock) AS category_total) і групуємо результати за category_id. Потім ми використовуємо aggregation('SUM(category_total)', 'SUM') для підсумовування цих проміжних підсумків. Другий аргумент 'SUM' задає функцію агрегування, яку потрібно застосувати до проміжних результатів.

Вставка, оновлення та видалення

Nette Database Explorer спрощує вставку, оновлення та видалення даних. Всі перераховані методи викидають повідомлення Nette\Database\DriverException у разі помилки.

Selection::insert (iterable $data): static

Вставляє нові записи в таблицю.

Вставка одного запису:.

Новий запис передається у вигляді асоціативного масиву або ітерабельного об'єкта (наприклад, ArrayHash, що використовується в формах), де ключі відповідають іменам стовпців в таблиці.

Якщо таблиця має визначений первинний ключ, метод повертає об'єкт ActiveRow, який перезавантажується з бази даних, щоб відобразити будь-які зміни, зроблені на рівні бази даних (наприклад, тригери, значення стовпців за замовчуванням або обчислення з автоінкрементами). Це забезпечує узгодженість даних, і об'єкт завжди містить поточні дані бази даних. Якщо первинний ключ не визначено явно, метод повертає вхідні дані у вигляді масиву.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row - це екземпляр ActiveRow, що містить повні дані вставленого ряду,
// включаючи автоматично згенерований ідентифікатор і будь-які зміни, зроблені тригерами
echo $row->id;          // Виводить ідентифікатор нового вставленого користувача
echo $row->created_at;  // Виводить час створення, якщо він встановлений тригером

Вставка декількох записів одночасно:.

Метод insert() дозволяє вставити кілька записів за допомогою одного SQL-запиту. У цьому випадку він повертає кількість вставлених рядків.

$insertedRows = $explorer->table('users')->insert([
	[
		'name' => 'John',
		'year' => 1994,
	],
	[
		'name' => 'Jack',
		'year' => 1995,
	],
]);
// INSERT INTO `users` (`name`, `year`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows буде дорівнювати 2

В якості параметра можна також передати об'єкт Selection з вибіркою даних.

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

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

Вставка спеціальних значень:

Значення можуть включати файли, об'єкти DateTime або літерали SQL:

$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // перетворення в формат бази даних
	'avatar' => fopen('image.jpg', 'rb'),   // вставляє вміст бінарного файлу
	'uuid' => $explorer::literal('UUID()'), // викликає функцію UUID()
]);

Selection::update (iterable $data): int

Оновлює рядки в таблиці на основі заданого фільтра. Повертає кількість фактично змінених рядків.

Оновлювані стовпці передаються у вигляді асоціативного масиву або ітерабельного об'єкта (наприклад, ArrayHash, що використовується в формах), де ключі відповідають іменам стовпців в таблиці:

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

Для зміни числових значень можна використовувати оператори += і -=:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // збільшує значення стовпця "points" на 1
		'coins-=' => 1,   // зменшує значення стовпця 'coins' на 1
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10

Selection::delete(): int

Видаляє рядки з таблиці на основі заданого фільтра. Повертає кількість видалених рядків.

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

При виклику update() або delete() обов'язково використовуйте where() для вказівки рядків, що оновлюються або видаляються. Якщо where() не використовується, операція буде виконана над усією таблицею!

ActiveRow::update (iterable $data): bool

Оновлює дані в рядку бази даних, представленому об'єктом ActiveRow. В якості параметра він приймає ітерабельні дані, де ключами є імена стовпців. Для зміни числових значень можна використовувати оператори += і -=:

Після виконання оновлення ActiveRow автоматично перезавантажується з бази даних, щоб відобразити всі зміни, зроблені на рівні бази даних (наприклад, тригерами). Метод повертає true тільки в тому випадку, якщо відбулася реальна зміна даних.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // збільшує кількість переглядів
]);
echo $article->views; // Виводить поточну кількість переглядів

Цей метод оновлює тільки один конкретний рядок в базі даних. Для масового оновлення декількох рядків використовуйте метод Selection::update().

ActiveRow::delete()

Видаляє з бази даних рядок, представлений об'єктом ActiveRow.

$book = $explorer->table('book')->get(1);
$book->delete(); // Видалення книги з ідентифікатором 1

Цей метод видаляє тільки один конкретний ряд в базі даних. Для масового видалення декількох рядків використовуйте метод Selection::delete().

Відносини між таблицями

У реляційних базах даних дані розділені на кілька таблиць і пов'язані між собою за допомогою зовнішніх ключів. Nette Database Explorer пропонує революційний спосіб роботи з цими відносинами – без написання запитів JOIN і без необхідності конфігурування або генерації сутностей.

Для демонстрації ми скористаємося базою даних example(доступна на GitHub). База даних включає в себе наступні таблиці:

• author – автори і перекладачі (стовпці id, name, web, born)
• book – книги (стовпці id, author_id, translator_id, title, sequel_id)
• tag – теги (колонки id, name)
• book_tag – таблиця зв'язків між книгами і тегами (колонки book_id, tag_id)

Структура бази даних

У цьому прикладі бази даних книг ми бачимо кілька типів зв'язків (спрощених в порівнянні з реальністю):

• Один-до-багатьох (1:N) – У кожної книги є один автор; автор може написати безліч книг.
• Нуль-до-багатьох (0:N) – У книги може бути перекладач; перекладач може перекласти безліч книг.
• Нуль-до-одного (0:1) – Книга може мати продовження.
• Багато-до-багатьох (M:N) – Книга може мати кілька тегів, і один тег може бути присвоєний декільком книгам.

У цих відносинах завжди є батьківська таблиця і дочірня таблиця. Наприклад, у відносинах між авторами і книгами таблиця author є батьківською, а таблиця book – дочірньою – можна вважати, що книга завжди “належить” одному автору. Це також відображено в структурі бази даних: дочірня таблиця book містить зовнішній ключ author_id, який посилається на батьківську таблицю author.

Якщо ми хочемо відобразити книги разом з іменами їх авторів, у нас є два варіанти. Або ми отримуємо дані за допомогою одного SQL-запиту з JOIN:

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

Або ми отримуємо дані в два етапи – спочатку книги, потім їх авторів – і збираємо їх в PHP:

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

Другий підхід, як не дивно, більш ефективний. Дані витягуються тільки один раз і можуть бути краще використані в кеші. Саме так працює Nette Database Explorer – він обробляє все під капотом і надає вам чистий API:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author - це запис з таблиці 'author'.
	echo 'translated by: ' . $book->translator?->name;
}

Доступ до батьківської таблиці

Доступ до батьківської таблиці дуже простий. Це такі відносини, як у книги є автор або у книги може бути перекладач. Доступ до пов'язаного запису можна отримати через властивість об'єкта ActiveRow – ім'я властивості збігається з ім'ям стовпця зовнішнього ключа без суфікса id:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // знаходить автора за стовпцем 'author_id'
echo $book->translator?->name; // знаходить перекладача за стовпцем 'translator_id'

При зверненні до властивості $book->author Explorer шукає в таблиці book стовпець, що містить рядок author (наприклад, author_id). На підставі значення в цьому стовпці він витягує відповідний запис з таблиці author і повертає його у вигляді об'єкта ActiveRow. Аналогічно, $book->translator використовує стовпець translator_id. Оскільки стовпець translator_id може містити null, використовується оператор ?->.

Альтернативний підхід забезпечується методом ref(), який приймає два аргументи – ім'я цільової таблиці і зв'язуючий стовпець – і повертає екземпляр ActiveRow або null:

echo $book->ref('author', 'author_id')->name;      // посилання на автора
echo $book->ref('author', 'translator_id')->name;  // посилання на перекладача

Метод ref() корисний, якщо доступ на основі властивостей не може бути використаний, наприклад, коли таблиця містить стовпець з тим же ім'ям, що і властивість (author). В інших випадках рекомендується використовувати доступ на основі властивостей для кращої читабельності.

Explorer автоматично оптимізує запити до бази даних. При ітерації книг і доступі до пов'язаних з ними записів (автори, перекладачі) Explorer не генерує запит для кожної книги окремо. Замість цього він виконує тільки один запит SELECT для кожного типу відносин, що значно знижує навантаження на базу даних. Наприклад:

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

Цей код виконає тільки три оптимізованих запити до бази даних:

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

Логіка визначення зв'язуючого стовпця визначається реалізацією Conventions. Ми рекомендуємо використовувати DiscoveredConventions, яка аналізує зовнішні ключі і дозволяє безперешкодно працювати з існуючими зв'язками таблиць.

Доступ до дочірньої таблиці

Доступ до дочірньої таблиці працює в зворотному напрямку. Тепер ми запитуємо які книги написав цей автор або які книги переклав цей перекладач. Для цього типу запиту ми використовуємо метод related(), який повертає об'єкт Selection з відповідними записами. Ось приклад:

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

// Виводить всі книги, написані автором
foreach ($author->related('book.author_id') as $book) {
	echo "Wrote: $book->title";
}

// Виводить всі книги, перекладені автором
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}

Метод related() приймає опис відносини як один аргумент з використанням точкової нотації або як два окремих аргументи:

$author->related('book.translator_id');    // один аргумент
$author->related('book', 'translator_id'); // два аргументи

Explorer може автоматично визначити правильний стовпець зв'язку на основі імені батьківської таблиці. В даному випадку зв'язок встановлюється через стовпець book.author_id, оскільки ім'я вихідної таблиці – author:

$author->related('book'); // використовує book.author_id

Якщо існує кілька можливих зв'язків, Explorer викине виняток AmbiguousReferenceKeyException.

Звичайно, ми також можемо використовувати метод related() при циклічному переборі декількох записів, і Explorer автоматично оптимізує запити і в цьому випадку:

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

Цей код генерує тільки два ефективних SQL-запити:

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

Відносини “багато-до-багатьох”

Для відносин “багато-до-багатьох” (M:N) потрібна таблиця-перехрестя (в нашому випадку book_tag). Ця таблиця містить два стовпці із зовнішніми ключами (book_id, tag_id). Кожен стовпець посилається на первинний ключ однієї з пов'язаних таблиць. Щоб отримати пов'язані дані, ми спочатку витягуємо записи з таблиці зв'язків за допомогою related('book_tag'), а потім переходимо до цільових даних:

$book = $explorer->table('book')->get(1);
// Виводить імена тегів, присвоєних книзі
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // отримує назву тега через таблицю посилань
}

$tag = $explorer->table('tag')->get(1);
// Протилежний напрямок: виводить назви книг з даним тегом
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // отримує назву книги
}

Explorer знову оптимізує SQL-запити в ефективну форму:

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

Запит через пов'язані таблиці

У методах where(), select(), order() і group() можна використовувати спеціальні позначення для доступу до стовпців з інших таблиць. Explorer автоматично створює необхідні JOIN.

Точкова нотація (parent_table.column) використовується для відносин 1:N з точки зору батьківської таблиці:

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

// Знаходить книги, імена авторів яких починаються з "Jon".
$books->where('author.name LIKE ?', 'Jon%');

// Сортує книги за ім'ям автора за спаданням
$books->order('author.name DESC');

// Виводить назву книги та ім'я автора
$books->select('book.title, author.name');

Нотація з двокрапкою (:child_table.column) використовується для відносин 1:N з точки зору дочірньої таблиці:

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

// Знаходить авторів, які написали книги з 'PHP' в назві
$authors->where(':book.title LIKE ?', '%PHP%');

// Підраховує кількість книг для кожного автора
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

У наведеному вище прикладі з позначенням двокрапки (:book.title) стовпець зовнішнього ключа явно не вказано. Explorer автоматично визначає потрібний стовпець на основі імені батьківської таблиці. В даному випадку з'єднання виконується через стовпець book.author_id, оскільки ім'я вихідної таблиці – author. Якщо існує кілька можливих з'єднань, Explorer викидає виняток AmbiguousReferenceKeyException.

Зв'язуючий стовпець можна явно вказати в круглих дужках:

// Знаходить авторів, які переклали книгу з 'PHP' в назві
$authors->where(':book(translator).title LIKE ?', '%PHP%');

Нотації можна об'єднувати в ланцюжки для доступу до даних в декількох таблицях:

// Пошук авторів книг, позначених тегом 'PHP'.
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

Розширення умов для JOIN

Метод joinWhere() додає додаткові умови до об'єднання таблиць в SQL після ключового слова ON.

Наприклад, ми хочемо знайти книги, перекладені певним перекладачем:

// Знаходить книги, перекладені перекладачем на ім'я 'David'
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')

В умові joinWhere() можна використовувати ті ж конструкції, що і в методі where(), – оператори, заповнювачі, масиви значень або вирази SQL.

Для більш складних запитів з кількома JOIN можна визначити псевдоніми таблиць:

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

Зверніть увагу, що якщо метод where() додає умови в речення WHERE, то метод joinWhere() розширює умови в реченні ON при об'єднанні таблиць.

Створення провідника вручну

Якщо ви не використовуєте контейнер Nette DI, ви можете створити екземпляр Nette\Database\Explorer вручну:

use Nette\Database;

// $storage реалізує Nette\Caching\Storage, наприклад:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// підключення до бази даних
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// управляє відображенням структури бази даних
$structure = new Database\Structure($connection, $storage);
// визначає правила зіставлення імен таблиць, стовпців і зовнішніх ключів
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);