SQL підхід
**********

.[perex]
Nette Database пропонує два шляхи: ви можете писати SQL-запити самостійно (SQL підхід), або дозволити генерувати їх автоматично (див. [Explorer |explorer]). SQL підхід дає вам повний контроль над запитами і при цьому забезпечує їх безпечне формування.

.[note]
Деталі щодо підключення та конфігурації бази даних знайдете в розділі [Підключення та конфігурація |guide#Підключення та конфігурація].


Базові запити
=============

Для запитів до бази даних служить метод `query()`. Він повертає об'єкт [ResultSet |api:Nette\Database\ResultSet], який представляє результат запиту. У разі невдачі метод [викине виняток|exceptions]. Результат запиту можна перебирати за допомогою циклу `foreach`, або використати одну з [допоміжних функцій |#Отримання даних].

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

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

Для безпечного вставлення значень у SQL-запити використовуємо параметризовані запити. Nette Database робить їх максимально простими - достатньо після SQL-запиту додати кому та значення:

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

При використанні кількох параметрів у вас є два варіанти запису. Ви можете "розбавляти" SQL-запит параметрами:

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

Або написати спочатку весь SQL-запит, а потім додати всі параметри:

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


Захист від SQL injection
========================

Чому важливо використовувати параметризовані запити? Тому що вони захищають вас від атаки під назвою SQL injection, під час якої зловмисник міг би підсунути власні SQL-команди і таким чином отримати або пошкодити дані в базі даних.

.[warning]
**Ніколи не вставляйте змінні безпосередньо в SQL-запит!** Завжди використовуйте параметризовані запити, які захистять вас від SQL injection.

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

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

Ознайомтеся з [можливими ризиками безпеки |security].


Техніки запитів
===============


Умови WHERE
-----------

Умови WHERE можна записати як асоціативний масив, де ключі - це назви стовпців, а значення - дані для порівняння. Nette Database автоматично вибере найбільш відповідний SQL-оператор залежно від типу значення.

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

У ключі можна також явно вказати оператор для порівняння:

```php
$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` або масиви.

```php
$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`:

```php
$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 |#Підказки для побудови SQL].


Правила ORDER BY
----------------

Сортування `ORDER BY` можна записати за допомогою масиву. У ключах вказуємо стовпці, а значенням буде boolean, що визначає, чи сортувати за зростанням:

```php
$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // за зростанням
	'name' => false, // за спаданням
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC
```


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

Для вставки записів використовується SQL-команда `INSERT`.

```php
$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 або перелічувані типи.

Вставка кількох записів одночасно:

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

Багаторазовий INSERT набагато швидший, оскільки виконується єдиний запит до бази даних замість багатьох окремих.

**Попередження щодо безпеки:** Ніколи не використовуйте як `$values` невалідовані дані. Ознайомтеся з [можливими ризиками |security#Безпечна робота зі стовпцями].


Оновлення даних (UPDATE)
------------------------

Для оновлення записів використовується SQL-команда `UPDATE`.

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

Кількість зачеплених рядків поверне `$result->getRowCount()`.

Для UPDATE можна використовувати оператори `+=` та `-=`:

```php
$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // інкрементація login_count
], 1);
```

Приклад вставки або оновлення запису, якщо він вже існує. Використаємо техніку `ON DUPLICATE KEY UPDATE`:

```php
$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`. Приклад з отриманням кількості видалених рядків:

```php
$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).

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

**Попередження:** символ `?name` використовуйте лише для назв таблиць та стовпців з валідованих вхідних даних, інакше ви наражаєтеся на [ризик безпеки |security#Динамічні ідентифікатори].

Інші підказки зазвичай не потрібно вказувати, оскільки Nette використовує розумну автодетекцію при складанні SQL-запиту (див. третій стовпець таблиці). Але ви можете її використати, наприклад, у ситуації, коли хочете об'єднати умови за допомогою `OR` замість `AND`:

```php
$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()')` вставляться безпосередньо в запит

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

У базах даних, які не мають нативної підтримки для типу даних `datetime` (як SQLite та Oracle), `DateTime` перетворюється на значення, визначене в [конфігурації бази даних|configuration] елементом `formatDateTime` (значення за замовчуванням - `U` - unix timestamp).


SQL літерали
------------

У деяких випадках потрібно вказати як значення безпосередньо SQL-код, який, однак, не повинен розглядатися як рядок і екрануватися. Для цього служать об'єкти класу `Nette\Database\SqlLiteral`. Їх створює метод `Connection::literal()`.

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

Або альтернативно:

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

SQL літерали можуть містити параметри:

```php
$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)
```

Завдяки чому можна створювати цікаві комбінації:

```php
$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].

| `fetch($sql, ...$params): ?Row`       | Виконує запит і повертає перший рядок як об'єкт `Row`
| `fetchAll($sql, ...$params): array`   | Виконує запит і повертає всі рядки як масив об'єктів `Row`
| `fetchPairs($sql, ...$params): array` | Виконує запит і повертає асоціативний масив, де перший стовпець представляє ключ, а другий - значення
| `fetchField($sql, ...$params): mixed` | Виконує запит і повертає значення першого поля з першого рядка
| `fetchList($sql, ...$params): ?array` | Виконує запит і повертає перший рядок як індексований масив

Приклад:

```php
// fetchField() - повертає значення першої комірки
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();
```


`foreach` - ітерація по рядках
------------------------------

Після виконання запиту повертається об'єкт [ResultSet|api:Nette\Database\ResultSet], який дозволяє перебирати результати кількома способами. Найпростіший спосіб виконати запит і отримати рядки - це ітерація в циклі `foreach`. Цей спосіб є найбільш економним з точки зору пам'яті, оскільки повертає дані поступово і не зберігає їх усі в пам'яті одночасно.

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

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

.[note]
`ResultSet` можна ітерувати лише один раз. Якщо вам потрібно ітерувати повторно, ви повинні спочатку завантажити дані в масив, наприклад, за допомогою методу `fetchAll()`.


fetch(): ?Row .[method]
-----------------------

Повертає рядок як об'єкт `Row`. Якщо більше немає рядків, повертає `null`. Пересуває внутрішній вказівник на наступний рядок.

```php
$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // читає перший рядок
if ($row) {
	echo $row->name;
}
```


fetchAll(): array .[method]
---------------------------

Повертає всі рядки, що залишилися, з `ResultSet` як масив об'єктів `Row`.

```php
$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 .[method]
---------------------------------------------------------------------------------------

Повертає результати як асоціативний масив. Перший аргумент визначає назву стовпця, який буде використаний як ключ у масиві, другий аргумент визначає назву стовпця, який буде використаний як значення:

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

Якщо вказати лише перший параметр, значенням буде весь рядок, тобто об'єкт `Row`:

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

У разі дублювання ключів використовується значення з останнього рядка. При використанні `null` як ключа масив буде індексовано нумерично з нуля (тоді колізій не виникає):

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


fetchPairs(Closure $callback): array .[method]
----------------------------------------------

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

```php
$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 .[method]
-----------------------------

Повертає значення першого поля з поточного рядка. Якщо більше немає рядків, повертає `null`. Пересуває внутрішній вказівник на наступний рядок.

```php
$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // читає ім'я з першого рядка
```


fetchList(): ?array .[method]
-----------------------------

Повертає рядок як індексований масив. Якщо більше немає рядків, повертає `null`. Пересуває внутрішній вказівник на наступний рядок.

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


getRowCount(): ?int .[method]
-----------------------------

Повертає кількість зачеплених рядків останнім запитом `UPDATE` або `DELETE`. Для `SELECT` це кількість повернутих рядків, але вона може бути невідомою - у такому випадку метод поверне `null`.


getColumnCount(): ?int .[method]
--------------------------------

Повертає кількість стовпців у `ResultSet`.


Інформація про запити
=====================

Для цілей налагодження ми можемо отримати інформацію про останній виконаний запит:

```php
echo $database->getLastQueryString();   // виводить SQL-запит

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

Для відображення результату у вигляді HTML-таблиці можна використати:

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

ResultSet пропонує інформацію про типи стовпців:

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

foreach ($types as $column => $type) {
	echo "$column має тип $type->type"; // напр. 'id має тип int'
}
```


Логування запитів
-----------------

Ми можемо реалізувати власне логування запитів. Подія `onQuery` - це масив callback'ів, які викликаються після кожного виконаного запиту:

```php
$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Запит: ' . $result->getQueryString());
	$logger->info('Час: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Великий набір результатів: ' . $result->getRowCount() . ' рядків');
	}
};
```