SQL direct

Puteți lucra cu Nette Database în două moduri: scriind interogări SQL direct (Direct Access) sau lăsând SQL să fie generat automat(Explorer Access). Direct Access vă permite să construiți în siguranță interogări, păstrând în același timp controlul deplin asupra structurii acestora.

Pentru informații privind crearea unei conexiuni și configurarea acesteia, consultați pagina separată.

Interogare de bază

Metoda query() execută interogări ale bazei de date și returnează un obiect ResultSet care reprezintă rezultatul. Dacă interogarea eșuează, metoda aruncă o excepție. Puteți parcurge în buclă rezultatul interogării folosind o buclă foreach sau folosind una dintre funcțiile de ajutor.

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

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

Pentru a introduce valori în siguranță în interogările SQL, utilizați interogări parametrizate. Nette Database face acest lucru foarte simplu: trebuie doar să adăugați o virgulă și valoarea la interogarea SQL.

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

Pentru parametrii multipli, puteți fie să intercalați interogarea SQL cu parametrii:

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

Sau 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 injectării SQL

De ce este important să utilizați interogări parametrizate? Pentru că vă protejează de atacurile de tip SQL injection, în care atacatorii pot injecta comenzi SQL rău intenționate pentru a manipula sau a accesa datele din baza de date.

Niciodată nu introduceți variabile direct într-o interogare SQL! Utilizați întotdeauna interogări parametrizate pentru a vă proteja împotriva injecțiilor SQL.

// ❌ UNSAFE CODE - vulnerabil la injectarea SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Asigurați-vă că vă familiarizați cu potențialele riscuri de securitate.

Tehnici de interogare

Condiții WHERE

Puteți scrie condițiile WHERE ca un array asociativ, în care cheile sunt numele coloanelor, iar valorile sunt datele de comparat. Nette Database selectează automat cel mai adecvat operator SQL în funcție de tipul valorii.

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

De asemenea, puteți specifica explicit operatorul în cheie:

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

Cazurile speciale, cum ar fi valorile null sau matricele, sunt gestionate automat:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // utilizează operatorul =
	'category_id' => [1, 2, 3], // utilizează IN
	'description' => null,      // uses 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',         // utilizează operatorul <>
	'category_id NOT' => [1, 2, 3], // utilizează NOT IN
	'description NOT' => null,      // uses IS NOT NULL
	'id' => [],                     // sărit
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

În mod implicit, condițiile sunt combinate utilizând operatorul AND. Puteți schimba acest comportament utilizând marcajul ?or.

ORDER BY Reguli

Clauza ORDER BY poate fi definită ca un array, unde cheile reprezintă coloane, iar valorile sunt booleeni care indică ordinea crescătoare:

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

Inserarea datelor (INSERT)

Pentru a introduce înregistrări, utilizați instrucțiunea 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 introdus. Pentru anumite baze de date (de exemplu, PostgreSQL), trebuie să specificați numele secvenței utilizând $database->getInsertId($sequenceId).

De asemenea, puteți trece valori speciale, cum ar fi fișiere, obiecte DateTime sau tipuri enum, ca parametri.

Inserarea simultană a mai multor înregistrări:

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

Efectuarea unei INSERTĂRI pe loturi este mult mai rapidă deoarece se execută o singură interogare a bazei de date în loc de mai multe interogări individuale.

Nota de securitate: Nu utilizați niciodată date nevalidate ca $values. Familiarizați-vă cu riscurile posibile.

Actualizarea datelor (UPDATE)

Pentru a actualiza înregistrările, utilizați instrucțiunea SQL UPDATE.

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

Puteți verifica numărul de rânduri afectate utilizând $result->getRowCount().

Puteți utiliza operatorii += și -= în UPDATE:

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

Pentru a introduce sau actualiza o înregistrare dacă aceasta există deja, utilizați 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

Rețineți că Nette Database recunoaște contextul comenzii SQL în care este utilizat un parametru cu un array și generează codul SQL în consecință. De exemplu, a construit (id, name, year) VALUES (123, 'Jim', 1978) din primul array, în timp ce l-a convertit pe al doilea în name = 'Jim', year = 1978. Acest aspect este tratat mai detaliat în secțiunea Sfaturi pentru construirea SQL.

Ștergerea datelor (DELETE)

Pentru a șterge înregistrări, utilizați instrucțiunea SQL DELETE. Exemplu cu numărul de rânduri șterse:

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

Sugestii de construcție SQL

Lockerele SQL vă permit să controlați modul în care valorile parametrilor sunt încorporate în expresiile SQL:

Indicație	Descriere	Folosit automat pentru
`?name`	Folosit pentru nume de tabele sau coloane	–
`?values`	Generează `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Generează atribuiri `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Unește condițiile dintr-un array cu `AND`	`WHERE ?`, `HAVING ?`
`?or`	Unește condițiile într-un array cu `OR`	–
`?order`	Generează clauza `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Pentru inserarea dinamică a numelor de tabele sau coloane, utilizați marcajul ?name. Nette Database asigură scăparea corectă în conformitate cu convențiile bazei de date (de exemplu, includerea în backticks pentru 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: Folosiți marcajul ?name numai pentru numele validate ale tabelelor și coloanelor. În caz contrar, riscați vulnerabilități de securitate.

De obicei, nu este necesar să specificați alte indicii, deoarece Nette utilizează detectarea automată inteligentă atunci când construiește interogări SQL (a se vedea a treia coloană din tabel). Cu toate acestea, le puteți utiliza în situațiile în care doriți să combinați condițiile utilizând 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

În plus față de tipurile scalare standard (de exemplu, string, int, bool), puteți trece și valori speciale ca parametri:

Fișiere: Utilizați fopen('file.png', 'r') pentru a introduce conținutul binar al unui fișier.
Data și ora: Obiectele DateTime sunt convertite automat în formatul de dată al bazei de date.
Valori enum: Instanțele din enum sunt convertite în valorile corespunzătoare.
Literale SQL: Create utilizând Connection::literal('NOW()'), acestea sunt inserate 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 oferă suport nativ pentru tipul datetime (de exemplu, SQLite și Oracle), valorile DateTime sunt convertite în conformitate cu opțiunea de configurare formatDateTime (implicit: U pentru timestamp Unix).

Literali SQL

În unele cazuri, poate fi necesar să introduceți cod SQL brut ca valoare fără a-l trata ca pe un șir de caractere sau fără a-l scăpa. Pentru aceasta, utilizați obiecte din clasa Nette\Database\SqlLiteral, care pot fi create utilizând 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())

Alternativ:

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

Literalele SQL pot conține și parametri:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year &lt; ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (an > 1978 AND an < 2017)

Acest lucru permite combinații flexibile:

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

Preluarea datelor

Scurtături pentru interogările SELECT

Pentru a simplifica recuperarea datelor, clasa Connection oferă mai multe scurtături care combină un apel la query() cu un apel ulterior la fetch*(). Aceste metode acceptă aceiași parametri ca și query(), și anume o interogare SQL și parametri opționali. O descriere detaliată a metodelor fetch*() poate fi găsită mai jos.

`fetch($sql, ...$params): ?Row`	Execută interogarea și extrage primul rând ca obiect `Row`.
`fetchAll($sql, ...$params): array`	Execută interogarea și extrage toate rândurile sub forma unei serii de obiecte `Row`.
`fetchPairs($sql, ...$params): array`	Execută interogarea și extrage un array asociativ în care prima coloană este cheia, iar a doua este valoarea.
`fetchField($sql, ...$params): mixed`	Execută interogarea și extrage valoarea primei celule din primul rând.
`fetchList($sql, ...$params): ?array`	Execută interogarea și extrage primul rând ca un array indexat.

Exemplu:

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

`foreach` – Iterarea peste rânduri

După executarea unei interogări, este returnat un obiect ResultSet, care vă permite să iterați peste rezultate în diverse moduri. Cel mai simplu și mai eficient mod de a prelua rânduri din memorie este prin iterarea într-o buclă foreach. Această metodă procesează rândurile unul câte unul și evită stocarea simultană a tuturor datelor în memorie.

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

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

Rețeaua ResultSet poate fi iterată o singură dată. Dacă trebuie să o iterați de mai multe ori, trebuie mai întâi să încărcați datele într-un array, de exemplu, utilizând metoda fetchAll().

fetch(): ?Row

Execută interogarea și extrage un singur rând ca obiect Row. Dacă nu mai sunt disponibile alte rânduri, se returnează null. Această metodă avansează pointerul intern la rândul următor.

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

fetchAll(): array

Preia toate rândurile rămase din ResultSet sub forma unei serii de obiecte Row.

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

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

Preia rezultatele sub forma unui array asociativ. Primul argument specifică coloana care urmează să fie utilizată ca cheie, iar al doilea specifică coloana care urmează să fie utilizată ca valoare:

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

Dacă este furnizat doar primul parametru, valoarea va fi întregul rând (ca obiect Row ):

$rows = $result->fetchPairs('id');
// [1 => rând (id: 1, nume: "John"), 2 => rând (id: 2, nume: "Jane"), ...]

Dacă null este trecut ca cheie, matricea va fi indexată numeric începând de la zero:

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

fetchPairs (Closure $callback): array

Alternativ, puteți furniza un callback care determină perechile cheie-valoare sau valorile pentru fiecare rând.

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

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

fetchField(): mixed

Preia valoarea primei celule din rândul curent. Dacă nu mai sunt disponibile alte rânduri, se returnează null. Această metodă avansează pointerul intern la rândul următor.

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

fetchList(): ?array

Preia rândul ca un array indexat. Dacă nu mai sunt disponibile alte rânduri, se returnează null. Această metodă avansează pointerul intern la rândul următor.

$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 interogările SELECT, se returnează numărul de rânduri extrase, dar acesta nu poate fi întotdeauna cunoscut – în astfel de cazuri, se returnează null.

getColumnCount(): ?int

Returnează numărul de coloane din ResultSet.

Informații despre interogare

Pentru a obține detalii despre cea mai recentă interogare executată, utilizați:

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 un tabel HTML, utilizați:

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

De asemenea, puteți obține informații despre tipurile de coloane din ResultSet:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // de exemplu, "id este de tip int
}

Înregistrarea interogărilor

Puteți implementa înregistrarea interogărilor personalizate. Evenimentul onQuery este o serie de callback-uri care sunt invocate după fiecare execuție a interogării:

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