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

Работа с проводником начинается с вызова метода 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**: генерирует: `имя` LIKE ?
where('LOWER(name) = ?', $value);// **CORRECT**: LOWER(`name`) = ?

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

Фильтрует результаты с помощью условий WHERE. Его сильной стороной является интеллектуальная обработка различных типов значений и автоматический выбор операторов SQL.

Базовое использование:

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

Благодаря автоматическому определению подходящих операторов вам не нужно разбираться с особыми случаями – Nette сделает это за вас:

$table->where('id', 1);          // ГДЕ `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 (`цена_окончательная` < `цена_оригинальная`) AND (`stock_count` > `min_stock`)
$table->where([
	'price_final &lt; 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 (`возраст` > 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 (`цена` > 1000) OR (`цена_с_таксом` > 1500)
$table->whereOr([
	'price > ?' => 1000,
	'price_with_tax > ?' => 1500,
]);

wherePrimary (mixed $key): static

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

// ГДЕ `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);    // ОГРАНИЧЕНИЕ 10 СМЕЩЕНИЕ 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 (string|int|null $key = null, string|int|null $value = null): 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', ...]

fetchPairs (Closure $callback): array

В качестве альтернативы вы можете передать в качестве параметра обратный вызов. Обратный вызов будет применен к каждому ряду, чтобы вернуть либо одиночное значение, либо пару ключ-значение.

$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(`цена` * `позиции_на_складе`) FROM `продукты` WHERE `активный` = 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` (`имя`, `год`) 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,  // увеличивает значение столбца "очки" на 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) – У книги может быть переводчик; переводчик может перевести множество книг.
• Zero-to-one (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');

Точечная нотация используется для отношений 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);