Директен SQL

Можете да работите с Nette Database по два начина: като пишете SQL заявки директно (директен достъп) или като оставите 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 инжектиране.

// ❌ БЕЗОПАСЕН КОД - уязвим към 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 `име` = '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,      // използва 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 обекти или 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 `име` = 'Jim', `година` = 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 осигурява правилно ескапиране според конвенциите на базата данни (напр. заграждане в задни тирета за 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 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 &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 => Ред(id: 1, име: "John"), 2 => Ред(id: 2, име: "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]);
// ["Джон" => 46, "Джейн" => 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');
	}
};