Прямой SQL

Вы можете работать с Nette Database двумя способами: писать SQL-запросы напрямую (Direct Access) или использовать автоматическую генерацию SQL(Explorer Access). Прямой доступ позволяет безопасно создавать запросы, сохраняя полный контроль над их структурой.

О создании соединения и его настройке читайте на отдельной странице.

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

Метод 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-инъекций

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

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

// ❌ UNSAFE CODE - уязвимость к 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%'

Особые случаи, такие как значения null или массивы, обрабатываются автоматически:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // использует оператор =
	'category_id' => [1, 2, 3], // использует IN
	'description' => null,      // uses 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 может быть задано в виде массива, где ключи представляют столбцы, а значения – булевы, указывающие на порядок возрастания:

$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() возвращает идентификатор последней вставленной строки. Для некоторых баз данных (например, PostgreSQL) необходимо указать имя последовательности с помощью $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('file.png', '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).

Литералы 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 &lt; ?', $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

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

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - Джон', '2 - Джейн', ...].

// Обратный вызов также может возвращать массив с парой ключ и значение:
$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 is of type $type->type"; // Например, 'id имеет тип int'.
}

Ведение журнала запросов

Вы можете реализовать пользовательское протоколирование запросов. Событие onQuery представляет собой массив обратных вызовов, которые вызываются после каждого выполнения запроса:

$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');
	}
};