Прямий SQL

Ви можете працювати з базою даних Nette двома способами: безпосередньо писати SQL-запити (прямий доступ) або дозволити SQL генеруватися автоматично(Explorer Access). Direct 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-ін'єкцій.

// НЕБЕЗПЕЧНИЙ КОД - вразливий до SQL-ін'єкцій
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Обов'язково ознайомтеся з потенційними ризиками безпеки.

Методи запитів

Де Умови

Ви можете записати умови 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` = 'Ноутбук' 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,      // uses IS NOT NULL
	'id' => [],                     // пропущено
]);
// WHERE `name` <> 'Ноутбук' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

За замовчуванням умови об'єднуються за допомогою оператора AND. Ви можете змінити цю поведінку за допомогою заповнювача ?or.

Впорядкувати за правилами

Речення 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)
//  ПРИ ОНОВЛЕННІ ДВІЧНОГО КЛЮЧА `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 - John', '2 - Jane', ...]

// Зворотний виклик також може повертати масив з парою ключ & значення:
$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');
	}
};