Abordarea SQL

Nette Database oferă două abordări: puteți scrie interogări SQL singur (abordarea SQL) sau le puteți lăsa generate automat (vezi Explorer). Abordarea SQL vă oferă control complet asupra interogărilor și, în același timp, asigură construirea lor în siguranță.

Detalii despre conectarea și configurarea bazei de date găsiți în capitolul Conectare și configurare.

Interogare de bază

Pentru interogarea bazei de date se folosește metoda query(). Aceasta returnează un obiect ResultSet, care reprezintă rezultatul interogării. În caz de eșec, metoda aruncă o excepție. Putem parcurge rezultatul interogării folosind bucla foreach sau putem folosi una dintre funcțiile auxiliare.

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

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

Pentru inserarea sigură a valorilor în interogările SQL, folosim interogări parametrizate. Nette Database le face extrem de simple – trebuie doar să adăugați o virgulă și valoarea după interogarea SQL:

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

Pentru mai mulți parametri, aveți două opțiuni de scriere. Fie puteți „intercala“ interogarea SQL cu parametri:

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

Fie scrieți mai întâi întreaga interogare SQL și apoi adăugați toți parametrii:

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

Protecție împotriva SQL injection

De ce este important să folosim interogări parametrizate? Deoarece vă protejează împotriva atacului numit SQL injection, în care un atacator ar putea introduce propriile comenzi SQL și astfel să obțină sau să deterioreze datele din baza de date.

Nu introduceți niciodată variabile direct în interogarea SQL! Folosiți întotdeauna interogări parametrizate, care vă protejează împotriva SQL injection.

// ❌ COD PERICULOS - vulnerabil la SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Interogare parametrizată sigură
$database->query('SELECT * FROM users WHERE name = ?', $name);

Familiarizați-vă cu posibilele riscuri de securitate.

Tehnici de interogare

Condiții WHERE

Condițiile WHERE pot fi scrise ca un array asociativ, unde cheile sunt numele coloanelor și valorile sunt datele pentru comparație. Nette Database selectează automat operatorul SQL cel mai potrivit în funcție de tipul valorii.

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

În cheie, puteți specifica explicit și operatorul pentru comparație:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // folosește operatorul >
	'name LIKE' => '%John%', // folosește operatorul LIKE
	'email NOT LIKE' => '%example.com%', // folosește operatorul NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Nette tratează automat cazurile speciale precum valorile null sau array-urile.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // folosește operatorul =
	'category_id' => [1, 2, 3], // folosește IN
	'description' => null,      // folosește IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Pentru condiții negative, utilizați operatorul NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // folosește operatorul <>
	'category_id NOT' => [1, 2, 3], // folosește NOT IN
	'description NOT' => null,      // folosește IS NOT NULL
	'id' => [],                     // se omite
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Pentru combinarea condițiilor se folosește operatorul AND. Acest lucru poate fi schimbat folosind substituentul ?or.

Reguli ORDER BY

Sortarea ORDER BY poate fi scrisă folosind un array. În chei specificăm coloanele, iar valoarea va fi un boolean care determină dacă se sortează ascendent:

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

Inserarea datelor (INSERT)

Pentru inserarea înregistrărilor se folosește comanda SQL INSERT.

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

Metoda getInsertId() returnează ID-ul ultimului rând inserat. Pentru unele baze de date (de ex. PostgreSQL), este necesar să specificați ca parametru numele secvenței din care trebuie generat ID-ul folosind $database->getInsertId($sequenceId).

Ca parametri putem transmite și valori speciale precum fișiere, obiecte DateTime sau tipuri enum.

Inserarea mai multor înregistrări simultan:

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

INSERT-ul multiplu este mult mai rapid, deoarece se execută o singură interogare la baza de date, în loc de multe interogări individuale.

Avertisment de securitate: Nu utilizați niciodată date nevalidate ca $values. Familiarizați-vă cu posibilele riscuri.

Actualizarea datelor (UPDATE)

Pentru actualizarea înregistrărilor se folosește comanda SQL UPDATE.

// Actualizarea unei singure înregistrări
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Numărul de rânduri afectate este returnat de $result->getRowCount().

Pentru UPDATE putem folosi operatorii += și -=:

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

Exemplu de inserare sau modificare a unei înregistrări, dacă aceasta există deja. Folosim tehnica 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

Observați că Nette Database recunoaște în ce context al comenzii SQL este inserat parametrul cu array-ul și, în funcție de aceasta, construiește codul SQL din el. Astfel, din primul array a construit (id, name, year) VALUES (123, 'Jim', 1978), în timp ce al doilea l-a convertit în forma name = 'Jim', year = 1978. Detaliem acest aspect în secțiunea Indicații pentru construirea SQL.

Ștergerea datelor (DELETE)

Pentru ștergerea înregistrărilor se folosește comanda SQL DELETE. Exemplu cu obținerea numărului de rânduri șterse:

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

Indicații pentru construirea SQL

O indicație este un substituent special în interogarea SQL care specifică modul în care valoarea parametrului trebuie rescrisă într-o expresie SQL:

Indicație	Descriere	Se utilizează automat
`?name`	se utilizează pentru inserarea numelui tabelului sau coloanei	–
`?values`	generează `(cheie, ...) VALUES (valoare, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	generează atribuirea `cheie = valoare, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	combină condițiile din array cu operatorul `AND`	`WHERE ?`, `HAVING ?`
`?or`	combină condițiile din array cu operatorul `OR`	–
`?order`	generează clauza `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Pentru inserarea dinamică a numelor de tabele și coloane în interogare se folosește substituentul ?name. Nette Database se ocupă de tratarea corectă a identificatorilor conform convențiilor bazei de date respective (de ex. încadrarea în ghilimele inverse în MySQL).

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

Avertisment: utilizați simbolul ?name numai pentru numele de tabele și coloane din intrări validate, altfel vă expuneți unui risc de securitate.

Celelalte indicații de obicei nu trebuie specificate, deoarece Nette folosește o autodetecție inteligentă la construirea interogării SQL (vezi a treia coloană a tabelului). Dar le puteți utiliza, de exemplu, într-o situație în care doriți să combinați condițiile folosind OR în loc de 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'

Valori speciale

Pe lângă tipurile scalare obișnuite (string, int, bool), puteți transmite ca parametri și valori speciale:

fișiere: fopen('image.gif', 'r') inserează conținutul binar al fișierului
data și ora: obiectele DateTime sunt convertite în formatul bazei de date
tipuri enum: instanțele enum sunt convertite în valoarea lor
literali SQL: creați folosind Connection::literal('NOW()') sunt inserați direct în interogare

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

Pentru bazele de date care nu au suport nativ pentru tipul de date datetime (precum SQLite și Oracle), DateTime este convertit în valoarea specificată în configurația bazei de date prin elementul formatDateTime (valoarea implicită este U – timestamp unix).

Literali SQL

În unele cazuri, trebuie să specificați direct cod SQL ca valoare, care însă nu trebuie interpretat ca șir și escapat. Pentru aceasta se folosesc obiectele clasei Nette\Database\SqlLiteral. Acestea sunt create de 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())

Sau alternativ:

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

Literalii SQL pot conține parametri:

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

Datorită cărora putem crea combinații interesante:

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

Obținerea datelor

Scurtături pentru interogări SELECT

Pentru a simplifica încărcarea datelor, Connection oferă câteva scurtături care combină apelul query() cu următorul fetch*(). Aceste metode acceptă aceiași parametri ca query(), adică interogarea SQL și parametrii opționali. O descriere completă a metodelor fetch*() găsiți mai jos.

`fetch($sql, ...$params): ?Row`	Execută interogarea și returnează primul rând ca obiect `Row`
`fetchAll($sql, ...$params): array`	Execută interogarea și returnează toate rândurile ca array de obiecte `Row`
`fetchPairs($sql, ...$params): array`	Execută interogarea și returnează un array asociativ, unde prima coloană reprezintă cheia și a doua valoarea
`fetchField($sql, ...$params): mixed`	Execută interogarea și returnează valoarea primului câmp din primul rând
`fetchList($sql, ...$params): ?array`	Execută interogarea și returnează primul rând ca array indexat

Exemplu:

// fetchField() - returnează valoarea primei celule
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – iterarea prin rânduri

După executarea interogării, se returnează obiectul ResultSet, care permite parcurgerea rezultatelor în mai multe moduri. Cel mai simplu mod de a executa o interogare și de a obține rânduri este prin iterarea într-o buclă foreach. Această metodă este cea mai eficientă din punct de vedere al memoriei, deoarece returnează datele treptat și nu le stochează pe toate în memorie simultan.

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

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

ResultSet poate fi iterat o singură dată. Dacă aveți nevoie să iterați în mod repetat, trebuie mai întâi să încărcați datele într-un array, de exemplu folosind metoda fetchAll().

fetch(): ?Row

Returnează un rând ca obiect Row. Dacă nu mai există alte rânduri, returnează null. Mută pointerul intern la următorul rând.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // încarcă primul rând
if ($row) {
	echo $row->name;
}

fetchAll(): array

Returnează toate rândurile rămase din ResultSet ca un array de obiecte Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // încarcă toate rândurile
foreach ($rows as $row) {
	echo $row->name;
}

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

Returnează rezultatele ca un array asociativ. Primul argument specifică numele coloanei care va fi folosită ca cheie în array, al doilea argument specifică numele coloanei care va fi folosită ca valoare:

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

Dacă specificăm doar primul parametru, valoarea va fi întregul rând, adică obiectul Row:

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

În cazul cheilor duplicate, se va folosi valoarea din ultimul rând. La utilizarea null ca cheie, array-ul va fi indexat numeric începând de la zero (atunci nu apar coliziuni):

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

fetchPairs(Closure $callback): array

Alternativ, puteți specifica ca parametru un callback care va returna pentru fiecare rând fie valoarea însăși, fie o pereche cheie-valoare.

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

// Callback-ul poate returna și un array cu perechea cheie & valoare:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Returnează valoarea primului câmp din rândul curent. Dacă nu mai există alte rânduri, returnează null. Mută pointerul intern la următorul rând.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // încarcă numele din primul rând

fetchList(): ?array

Returnează un rând ca array indexat. Dacă nu mai există alte rânduri, returnează null. Mută pointerul intern la următorul rând.

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

getRowCount(): ?int

Returnează numărul de rânduri afectate de ultima interogare UPDATE sau DELETE. Pentru SELECT, este numărul de rânduri returnate, dar acesta poate să nu fie cunoscut – în acest caz, metoda returnează null.

getColumnCount(): ?int

Returnează numărul de coloane din ResultSet.

Informații despre interogări

În scopuri de depanare, putem obține informații despre ultima interogare executată:

echo $database->getLastQueryString();   // afișează interogarea SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // afișează interogarea SQL
echo $result->getTime();           // afișează timpul de execuție în secunde

Pentru a afișa rezultatul ca tabel HTML, se poate folosi:

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

ResultSet oferă informații despre tipurile coloanelor:

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

foreach ($types as $column => $type) {
	echo "$column este de tip $type->type"; // de ex. 'id este de tip int'
}

Logarea interogărilor

Putem implementa propria logare a interogărilor. Evenimentul onQuery este un array de callback-uri care sunt apelate după fiecare interogare executată:

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

Nette Documentation Preview