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-инъекций
$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' => [],                     // пропускается
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

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

Правила ORDER BY

Сортировку ORDER BY можно записать с помощью массива. В ключах указываем столбцы, а значением будет boolean, определяющий, сортировать ли по возрастанию:

$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 или перечисляемые типы.

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

$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 используйте только для имен таблиц и столбцов из валидированных входных данных, иначе вы подвергаетесь риску безопасности.

Остальные подсказки обычно указывать не нужно, так как Nette использует умное автоопределение при составлении SQL-запроса (см. третий столбец таблицы). Но вы можете использовать его, например, в ситуации, когда хотите объединить условия с помощью 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') вставляет бинарное содержимое файла
дата и время: объекты DateTime преобразуются в формат базы данных
перечисляемые типы: экземпляры enum преобразуются в их значение
SQL-литералы: созданные с помощью Connection::literal('NOW()') вставляются непосредственно в запрос

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

В базах данных, которые не имеют нативной поддержки типа данных datetime (например, SQLite и Oracle), DateTime преобразуется в значение, указанное в конфигурации базы данных в элементе formatDateTime (значение по умолчанию — U – unix timestamp).

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`
`fetchAll($sql, ...$params): array`	Выполняет запрос и возвращает все строки как массив объектов `Row`
`fetchPairs($sql, ...$params): array`	Выполняет запрос и возвращает ассоциативный массив, где первый столбец представляет ключ, а второй — значение
`fetchField($sql, ...$params): mixed`	Выполняет запрос и возвращает значение первого поля из первой строки
`fetchList($sql, ...$params): ?array`	Выполняет запрос и возвращает первую строку как индексированный массив

Пример:

// 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', ...]

Если указать только первый параметр, значением будет вся строка, то есть объект 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, который будет для каждой строки возвращать либо само значение, либо пару ключ-значение.

$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(); // считывает имя из первой строки

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 имеет тип $type->type"; // например, 'id имеет тип int'
}

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

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

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Запрос: ' . $result->getQueryString());
	$logger->info('Время: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Большой набор результатов: ' . $result->getRowCount() . ' строк');
	}
};

Nette Documentation Preview