Közvetlen SQL

A Nette adatbázissal kétféleképpen dolgozhat: közvetlenül SQL-lekérdezéseket írhat (közvetlen hozzáférés) vagy hagyhatja, hogy az SQL automatikusan generálódjon(felfedező hozzáférés). A közvetlen hozzáférés lehetővé teszi a lekérdezések biztonságos létrehozását, miközben teljes mértékben ellenőrizheti azok szerkezetét.

A kapcsolat létrehozására és konfigurálására vonatkozó információkat lásd a külön oldalon.

Alapvető lekérdezés

A query() metódus adatbázis-lekérdezéseket hajt végre, és az eredményt reprezentáló ResultSet objektumot ad vissza. Ha a lekérdezés sikertelen, a metódus kivételt dob. A lekérdezés eredményét a foreach hurok segítségével vagy a segédfüggvények valamelyikének használatával végighaladhat a lekérdezés eredményén.

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

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

Az értékek SQL-lekérdezésekbe történő biztonságos beszúrásához használjon paraméterezett lekérdezéseket. A Nette Database ezt nagyon egyszerűvé teszi: csak egy vesszőt és az értéket kell az SQL-lekérdezéshez csatolni.

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

Több paraméter esetén az SQL-lekérdezést vagy közbeiktathatja a paraméterekkel:

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

Vagy írja meg először a teljes SQL-lekérdezést, majd csatolja az összes paramétert:

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

SQL Injection elleni védelem

Miért fontos a paraméterezett lekérdezések használata? Mert megvédik az SQL-injekciós támadásoktól, amelyek során a támadók rosszindulatú SQL-parancsokat juttathatnak be az adatbázis adatainak manipulálása vagy az azokhoz való hozzáférés érdekében.

Soha ne illesszen be változókat közvetlenül egy SQL-lekérdezésbe! Mindig használjon paraméterezett lekérdezéseket, hogy megvédje magát az SQL injekcióval szemben.

// ❌ UNSAFE CODE - SQL injekcióval sebezhető
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Biztonságos paraméteres lekérdezés
$database->query('SELECT * FROM users WHERE name = ?', $name);

Győződjön meg róla, hogy megismerkedett a lehetséges biztonsági kockázatokkal.

Lekérdezési technikák

WHERE feltételek

A WHERE feltételeket asszociatív tömbként is megírhatja, ahol a kulcsok az oszlopnevek, az értékek pedig az összehasonlítandó adatok. A Nette Database automatikusan kiválasztja a legmegfelelőbb SQL-operátort az érték típusa alapján.

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

Az operátort kifejezetten is megadhatja a kulcsban:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,           // a > operátort használja
	'name LIKE' => '%John%', // a LIKE operátort használja
	'email NOT LIKE' => '%example.com%', // a NOT LIKE operátort használja
]);
// WHERE "age" > 25 AND "name" LIKE '%John%' AND "email" NOT LIKE '%example.com%'

Az olyan speciális eseteket, mint a null értékek vagy tömbök, a rendszer automatikusan kezeli:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // az = operátort használja
	'category_id' => [1, 2, 3], // IN-t használ
	'description' => null,      // IS NULL-t használ
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

A negatív feltételek esetén használja a NOT operátort:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // a <> operátort használja
	'category_id NOT' => [1, 2, 3], // NOT IN-t használ
	'description NOT' => null,      // IS NOT NULL operátort használ
	'id' => [],                     // kihagyta
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Alapértelmezés szerint a feltételek kombinálása a AND operátorral történik. Ezt a viselkedést a ?or helyőrzővel változtathatja meg.

ORDER BY szabályok

A ORDER BY záradék definiálható tömbként, ahol a kulcsok oszlopokat, az értékek pedig növekvő sorrendet jelző bólusokat jelentenek:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true,  // felmenő
	'name' => false, // ereszkedő
]);
// SELECT id FROM szerző ORDER BY `id`, `név` DESC

Adatok beszúrása (INSERT)

Rekordok beszúrásához használja az SQL INSERT utasítást.

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

A getInsertId() módszer az utoljára beillesztett sor azonosítóját adja vissza. Bizonyos adatbázisok (pl. PostgreSQL) esetében a $database->getInsertId($sequenceId) segítségével meg kell adnia a sorszámnevet.

Speciális értékeket, például fájlokat, DateTime objektumokat vagy enum típusokat is átadhat paraméterként.

Több rekord egyszerre történő beszúrása:

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

A kötegelt INSERT végrehajtása sokkal gyorsabb, mivel több egyedi lekérdezés helyett csak egyetlen adatbázis-lekérdezés kerül végrehajtásra.

Biztonsági megjegyzés: Soha ne használjon érvénytelenített adatokat a $values címen. Ismerkedjen meg a lehetséges kockázatokkal.

Az adatok frissítése (UPDATE)

A rekordok frissítéséhez használja az SQL UPDATE utasítást.

// Egyetlen rekord frissítése
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Az érintett sorok számát a $result->getRowCount() segítségével ellenőrizheti.

A += és a -= operátorokat a UPDATE oldalon használhatja:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // login_count növelése
], 1);

A ON DUPLICATE KEY UPDATE technikát használhatja egy rekord beszúrásához vagy frissítéséhez, ha az már létezik:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `név`, `év`) VALUES (123, 'Jim', 1978)
//  ON DUPLICATE KEY UPDATE `név` = 'Jim', `év` = 1978

Vegye figyelembe, hogy a Nette Database felismeri az SQL-parancs kontextusát, amelyben egy tömböt tartalmazó paramétert használ, és ennek megfelelően generálja az SQL-kódot. Például az első tömbből a (id, name, year) VALUES (123, 'Jim', 1978) címet konstruálta, míg a másodikat a name = 'Jim', year = 1978 címmé alakította. Erről részletesebben a Hints az SQL konstruálásához című részben olvashat.

Adatok törlése (DELETE)

A rekordok törléséhez használja az SQL DELETE utasítást. Példa a törölt sorok számával:

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

SQL építési tippek

Az SQL-helyettesítők lehetővé teszik annak szabályozását, hogy a paraméterértékek hogyan épüljenek be az SQL-kifejezésekbe:

Hint	Leírás	Automatikusan használva a következőkhöz
`?name`	Tábla- vagy oszlopneveknél használatos	– –
`?values`	Generálja a `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Hozzárendeléseket generál `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Összekapcsolja a feltételeket egy tömbben a `AND`	`WHERE ?`, `HAVING ?`
`?or`	Összekapcsolja a feltételeket egy tömbben a `OR`	–
`?order`	Generálja a `ORDER BY` záradékot	`ORDER BY ?`, `GROUP BY ?`

A táblázat- vagy oszlopnevek dinamikus beillesztéséhez használja a ?name helyőrzőt. A Nette Database gondoskodik az adatbázis konvencióinak megfelelő escapingről (pl. a MySQL esetében a backtickekbe zárás).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `név` FROM `felhasználók` WHERE id = 1 (MySQL-ben)

Figyelmeztetés: Kizárólag a ?name helyőrzőt használja az érvényesített tábla- és oszlopnevekhez. Ellenkező esetben biztonsági réseket kockáztat.

Egyéb tippeket általában nem szükséges megadni, mivel a Nette intelligens automatikus felismerést használ az SQL-lekérdezések szerkesztésekor (lásd a táblázat harmadik oszlopát). Használhatja őket azonban olyan helyzetekben, amikor a AND helyett a OR használatával kívánja a feltételeket kombinálni:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `név` = 'John' OR `email` = 'john@example.com'

Különleges értékek

A szabványos skalár típusok (pl. string, int, bool) mellett speciális értékeket is átadhat paraméterként:

Fájlok: A fopen('file.png', 'r') segítségével egy fájl bináris tartalmát illesztheti be.
Dátum és idő: A DateTime objektumok automatikusan az adatbázis dátumformátumára konvertálódnak.
Enum értékek: A enum példányai a megfelelő értékekké konvertálódnak.
SQL literálok: A Connection::literal('NOW()') segítségével létrehozva, ezek közvetlenül a lekérdezésbe kerülnek beillesztésre.

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

Az olyan adatbázisok esetében, amelyek nem támogatják natívan a datetime típust (pl. SQLite és Oracle), a DateTime értékek a formatDateTime konfigurációs opciónak megfelelően kerülnek átalakításra (alapértelmezett: U Unix timestamp esetén).

SQL irodalmi karakterek

Bizonyos esetekben előfordulhat, hogy nyers SQL-kódot kell értékként beillesztenie anélkül, hogy azt karakterláncként kezelné vagy szcenírozná. Ehhez használjuk a Nette\Database\SqlLiteral osztály objektumait, amelyeket a Connection::literal() metódussal hozhatunk létre.

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

Alternatívaként:

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

Az SQL literálok paramétereket is tartalmazhatnak:

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

Ez rugalmas kombinációkat tesz lehetővé:

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

Adatok lekérése

SELECT lekérdezések rövidítései

Az adatok lekérdezésének egyszerűsítése érdekében a Connection osztály több olyan gyorsbillentyűt biztosít, amelyek a query() hívást egy későbbi fetch*() hívással kombinálják. Ezek a módszerek ugyanazokat a paramétereket fogadják el, mint a query(), azaz egy SQL-lekérdezést és opcionális paramétereket. A fetch*() metódusok részletes leírása alább található.

`fetch($sql, ...$params): ?Row`	A lekérdezés végrehajtása és az első sor lekérdezése `Row` objektumként.
`fetchAll($sql, ...$params): array`	Végrehajtja a lekérdezést, és az összes sort `Row` objektumok tömbjeként hozza ki.
`fetchPairs($sql, ...$params): array`	A lekérdezés végrehajtása és egy asszociatív tömb kinyerése, ahol az első oszlop a kulcs, a második pedig az érték.
`fetchField($sql, ...$params): mixed`	A lekérdezés végrehajtása és az első sor első cellájának értékének lekérdezése.
`fetchList($sql, ...$params): ?array`	A lekérdezés végrehajtása és az első sor indexelt tömbként történő lekérdezése.

Példa:

// fetchField() - az első cella értékét adja vissza
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – Sorokon való ismétlés

A lekérdezés végrehajtása után egy ResultSet objektumot kapunk vissza, amely lehetővé teszi, hogy különböző módon végigmenjünk az eredményeken. A sorok lekérdezésének legegyszerűbb és memóriahatékonyabb módja a foreach ciklusban történő iterálás. Ez a módszer egyesével dolgozza fel a sorokat, és elkerüli az összes adat egyszerre történő tárolását a memóriában.

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

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

A ResultSet csak egyszer iterálható. Ha többször kell iterálni rajta, akkor először be kell tölteni az adatokat egy tömbbe, például a fetchAll() módszerrel.

fetch(): ?Row

Végrehajtja a lekérdezést, és egyetlen sort hív le a Row objektumként. Ha nem áll rendelkezésre több sor, akkor a null objektumot adja vissza. Ez a módszer a belső mutatót a következő sorra továbbítja.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // az első sort hozza le
if ($row) {
	echo $row->name;
}

fetchAll(): array

A ResultSet összes fennmaradó sorát a Row objektumok tömbjeként hívja le.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // az összes sort lekérdezi
foreach ($rows as $row) {
	echo $row->name;
}

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

Az eredmények asszociatív tömbként való lekérdezése. Az első argumentum a kulcsként használandó oszlopot, a második pedig az értékként használandó oszlopot adja meg:

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

Ha csak az első paramétert adjuk meg, az érték a teljes sor lesz ( Row objektumként):

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

Ha kulcsként a null értéket adjuk meg, akkor a tömböt nullától kezdődően numerikusan indexeljük:

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

fetchPairs (Closure $callback): array

Alternatívaként megadhat egy visszahívást, amely meghatározza az egyes sorok kulcs-érték párosait vagy értékeit.

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

// A visszahívás egy kulcs- és értékpárral rendelkező tömböt is visszaadhat:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Az aktuális sor első cellájának értékét kéri ki. Ha nincs több sor, akkor a null értéket adja vissza. Ez a módszer a belső mutatót a következő sorra továbbítja.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // az első sorban szereplő nevet hívja le

fetchList(): ?array

A sort indexelt tömbként hívja le. Ha nem áll rendelkezésre több sor, akkor a null értéket adja vissza. Ez a módszer a belső mutatót a következő sorra továbbítja.

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

getRowCount(): ?int

Visszaadja az utolsó UPDATE vagy DELETE lekérdezés által érintett sorok számát. A SELECT lekérdezések esetén a lekérdezett sorok számát adja vissza, de ez nem mindig ismert – ilyen esetekben a null értéket adja vissza.

getColumnCount(): ?int

Visszaadja az oszlopok számát a ResultSet oldalon.

Lekérdezési információ

A legutóbb végrehajtott lekérdezés részleteinek lekérdezéséhez használja a következőt:

echo $database->getLastQueryString(); // kiadja az SQL lekérdezést

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // kiadja az SQL-lekérdezést
echo $result->getTime();           // a végrehajtási idő másodpercben kifejezve

Az eredmény HTML-táblázatként való megjelenítéséhez használja a következőt: A lekérdezés eredményének HTML-táblázatként való megjelenítéséhez használja a következőt:

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

Az oszloptípusokra vonatkozó információkat a ResultSet oldalon is lekérdezheti:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // pl.: 'id az int típusú'
}

Lekérdezés naplózása

Egyéni lekérdezésnaplózást is megvalósíthat. A onQuery esemény egy olyan visszahívásokból álló tömb, amely minden egyes lekérdezés végrehajtása után meghívásra kerül:

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