SQL přístup

Nette Database nabízí dvě cesty: můžete psát SQL dotazy sami (SQL přístup), nebo je nechat generovat automaticky (viz Explorer). SQL přístup vám dává plnou kontrolu nad dotazy a přitom zajišťuje jejich bezpečné sestavení.

Detaily k připojení a konfiguraci databáze najdete v kapitole Připojení a konfigurace.

Základní dotazování

Pro dotazování do databáze slouží metoda query(). Ta vrací objekt ResultSet, který reprezentuje výsledek dotazu. V případě selhání metoda vyhodí výjimku. Výsledek dotazu můžeme procházet pomocí cyklu foreach, nebo použít některou z pomocných funkcí.

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

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

Pro bezpečné vkládání hodnot do SQL dotazů používáme parametrizované dotazy. Nette Database je dělá maximálně jednoduché – stačí za SQL dotaz přidat čárku a hodnotu:

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

Při více parametrech máte dvě možnosti zápisu. Buď můžete SQL dotaz „prokládat“ parametry:

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

Nebo napsat nejdříve celý SQL dotaz a pak připojit všechny parametry:

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

Ochrana před SQL injection

Proč je důležité používat parametrizované dotazy? Protože vás chrání před útokem zvaným SQL injection, při kterém by útočník mohl podstrčit vlastní SQL příkazy a tím získat nebo poškodit data v databázi.

Nikdy nevkládejte proměnné přímo do SQL dotazu! Vždy používejte parametrizované dotazy, které vás ochrání před SQL injection.

// ❌ NEBEZPEČNÝ KÓD - zranitelný vůči SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Bezpečný parametrizovaný dotaz
$database->query('SELECT * FROM users WHERE name = ?', $name);

Seznamte se s možnými bezpečnostními riziky.

Techniky dotazování

Podmínky WHERE

Podmínky WHERE můžete zapsat jako asociativní pole, kde klíče jsou názvy sloupců a hodnoty jsou data pro porovnání. Nette Database automaticky vybere nejvhodnější SQL operátor podle typu hodnoty.

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

V klíči můžete také explicitně specifikovat operátor pro porovnání:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // použije operátor >
	'name LIKE' => '%John%', // použije operátor LIKE
	'email NOT LIKE' => '%example.com%', // použije operátor NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette automaticky ošetřuje speciální případy jako null hodnoty nebo pole.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // použije operátor =
	'category_id' => [1, 2, 3], // použije IN
	'description' => null,      // použije IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Pro negativní podmínky použijte operátor NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // použije operátor <>
	'category_id NOT' => [1, 2, 3], // použije NOT IN
	'description NOT' => null,      // použije IS NOT NULL
	'id' => [],                     // vynechá se
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Pro spojování podmínek se používá operátor AND. To lze změnit pomocí zástupného symbolu ?or.

Pravidla ORDER BY

Řazení ORDER BY se dá zapsat pomocí pole. V klíčích uvedeme sloupce a hodnotou bude boolean určující, zda řadit vzestupně:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // vzestupně
	'name' => false, // sestupně
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Vkládání dat (INSERT)

Pro vkládání záznamů se používá SQL příkaz INSERT.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

Metoda getInsertId() vrátí ID naposledy vloženého řádku. U některých databází (např. PostgreSQL) je nutné jako parametr specifikovat název sekvence, ze které se má ID generovat pomocí $database->getInsertId($sequenceId).

Jako parametry můžeme předávat i speciální hodnoty jako soubory, objekty DateTime nebo výčtové typy.

Vložení více záznamů najednou:

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

Vícenásobný INSERT je mnohem rychlejší, protože se provede jediný databázový dotaz, namísto mnoha jednotlivých.

Bezpečnostní upozornění: Nikdy nepoužívejte jako $values nevalidovaná data. Seznamte se s možnými riziky.

Aktualizace dat (UPDATE)

Pro aktualizacizáznamů se používá SQL příkaz UPDATE.

// Aktualizace jednoho záznamu
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Počet ovlivněných řádků vrátí $result->getRowCount().

Pro UPDATE můžeme využít operátorů += a -=:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // inkrementace login_count
], 1);

Příklad vložení, nebo úpravy záznamu, pokud již existuje. Použijeme techniku 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

Všimněte si, že Nette Database pozná, v jakém kontextu SQL příkazu parametr s polem vkládáme a podle toho z něj sestaví SQL kód. Takže z prvního pole sestavil (id, name, year) VALUES (123, 'Jim', 1978), zatímco druhé převedl do podoby name = 'Jim', year = 1978. Podroběji se tomu věnujeme v části Hinty pro sestavování SQL.

Mazání dat (DELETE)

Pro mazání záznamů se používá SQL příkaz DELETE. Příklad se získáním počtu smazaných řádků:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Hinty pro sestavování SQL

Hint je speciální zástupný symbol v SQL dotazu, který říká, jak se má hodnota parametru přepsat do SQL výrazu:

Hint	Popis	Automaticky se použije
`?name`	použije pro vložení názvu tabulky nebo sloupce	–
`?values`	vygeneruje `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	vygeneruje přiřazení `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	spojí podmínky v poli operátorem `AND`	`WHERE ?`, `HAVING ?`
`?or`	spojí podmínky v poli operátorem `OR`	–
`?order`	vygeneruje klauzuli `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Pro dynamické vkládání názvů tabulek a sloupců do dotazu slouží zástupný symbol ?name. Nette Database se postará o správné ošetření identifikátorů podle konvencí dané databáze (např. uzavření do zpětných uvozovek v MySQL).

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

Upozornění: symbol ?name používejte pouze pro názvy tabulek a sloupců z validovaných vstupů, jinak se vystavujete bezpečnostnímu riziku.

Ostatní hinty obvykle není potřeba uvádět, neboť Nette používá při skládání SQL dotazu chytrou autodetekci (viz třetí sloupec tabulky). Ale můžete jej využít například v situaci, kdy chcete spojit podmínky pomocí OR namísto 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'

Speciální hodnoty

Kromě běžných skalárních typů (string, int, bool) můžete jako parametry předávat i speciální hodnoty:

soubory: fopen('image.gif', 'r') vloží binární obsah souboru
datum a čas: objekty DateTime se převedou na databázový formát
výčtové typy: instance enum se převedou na jejich hodnotu
SQL literály: vytvořené pomocí Connection::literal('NOW()') se vloží přímo do dotazu

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

U databází, které nemají nativní podporu pro datový typ datetime (jako SQLite a Oracle), se DateTime převádí na hodnotu určenou v konfiguraci databáze položkou formatDateTime (výchozí hodnota je U – unix timestamp).

SQL literály

V některých případech potřebujete jako hodnotu uvést přímo SQL kód, který se ale nemá chápat jako řetězec a escapovat. K tomuto slouží objekty třídy Nette\Database\SqlLiteral. Vytváří je metoda Connection::literal().

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

Nebo alternativě:

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

SQL literály mohou obsahovat parametry:

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

Díky čemuž můžeme vytvářet zajímavé kombinace:

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

Získání dat

Zkratky pro SELECT dotazy

Pro zjednodušení načítání dat nabízí Connection několik zkratek, které kombinují volání query() s následujícím fetch*(). Tyto metody přijímají stejné parametry jako query(), tedy SQL dotaz a volitelné parametry. Plnohodnotný popis metod fetch*() najdete níže.

`fetch($sql, ...$params): ?Row`	Provede dotaz a vrátí první řádek jako objekt `Row`
`fetchAll($sql, ...$params): array`	Provede dotaz a vrátí všechny řádky jako pole objektů `Row`
`fetchPairs($sql, ...$params): array`	Provede dotaz a vrátí asocitivní pole, kde první sloupec představuje klíč a druhý hodnotu
`fetchField($sql, ...$params): mixed`	Provede dotaz a vrátí hodnotu prvního políčka z prvního řádku
`fetchList($sql, ...$params): ?array`	Provede dotaz a vrací první řádek jako indexované pole

Příklad:

// fetchField() - vrátí hodnotu první buňky
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – iterace přes řádky

Po vykonání dotazu se vrací objekt ResultSet, který umožňuje procházet výsledky několika způsoby. Nejsnazší způsob, jak vykonat dotaz a získat řádky, je iterováním v cyklu foreach. Tento způsob je paměťově nejúspornější, neboť vrací data postupně a neukládá si je do paměti najednou.

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

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

ResultSet lze iterovat pouze jednou. Pokud potřebujete iterovat opakovaně, musíte nejprve načíst data do pole, například pomocí metody fetchAll().

fetch(): ?Row

Vrací řádek jako objekt Row. Pokud už neexistují další řádky, vrací null. Posune interní ukazatel na další řádek.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // načte první řádek
if ($row) {
	echo $row->name;
}

fetchAll(): array

Vrací všechny zbývající řádky z ResultSetu jako pole objektů Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // načte všechny řádky
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Vrátí výsledky jako asociativní pole. První argument určuje název sloupce, který se použije jako klíč v poli, druhý argument určuje název sloupce, který se použije jako hodnota:

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

Pokud uvedeme pouze první parametr, bude hodnotou celý řádek, tedy objekt Row:

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

Pokud jako klíč uvedeme null, bude pole indexováno numericky od nuly:

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

fetchPairs (Closure $callback): array

Alternativně můžete jako parametr uvést callback, který bude pro každý řádek vracet buď samotnou hodnotu, nebo dvojici klíč-hodnota.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// Callback může také vracet pole s dvojicí klíč & hodnota:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Vrací hodnotu prvního políčka z aktuálního řádku. Pokud už neexistují další řádky, vrací null. Posune interní ukazatel na další řádek.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // načte jméno z prvního řádku

fetchList(): ?array

Vrací řádek jako indexované pole. Pokud už neexistují další řádky, vrací null. Posune interní ukazatel na další řádek.

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

getRowCount(): ?int

Vrací počet ovlivněných řádků posledním dotazem UPDATE nebo DELETE. Pro SELECT je to počet vrácených řádků, ale ten nemusí být znám – v takovém případě metoda vrátí null.

getColumnCount(): ?int

Vrací počet sloupců v ResultSetu.

Informace o dotazech

Pro ladicí účely můžeme získat informace o posledním provedeném dotazu:

echo $database->getLastQueryString();   // vypíše SQL dotaz

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // vypíše SQL dotaz
echo $result->getTime();           // vypíše dobu vykonání v sekundách

Pro zobrazení výsledku jako HTML tabulky lze použít:

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

ResultSet nabízí informace o typech sloupců:

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

foreach ($types as $column => $type) {
	echo "$column je typu $type->type"; // např. 'id je typu int'
}

Logování dotazů

Můžeme implementovat vlastní logování dotazů. Událost onQuery je pole callbacků, které se zavolají po každém provedeném dotazu:

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