Direct SQL

S Nette Database můžete pracovat dvěma způsoby – buď přímo psát SQL dotazy (Direct přístup), nebo nechat SQL generovat automaticky (Explorer přístup). Direct přístup vám pomůže s bezpečným sestavováním dotazů, ale zachovává vám plnou kontrolu nad jejich podobou.

Informace o vytvoření připojení a konfiguraci najdete na samostatné stránce.

Základní dotazování

Databázové dotazy pokládáme metodou query(), která vrací ResultSet.

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

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

echo $result->getRowCount(); // vrací počet řádků výsledku, pokud je znám

Nad ResultSet je možné iterovat pouze jednou, pokud potřebujeme iterovat vícekrát, je nutno výsledek převést na pole metodou fetchAll().

Do dotazu lze velmi snadno přidávat i parametry, všimněte si otazníku:

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

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

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids je pole

V případě selhání query() vyhodí buď Nette\Database\DriverException nebo některého z potomků:

• ConstraintViolationException – porušení nějakého omezení pro tabulku
• ForeignKeyConstraintViolationException – neplatný cizí klíč
• NotNullConstraintViolationException – porušení podmínky NOT NULL
• UniqueConstraintViolationException – koliduje unikátní index

Kromě query() jsou tu další užitečné funkce:

// vrátí asociativní pole id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// vrátí všechny záznamy jako pole
$rows = $database->fetchAll('SELECT * FROM users');

// vrátí jeden záznam
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// vrátí přímo hodnotu buňky
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

V případě selhání všechny tyto metody vyhodí Nette\Database\DriverException.

Insert, Update & Delete

Parameterem, který vkládáme do SQL dotazu, může být i pole (v takovém případě je navíc možné zástupný symbol ? vynechat), což se hodí třeba pro sestavení příkazu INSERT:

$database->query('INSERT INTO users ?', [ // tady můžeme otazník vynechat
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // vrátí auto-increment vloženého záznamu

$id = $database->getInsertId($sequence); // nebo hodnotu sekvence

Vícenásobný INSERT:

$database->query('INSERT INTO users', [
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	]
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

Jako parametry můžeme předávat i soubory nebo objekty DateTime:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // nebo $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // vloží soubor
]);

Úprava záznamů:

$result = $database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

echo $result->getRowCount(); // vrací počet ovlivněných řádků

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

$database->query('UPDATE users SET', [
	'age+=' => 1, // všimněte si +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

Mazání:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // vrací počet ovlivněných řádků

Pokročilé dotazy

Vložení, nebo úprava záznamu, pokud již existuje:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// 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.

Také řazení můžeme ovlivnit polem, 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

Pokud by u neobvyklé konstrukce detekce nezafungovala, můžete formu sestavení určit zástupným symbolem ? doplněným o hint. Podporovány jsou tyto hinty:

V klauzuli WHERE se používá operátor ?and, takže podmínky se spojují operátorem AND:

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

Což můžeme snadno změnit na OR tím, že uvedeme zástupný symbol ?or:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

V podmínkách můžeme používat operátory:

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

A také výčty:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // výčet + operátor NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

Do podmínky také můžeme vložit kus vlastního SQL kódu pomocí tzv. SQL literálu:

$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ál také může mít své 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')

Proměnný název

Ještě existuje zástupný symbol ?name, který využijete v případě, že název tabulky nebo sloupce je proměnnou. (Pozor, nedovolte uživateli manipulovat s obsahem takové proměnné):

$table = 'blog.users';
$column = 'name';
$database->query('SELECT * FROM ?name WHERE ?name = ?', $table, $column, $name);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'