SQL-подход

Nette Database предлагает два способа работы: вы можете писать SQL-запросы сами (SQL-подход) или позволить порождать их автоматически (см. Explorer). SQL-подход даёт вам полный контроль над запросами и при этом обеспечивает их безопасное построение.

Подробности о соединении с базой данных и его настройке можно найти в главе Соединение и настройка.

Основы запросов

Для запросов к базе данных служит метод query(). Он возвращает объект ResultSet, представляющий результат запроса. Если запрос не удаётся, метод выбрасывает исключение. Результат запроса можно обойти циклом foreach или воспользоваться одним из вспомогательных методов.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

Чтобы безопасно вставлять значения в SQL-запросы, используйте параметризованные запросы. Nette Database делает это исключительно просто: достаточно добавить после SQL-запроса запятую и значение:

$database->query('SELECT * FROM users WHERE name = ?', $name);

При нескольких параметрах у вас два варианта. Вы можете чередовать SQL-запрос и параметры:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Или сначала написать весь SQL-запрос, а затем добавить все параметры:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Защита от SQL injection

Почему важно использовать параметризованные запросы? Потому что они защищают вас от атаки под названием SQL injection, при которой злоумышленник может подсунуть собственные SQL-команды и тем самым получить доступ к данным в базе или повредить их.

Никогда не вставляйте переменные прямо в SQL-запрос! Всегда используйте параметризованные запросы, которые защищают вас от SQL injection.

// ❌ ОПАСНЫЙ КОД - уязвим для SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Безопасный параметризованный запрос
$database->query('SELECT * FROM users WHERE name = ?', $name);

Ознакомьтесь с возможными рисками безопасности.

Приёмы построения запросов

Условия WHERE

Условия WHERE можно записать ассоциативным массивом, где ключи – имена столбцов, а значения – данные для сравнения. Nette Database автоматически выбирает наиболее подходящий оператор SQL по типу значения.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

Оператор сравнения можно указать в ключе и явно:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // использует оператор >
	'name LIKE' => '%John%', // использует оператор LIKE
	'email NOT LIKE' => '%example.com%', // использует оператор NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette автоматически обрабатывает особые случаи вроде значений null или массивов.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // использует оператор =
	'category_id' => [1, 2, 3], // использует IN
	'description' => null,      // использует IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Для отрицательных условий используйте оператор NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // использует оператор !=
	'category_id NOT' => [1, 2, 3], // использует NOT IN
	'description NOT' => null,      // использует IS NOT NULL
	'id NOT' => [],                 // пропускается
]);
// WHERE `name` != 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

По умолчанию условия объединяются оператором AND. Это можно изменить подсказкой ?or.

Правила ORDER BY

Конструкцию ORDER BY можно записать массивом. Столбцы указывайте в ключах, а логическим значением обозначайте порядок: по возрастанию (true) или по убыванию (false):

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // по возрастанию
	'name' => false, // по убыванию
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Вставка данных (INSERT)

Для вставки записей служит команда SQL INSERT.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Метод getInsertId() возвращает ID последней вставленной записи. Для некоторых баз данных (например, PostgreSQL) нужно указать параметром имя последовательности, из которой должен порождаться ID: $database->getInsertId($sequenceId).

Параметрами можно передавать и особые значения, такие как файлы, объекты DateTime или типы enum.

Вставка нескольких записей сразу:

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

Многозаписный INSERT намного быстрее, потому что выполняется всего один запрос к базе вместо множества отдельных.

Замечание о безопасности: никогда не используйте непроверенные данные в качестве $values. Ознакомьтесь с возможными рисками.

Обновление данных (UPDATE)

Для обновления записей служит команда SQL UPDATE.

// Обновление одной записи
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Число затронутых записей возвращает $result->getRowCount().

Для UPDATE мы можем использовать операторы += и -=:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // увеличивает login_count
], 1);

Пример вставки или обновления записи, если она уже существует. Мы используем приём ON DUPLICATE KEY UPDATE:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Обратите внимание, что Nette Database распознаёт контекст, в котором в SQL-команде используется параметр-массив, и строит SQL-код соответственно. Так, из первого массива он построил (id, name, year) VALUES (123, 'Jim', 1978), а второй преобразовал в вид name = 'Jim', year = 1978. Подробнее об этом в разделе Подсказки построения SQL.

Удаление данных (DELETE)

Для удаления записей служит команда SQL DELETE. Пример получения числа удалённых записей:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Подсказки построения SQL

Подсказка – особая подстановка в SQL-запросе, задающая, как значение параметра нужно превратить в выражение SQL:

Подсказка	Описание	Автоматически используется в
`?name`	Служит для вставки имён таблиц или столбцов	–
`?values`	Порождает `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Порождает присваивания `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Объединяет условия массива через `AND`	`WHERE ?`, `HAVING ?`
`?or`	Объединяет условия массива через `OR`	–
`?order`	Порождает конструкцию `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Подстановка ?name служит для динамической вставки имён таблиц и столбцов в запрос. Nette Database заботится о правильном экранировании идентификаторов по соглашениям базы данных (например, о заключении в обратные кавычки в MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (в MySQL)

Внимание: используйте подстановку ?name только для проверенных имён таблиц и столбцов. Иначе вы рискуете уязвимостями безопасности.

Остальные подсказки обычно указывать не нужно, потому что при построении SQL-запроса Nette использует умное автоопределение (см. третий столбец таблицы). Но вы можете применить их, например, в ситуации, когда хотите объединить условия через OR вместо AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Особые значения

Помимо обычных скалярных типов (string, int, bool) параметрами можно передавать и особые значения:

файлы: fopen('image.gif', 'r') вставляет двоичное содержимое файла
дату и время: объекты DateTimeInterface преобразуются в формат базы данных
типы enum: экземпляры enum преобразуются в своё значение
литералы SQL: созданные через Connection::literal('NOW()') вставляются в запрос напрямую

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTimeImmutable, // или new DateTime
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Для баз данных без нативной поддержки типа datetime (таких как SQLite и Oracle) объекты DateTime и DateTimeImmutable преобразуются в значение, заданное в конфигурации базы данных параметром formatDateTime (значение по умолчанию – U, то есть Unix-время).

Литералы SQL

В некоторых случаях вам нужно передать значением сырой SQL-код, который не должен восприниматься как строка и экранироваться. Для этого служат объекты класса Nette\Database\SqlLiteral. Их создаёт метод Connection::literal().

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Как вариант:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

Литералы SQL могут содержать параметры:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Это позволяет строить интересные сочетания:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Получение данных

Сокращения для запросов SELECT

Чтобы упростить получение данных, Connection предлагает несколько сокращений, объединяющих вызов query() с последующим вызовом fetch*(). Эти методы принимают те же параметры, что и query(), то есть SQL-запрос и необязательные параметры. Полное описание методов fetch*() можно найти ниже.

`fetch($sql, ...$params): ?Row`	Выполняет запрос и возвращает первую запись как объект `Row` или `null`.
`fetchAll($sql, ...$params): array`	Выполняет запрос и возвращает все записи как массив объектов `Row`.
`fetchPairs($sql, ...$params): array`	Выполняет запрос и возвращает ассоциативный массив (пары ключ ⇒ значение).
`fetchField($sql, ...$params): mixed`	Выполняет запрос и возвращает значение первого столбца первой записи.
`fetchList($sql, ...$params): ?array`	Выполняет запрос и возвращает первую запись как индексированный массив или `null`.

Пример:

// fetchField() - возвращает значение первой ячейки
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – обход записей

После выполнения запроса возвращается объект ResultSet, позволяющий обходить результаты несколькими способами. Проще всего выполнить запрос и получить записи обходом в цикле foreach. Этот способ наиболее экономен по памяти, потому что получает данные запись за записью и не загружает весь результат в память сразу.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	// ...
}

ResultSet можно обойти только один раз. Если вам нужен повторный обход, сначала загрузите данные в массив, например методом fetchAll().

fetch(): ?Row

Возвращает запись как объект Row. Если записей больше нет, возвращает null. Сдвигает внутренний указатель на следующую запись.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // загружает первую запись
if ($row) {
	echo $row->name;
}

fetchAll(): array

Возвращает все оставшиеся записи из ResultSet как массив объектов Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // загружает все записи
foreach ($rows as $row) {
	echo $row->name;
}

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

Возвращает результат как ассоциативный массив. Первый аргумент задаёт столбец, который используется как ключи, а второй – столбец, используемый как значения:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Если задан только первый параметр ($key), значением будет вся запись (объект Row):

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

При повторяющихся ключах используется значение из последней записи. Использование null в качестве ключа даёт массив с числовой индексацией (начиная с нуля), что предотвращает столкновения ключей:

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Как вариант, вы можете передать callback, обрабатывающий каждую запись. Callback может вернуть одно значение или пару ключ-значение.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Callback может вернуть и массив с парой ключ и значение:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Возвращает значение первого столбца текущей записи. Если записей больше нет, возвращает null. Сдвигает внутренний указатель на следующую запись.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // загружает name из первой записи

fetchList(): ?array

Возвращает запись как индексированный массив. Если записей больше нет, возвращает null. Сдвигает внутренний указатель на следующую запись.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Возвращает число затронутых записей от последнего запроса UPDATE или DELETE. Для запросов SELECT возвращает число записей в результате. Однако оно не всегда бывает известно, и в этом случае метод возвращает null.

getColumnCount(): ?int

Возвращает число столбцов в ResultSet.

Сведения о запросе

Ради отладки мы можем получить сведения о последнем выполненном запросе:

echo $database->getLastQueryString();   // выводит SQL-запрос

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // выводит SQL-запрос
echo $result->getTime();           // выводит время выполнения в секундах

Чтобы вывести результат в виде HTML-таблицы, можно использовать:

$result = $database->query('SELECT * FROM articles');
$result->dump();

ResultSet предоставляет сведения о типах столбцов:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column is of type $type"; // например, 'id is of type int'
}

Логирование запросов

Мы можем реализовать собственное логирование запросов. Событие onQuery – массив callback-функций, вызываемых после каждого выполненного запроса:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};