Database Explorer

Az Explorerrel való munka a Nette\Database\Explorer objektum table() metódusának meghívásával kezdődik (a kapcsolatok létrehozásával és konfigurálásával kapcsolatos információkért lásd a külön erre a célra szolgáló oldalt):

$books = $explorer->table('book'); // 'book' a táblázat neve

A módszer egy Selection objektumot ad vissza, amely egy SQL-lekérdezést reprezentál. Az eredmények szűréséhez és rendezéséhez további metódusok kapcsolhatók ehhez az objektumhoz. A lekérdezés csak akkor áll össze és hajtódik végre, amikor az adatokat kérik, például a foreach iterálásával. Minden sort egy ActiveRow objektum képvisel:

foreach ($books as $book) {
	echo $book->title;        // a "title" oszlop kimenete
	echo $book->author_id;    // kimenet 'author_id' oszlop
}

Az Explorer jelentősen leegyszerűsíti a táblázatos kapcsolatokkal való munkát. A következő példa azt mutatja, hogy milyen egyszerűen adhatunk ki adatokat a kapcsolódó táblákból (könyvek és szerzőik). Vegyük észre, hogy nem kell JOIN-lekérdezéseket írni; a Nette generálja ezeket helyettünk:

$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // JOIN-t hoz létre a 'author' táblához
}

A Nette Database Explorer optimalizálja a lekérdezéseket a maximális hatékonyság érdekében. A fenti példa csak két SELECT-lekérdezést hajt végre, függetlenül attól, hogy 10 vagy 10 000 könyvet dolgozunk fel.

Ezenkívül az Explorer nyomon követi, hogy a kódban mely oszlopokat használjuk, és csak azokat hívja le az adatbázisból, ami további teljesítményt takarít meg. Ez a viselkedés teljesen automatikus és adaptív. Ha később módosítjuk a kódot, hogy további oszlopokat használjunk, az Explorer automatikusan módosítja a lekérdezéseket. Nem kell semmit sem konfigurálnia, vagy gondolkodnia azon, hogy mely oszlopokra lesz szükség – ezt bízza a Nette-re.

Szűrés és rendezés

A Selection osztály metódusokat biztosít az adatok szűrésére és rendezésére.

A metódusok láncolhatók (ún. folyékony interfész): $table->where(...)->order(...)->limit(...).

Ezek a módszerek speciális jelölések használatát is lehetővé teszik a kapcsolódó táblák adatainak eléréséhez.

Kikerülés és azonosítók

A módszerek automatikusan kikerülik a paramétereket és az idézőjeles azonosítókat (táblázat- és oszlopnevek), megakadályozva ezzel az SQL injektálást. A megfelelő működés biztosítása érdekében néhány szabályt be kell tartani:

• A kulcsszavakat, függvényneveket, eljárásokat stb. uppercase-ben írjuk.
• Az oszlop- és táblázatok neveit kisbetűvel írja.
• Mindig paraméterek használatával adjon át karakterláncokat.

where('name = ' . $name);         // **DISASTER**: sebezhető SQL injekcióval szemben
where('name LIKE "%search%"');    // **WRONG**: megnehezíti az automatikus idézést
where('name LIKE ?', '%search%'); // **CORRECT**: paraméterként átadott érték

where('name like ?', $name);     // **HIBA**: generál: `név` `mint` ?
where('name LIKE ?', $name);     // **CORRECT**: generál: `név` LIKE ?
where('LOWER(name) = ?', $value);// **TÖRVÉNYES**: LOWER(`név`) = ?

where (string|array $condition, …$parameters): static

Az eredmények szűrése WHERE feltételekkel. Erőssége a különböző értéktípusok intelligens kezelésében és az SQL-operátorok automatikus kiválasztásában rejlik.

Alapvető használat:

$table->where('id', $value);     // WHERE `id` = 123
$table->where('id > ?', $value); // WHERE `id` > 123
$table->where('id = ? OR name = ?', $id, $name); // WHERE `id` = 1 OR `name` = 'Jon Snow'

A megfelelő operátorok automatikus felismerésének köszönhetően nem kell speciális eseteket kezelnie – a Nette kezeli ezeket Ön helyett:

$table->where('id', 1);          // WHERE `id` = 1
$table->where('id', null);       // WHERE `id` IS NULL
$table->where('id', [1, 2, 3]);  // WHERE `id` IN (1, 2, 3)
// A helyőrző ? operátor nélkül is használható:
$table->where('id ?', 1);        // WHERE `id` = 1

A módszer a negatív feltételeket és az üres tömböket is helyesen kezeli:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- nem talál semmit
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- mindent megtalál
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- mindent megtalál.
// $table->where('NOT id ?', $ids); // FIGYELEM: Ez a szintaxis nem támogatott.

Egy másik táblázat lekérdezésének eredményét is átadhatja paraméterként, al-lekérdezést létrehozva:

// WHERE `id` IN (SELECT `id` FROM `tableName`)
$table->where('id', $explorer->table($tableName));

// WHERE `id` IN (SELECT `col` FROM `tableName`)
$table->where('id', $explorer->table($tableName)->select('col'));

A feltételek tömbként is átadhatók, az elemeket pedig AND segítségével kombinálhatjuk:

// WHERE (`ár_végleges` < `ár_eredeti`) AND (`készlet_szám` > `min_készlet`)
$table->where([
	'price_final &lt; price_original',
	'stock_count > min_stock',
]);

A tömbben kulcs-érték párokat lehet használni, és a Nette ismét automatikusan kiválasztja a megfelelő operátorokat:

// WHERE (`status` = 'active') AND (`id` IN (1, 2, 3))
$table->where([
	'status' => 'active',
	'id' => [1, 2, 3],
]);

SQL-kifejezéseket is keverhetünk helyőrzővel és több paraméterrel. Ez a pontosan meghatározott operátorokkal rendelkező összetett feltételeknél hasznos:

// WHERE (`életkor` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // két paramétert adunk át tömbként
]);

A where() többszöri hívása automatikusan kombinálja a feltételeket az AND segítségével.

whereOr (array $parameters): static

Hasonló a where()-hoz, de a feltételeket OR segítségével kombinálja:

// WHERE (`status` = 'active') OR (`deleted` = 1)
$table->whereOr([
	'status' => 'active',
	'deleted' => true,
]);

Bonyolultabb kifejezések is használhatók:

// WHERE (`ár` > 1000) OR (`ár_adóval együtt` > 1500)
$table->whereOr([
	'price > ?' => 1000,
	'price_with_tax > ?' => 1500,
]);

wherePrimary (mixed $key): static

Feltételt ad a táblázat elsődleges kulcsához:

// WHERE `id` = 123
$table->wherePrimary(123);

// WHERE `id` IN (1, 2, 3)
$table->wherePrimary([1, 2, 3]);

Ha a táblának összetett elsődleges kulcsa van (pl. foo_id, bar_id), akkor azt tömbként adjuk át:

// WHERE `foo_id` = 1 AND `bar_id` = 5
$table->wherePrimary(['foo_id' => 1, 'bar_id' => 5])->fetch();

// WHERE (`foo_id`, `bar_id`) IN ((1, 5), (2, 3))
$table->wherePrimary([
	['foo_id' => 1, 'bar_id' => 5],
	['foo_id' => 2, 'bar_id' => 3],
])->fetchAll();

order (string $columns, …$parameters): static

Megadja a sorok visszaadási sorrendjét. Rendezhet egy vagy több oszlop szerint, növekvő vagy csökkenő sorrendben, vagy egyéni kifejezéssel:

$table->order('created');                   // ORDER BY "kreált
$table->order('created DESC');              // ORDER BY `created` DESC
$table->order('priority DESC, created');    // ORDER BY `prioritás` DESC, `létrehozva`
$table->order('status = ? DESC', 'active'); // ORDER BY `status` = 'active' DESC

select (string $columns, …$parameters): static

Megadja az adatbázisból visszaadandó oszlopokat. Alapértelmezés szerint a Nette Database Explorer csak a kódban ténylegesen használt oszlopokat adja vissza. Használja a select() módszert, ha konkrét kifejezések lekérdezésére van szüksége:

// SELECT *, DATE_FORMAT(`created_at`, "%d.%m.%Y") AS `formatted_date` (formázott dátum)
$table->select('*, DATE_FORMAT(created_at, ?) AS formatted_date', '%d.%m.%Y');

A AS segítségével definiált aliasok ezután a ActiveRow objektum tulajdonságaiként érhetők el:

foreach ($table as $row) {
	echo $row->formatted_date;   // hozzáférés az aliashoz
}

limit (?int $limit, ?int $offset = null): static

Korlátozza a visszaadott sorok számát (LIMIT) és opcionálisan beállít egy eltolást:

$table->limit(10);        // LIMIT 10 (az első 10 sort adja vissza)
$table->limit(10, 20);    // LIMIT 10 OFFSET 20

Oldalszámozáshoz célszerűbb a page() módszert használni.

page (int $page, int $itemsPerPage, &$numOfPages = null): static

Egyszerűsíti az eredmények oldalszámozását. Elfogadja az oldalszámot (1-től kezdődően) és az oldalankénti elemek számát. Opcionálisan átadhat egy hivatkozást egy változóra, ahol az oldalak teljes száma tárolódik:

$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, $numOfPages);
echo "Total pages: $numOfPages";

group (string $columns, …$parameters): static

Csoportosítja a sorokat a megadott oszlopok szerint (GROUP BY). Általában aggregáló függvényekkel együtt használják:

// Megszámolja az egyes kategóriákba tartozó termékek számát
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');

having (string $having, …$parameters): static

Feltételt állít be a csoportosított sorok szűrésére (HAVING). A group() módszerrel és az aggregáló függvényekkel együtt használható:

// 100-nál több terméket tartalmazó kategóriák keresése
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Adatok leolvasása

Az adatok adatbázisból való kiolvasásához több hasznos módszer áll rendelkezésre:

Az ActiveRow objektum csak olvasható. Ez azt jelenti, hogy a tulajdonságainak értékeit nem lehet megváltoztatni. Ez a korlátozás biztosítja az adatok konzisztenciáját és megakadályozza a váratlan mellékhatásokat. Az adatok az adatbázisból kerülnek lekérdezésre, és minden változtatást kifejezetten és ellenőrzött módon kell végrehajtani.

foreach – Az összes soron való ismétlés

A lekérdezés végrehajtásának és a sorok kinyerésének legegyszerűbb módja az iterálás a foreach ciklus segítségével. Ez automatikusan végrehajtja az SQL-lekérdezést.

$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key = elsődleges kulcs, $book = ActiveRow
	echo "$book->title ({$book->author->name})";
}

get ($key): ?ActiveRow

Végrehajt egy SQL-lekérdezést, és visszaad egy sort az elsődleges kulcs alapján, vagy ha nem létezik, akkor a null címen.

$book = $explorer->table('book')->get(123); // visszatér ActiveRow azonosítóval 123 vagy null
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Visszaad egy sort, és a belső mutatót a következő sorra továbbítja. Ha nincs több sor, akkor visszaadja a null.

$books = $explorer->table('book');
while ($book = $books->fetch()) {
	$this->processBook($book);
}

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

Az eredményeket asszociatív tömbként adja vissza. Az első argumentum a tömb kulcsaként használandó oszlop nevét, a második argumentum pedig az értékként használandó oszlop nevét adja meg:

$authors = $explorer->table('author')->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Ha csak az első paramétert adjuk meg, akkor a teljes sort használjuk értékként, amelyet egy ActiveRow objektumként jelenítünk meg:

$authors = $explorer->table('author')->fetchPairs('id');
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]

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

$authors = $explorer->table('author')->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Alternatívaként paraméterként átadhat egy visszahívást is. A visszahívás minden egyes sorra vonatkozik, és vagy egyetlen értéket, vagy egy kulcs-érték párt ad vissza.

$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// [1 => 'Első könyv (Jan Novak)', ...]

// A visszahívás egy kulcs-érték párt tartalmazó tömböt is visszaadhat:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['Első könyv' => 'Jan Novak', ...]

fetchAll(): array

Visszaadja az összes sort a ActiveRow objektumok asszociatív tömbjeként, ahol a kulcsok az elsődleges kulcsok értékei.

$allBooks = $explorer->table('book')->fetchAll();
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]

count(): int

A count() metódus paraméterek nélkül a Selection objektum sorainak számát adja vissza:

$table->where('category', 1);
$count = $table->count();
$count = count($table); // alternatív

Megjegyzés: A count() paraméterrel a COUNT aggregációs funkciót hajtja végre az adatbázisban, az alábbiakban leírtak szerint.

ActiveRow::toArray(): array

A ActiveRow objektumot egy asszociatív tömbre alakítja át, ahol a kulcsok az oszlopnevek, az értékek pedig a megfelelő adatok.

$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray lesz ['id' => 1, 'title' => '...', 'author_id' => ..., ...]

Összesítés

A Selection osztály metódusokat biztosít az aggregációs funkciók (COUNT, SUM, MIN, MAX, AVG stb.) egyszerű végrehajtásához.

count (string $expr): int

SQL-lekérdezést hajt végre a COUNT függvénnyel, és visszaadja az eredményt. Ez a módszer annak meghatározására szolgál, hogy hány sor felel meg egy adott feltételnek:

$count = $table->count('*');                 // SELECT COUNT(*) FROM `table`
$count = $table->count('DISTINCT column');   // SELECT COUNT(DISTINCT `oszlop`) FROM `tábla`

Megjegyzés: a count() paraméter nélkül egyszerűen a Selection objektumban lévő sorok számát adja vissza.

min (string $expr) and max(string $expr)

A min() és max() metódusok a megadott oszlop vagy kifejezés minimális és maximális értékét adják vissza:

// SELECT MAX(`ár`) FROM `termék` WHERE `aktív` = 1
$maxPrice = $products->where('active', true)
	->max('price');

sum (string $expr): int

A megadott oszlop vagy kifejezés értékeinek összegét adja vissza:

// SELECT SUM(`ár` * `tételek_készleten`) FROM `termékek` WHERE `aktív` = 1
$totalPrice = $products->where('active', true)
	->sum('price * items_in_stock');

aggregation (string $function, ?string $groupFunction = null): mixed

Lehetővé teszi bármilyen aggregációs függvény végrehajtását.

// Kiszámítja a termékek átlagárát egy kategóriában
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// A termékcímkéket egyetlen karakterlánccá kombinálja
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Ha olyan eredményeket kell aggregálnunk, amelyek maguk is egy aggregálás és csoportosítás eredménye (pl. SUM(value) a csoportosított sorok felett), akkor második argumentumként megadjuk a közbenső eredményekre alkalmazandó aggregációs függvényt:

// Kiszámítja a készleten lévő termékek teljes árát kategóriánként, majd összegzi ezeket az árakat.
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

Ebben a példában először kiszámítjuk az egyes kategóriákba tartozó termékek összárát (SUM(price * stock) AS category_total), majd az eredményeket a category_id alapján csoportosítjuk. Ezután a aggregation('SUM(category_total)', 'SUM') segítségével összegezzük ezeket a részösszegeket. A 'SUM' második argumentum a közbenső eredményekre alkalmazandó aggregációs függvényt adja meg.

Beszúrás, frissítés és törlés

A Nette Database Explorer leegyszerűsíti az adatok beszúrását, frissítését és törlését. Az összes említett módszer hiba esetén a Nette\Database\DriverException címet dobja.

Selection::insert (iterable $data): static

Új rekordok beillesztése egy táblázatba.

Egyetlen rekord beillesztése:

Az új rekordot asszociatív tömbként vagy iterábilis objektumként (mint például az űrlapokban használt ArrayHash ) adja át, ahol a kulcsok megfelelnek a táblázat oszlopneveinek.

Ha a táblának van definiált elsődleges kulcsa, a módszer egy ActiveRow objektumot ad vissza, amelyet az adatbázisból újratöltenek, hogy tükrözze az adatbázis szintjén végrehajtott változásokat (pl. triggerek, alapértelmezett oszlopértékek vagy automatikus növelési számítások). Ez biztosítja az adatok konzisztenciáját, és az objektum mindig az aktuális adatbázis-adatokat tartalmazza. Ha nincs explicit módon definiálva elsődleges kulcs, a metódus a bemeneti adatokat tömbként adja vissza.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row az ActiveRow egy példánya, amely a beillesztett sor teljes adatát tartalmazza,
// beleértve az automatikusan generált azonosítót és a triggerek által végrehajtott módosításokat is.
echo $row->id;          // Kimeneti az újonnan beillesztett felhasználó azonosítóját
echo $row->created_at;  // Kimeneti a létrehozás idejét, ha azt egy trigger állította be.

Egyszerre több rekord beillesztése:

A insert() módszer lehetővé teszi több rekord beszúrását egyetlen SQL-lekérdezéssel. Ebben az esetben a beillesztett sorok számát adja vissza.

$insertedRows = $explorer->table('users')->insert([
	[
		'name' => 'John',
		'year' => 1994,
	],
	[
		'name' => 'Jack',
		'year' => 1995,
	],
]);
// INSERT INTO `felhasználók` (`név`, `év`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows 2 lesz

Paraméterként átadhat egy Selection objektumot is egy adatválasztékkal.

$newUsers = $explorer->table('potential_users')
	->where('approved', 1)
	->select('name, email');

$insertedRows = $explorer->table('users')->insert($newUsers);

Speciális értékek beillesztése:

Az értékek lehetnek fájlok, DateTime objektumok vagy SQL literálok:

$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // átalakítja az adatbázis-formátumra
	'avatar' => fopen('image.jpg', 'rb'),   // beilleszti a bináris fájl tartalmát
	'uuid' => $explorer::literal('UUID()'), // meghívja az UUID() függvényt
]);

Selection::update (iterable $data): int

Egy megadott szűrő alapján frissíti egy táblázat sorait. A ténylegesen módosított sorok számát adja vissza.

A frissítendő oszlopokat asszociatív tömbként vagy iterábilis objektumként (mint például az űrlapokban használt ArrayHash ) kell átadni, ahol a kulcsok megegyeznek a táblázat oszlopneveivel:

$affected = $explorer->table('users')
	->where('id', 10)
	->update([
		'name' => 'John Smith',
		'year' => 1994,
	]);
// UPDATE `users` SET `name` = 'John Smith', `year` = 1994 WHERE `id` = 10

A numerikus értékek módosításához a += és a -= operátorokat használhatja:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // növeli a "pontok" oszlop értékét 1 ponttal.
		'coins-=' => 1,   // csökkenti az "érmék" oszlop értékét 1-gyel.
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10

Selection::delete(): int

Töröl sorokat egy táblázatból egy megadott szűrő alapján. Visszaadja a törölt sorok számát.

$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE `id` = 10

A update() vagy a delete() meghívásakor mindenképpen használja a where() címet a frissítendő vagy törlendő sorok megadásához. Ha nem használja a where() címet, a művelet a teljes táblán fog végrehajtódni!

ActiveRow::update (iterable $data): bool

Frissíti a ActiveRow objektum által reprezentált adatbázis-sor adatait. Paraméterként iterálható adatokat fogad el, ahol a kulcsok oszlopnevek. A numerikus értékek módosításához a += és a -= operátorokat használhatja:

A frissítés végrehajtása után a ActiveRow objektum automatikusan újratöltődik az adatbázisból, hogy tükrözze az adatbázis szintjén (pl. triggerek) végrehajtott változásokat. A módszer csak akkor adja vissza a true értéket, ha valódi adatváltozás történt.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // növeli a megtekintések számát
]);
echo $article->views; // Kimeneti az aktuális nézetszámot

Ez a módszer csak egy adott sort frissít az adatbázisban. Több sor tömeges frissítéséhez használja a Selection::update() metódust.

ActiveRow::delete()

Töröl egy sort az adatbázisból, amelyet a ActiveRow objektum képvisel.

$book = $explorer->table('book')->get(1);
$book->delete(); // Törli az 1 azonosítóval rendelkező könyvet

Ez a módszer csak egy adott sort töröl az adatbázisból. Több sor tömeges törléséhez használja a Selection::delete() metódust.

Táblák közötti kapcsolatok

A relációs adatbázisokban az adatok több táblára vannak felosztva, és idegen kulcsok segítségével kapcsolódnak egymáshoz. A Nette Database Explorer forradalmi módot kínál az ilyen kapcsolatokkal való munkára – JOIN-lekérdezések írása nélkül, illetve anélkül, hogy bármilyen konfigurációt vagy entitásgenerálást igényelne.

A bemutatáshoz a példaadatbázist fogjuk használni(elérhető a GitHubon). Az adatbázis a következő táblákat tartalmazza:

• author – szerzők és fordítók ( id, name, web, born oszlopok).
• book – könyvek (oszlopok: id, author_id, translator_id, title, sequel_id).
• tag – címkék ( id, name oszlopok).
• book_tag – könyvek és címkék közötti kapcsolati táblázat ( book_id, tag_id oszlopok).

Adatbázis szerkezete

Ebben a könyvadatbázis példában többféle kapcsolatot találunk (a valósághoz képest leegyszerűsítve):

• Egytől sokig (1:N) – Minden könyvnek egy szerzője van; egy szerző több könyvet is írhat.
• Nulla a sokhoz (0:N) – Egy könyvnek lehet egy fordítója; egy fordító több könyvet is lefordíthat.
• Nulla az egyhez (0:1) – Egy könyvnek lehet folytatása.
• Sok-sok (M:N) – Egy könyvnek több címkéje is lehet, és egy címke több könyvhöz is hozzárendelhető.

Ezekben a kapcsolatokban mindig van egy szülő tábla és egy gyermek tábla. Például a szerzők és a könyvek közötti kapcsolatban a author tábla a szülő, a book tábla pedig a gyermek – úgy is elképzelhető, hogy egy könyv mindig egy szerzőhöz “tartozik”. Ez az adatbázis szerkezetében is tükröződik: a book gyermek tábla tartalmazza a author_id idegen kulcsot, amely a author szülő táblára hivatkozik.

Ha a könyveket a szerzők nevével együtt szeretnénk megjeleníteni, két lehetőségünk van. Vagy egyetlen SQL-lekérdezéssel lekérdezzük az adatokat JOIN-nal:

SELECT book.*, author.name FROM book LEFT JOIN author ON book.author_id = author.id;

Vagy két lépésben – először a könyveket, majd a szerzőket – és PHP-ben állítjuk össze az adatokat:

SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3); -- IDs of authors retrieved from books

A második megközelítés meglepő módon hatékonyabb. Az adatokat csak egyszer hívjuk le, és a gyorsítótárban jobban kihasználhatók. A Nette Database Explorer pontosan így működik – mindent a motorháztető alatt kezel, és tiszta API-t biztosít:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author egy rekord a 'author' táblából
	echo 'translated by: ' . $book->translator?->name;
}

Hozzáférés a szülői táblázathoz

Az anyatáblához való hozzáférés egyszerű. Ezek olyan kapcsolatok, mint például egy könyvnek van szerzője vagy egy könyvnek lehet fordítója. A kapcsolódó rekordot a ActiveRow objektumtulajdonságon keresztül lehet elérni – a tulajdonság neve megegyezik az idegen kulcs oszlopnevével a id utótag nélkül:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // megtalálja a szerzőt az 'author_id' oszlopon keresztül
echo $book->translator?->name; // a fordítót a 'translator_id' oszlopon keresztül találja meg.

A $book->author tulajdonság elérésekor az Explorer a book táblában olyan oszlopot keres, amely a author karakterláncot tartalmazza (pl. author_id). Az ebben az oszlopban lévő érték alapján lekérdezi a megfelelő rekordot a author táblából, és azt ActiveRow objektumként adja vissza. Hasonlóképpen, a $book->translator a translator_id oszlopot használja. Mivel a translator_id oszlop tartalmazhatja a null oszlopot, a ?-> operátort használja.

Alternatív megközelítést kínál a ref() metódus, amely két argumentumot fogad el – a céltábla nevét és az összekötő oszlopot -, és egy ActiveRow példányt vagy a null-t adja vissza:

echo $book->ref('author', 'author_id')->name;      // link a szerzőhöz
echo $book->ref('author', 'translator_id')->name;  // link a fordítóhoz

A ref() módszer akkor hasznos, ha a tulajdonságalapú hozzáférés nem használható, például ha a táblázat tartalmaz egy olyan oszlopot, amelynek neve megegyezik a tulajdonsággal (author). Más esetekben a jobb olvashatóság érdekében ajánlott a tulajdonságalapú hozzáférés használata.

Az Explorer automatikusan optimalizálja az adatbázis-lekérdezéseket. A könyvek iterálásakor és a kapcsolódó rekordok (szerzők, fordítók) elérésekor az Explorer nem generál lekérdezést minden egyes könyvre külön-külön. Ehelyett csak egy SELECT lekérdezést hajt végre minden egyes kapcsolattípushoz, jelentősen csökkentve ezzel az adatbázis terhelését. Például:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	echo $book->translator?->name;
}

Ez a kód csak három optimalizált adatbázis-lekérdezést hajt végre:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- IDs from 'author_id' column in selected books
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- IDs from 'translator_id' column in selected books

Az összekötő oszlop azonosításának logikáját a Conventions implementációja határozza meg. Javasoljuk a DiscoveredConventions használatát, amely elemzi az idegen kulcsokat, és lehetővé teszi, hogy zökkenőmentesen dolgozzon a meglévő táblázati kapcsolatokkal.

Hozzáférés a gyermektáblához

A gyermektáblához való hozzáférés ellenkező irányban működik. Most azt kérdezzük, hogy melyik könyvet írta ez a szerző vagy melyik könyvet fordította ez a fordító. Az ilyen típusú lekérdezéshez a related() metódust használjuk, amely egy Selection objektumot ad vissza a kapcsolódó rekordokkal. Íme egy példa:

$author = $explorer->table('author')->get(1);

// A szerző által írt összes könyv kimenete
foreach ($author->related('book.author_id') as $book) {
	echo "Wrote: $book->title";
}

// A szerző által lefordított összes könyv kiadása
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}

A related() módszer a kapcsolat leírását egyetlen argumentumként fogadja el, pontjelöléssel vagy két külön argumentumként:

$author->related('book.translator_id');    // egyetlen érv
$author->related('book', 'translator_id'); // két érv

Az Explorer automatikusan felismeri a megfelelő kapcsoló oszlopot a szülő tábla neve alapján. Ebben az esetben a book.author_id oszlopon keresztül linkel, mivel a forrás tábla neve author:

$author->related('book'); // használ book.author_id

Ha több lehetséges kapcsolat létezik, az Explorer AmbiguousReferenceKeyException kivételt dob.

Természetesen használhatjuk a related() módszert akkor is, ha több rekordon iterálunk egy ciklusban, és az Explorer ebben az esetben is automatikusan optimalizálja a lekérdezéseket:

$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' wrote:';
	foreach ($author->related('book') as $book) {
		echo $book->title;
	}
}

Ez a kód csak két hatékony SQL-lekérdezést generál:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- IDs of the selected authors

Sok-sok közötti kapcsolat

A sok-sok (M:N) kapcsolathoz egy összekötő táblára (esetünkben a book_tag) van szükség. Ez a tábla két idegen kulcs oszlopot tartalmaz (book_id, tag_id). Mindkét oszlop a kapcsolt táblák egyikének elsődleges kulcsára hivatkozik. A kapcsolódó adatok lekérdezéséhez először a related('book_tag') segítségével a kapcsoló táblából hívjuk le a rekordokat, majd folytatjuk a céladatokkal:

$book = $explorer->table('book')->get(1);
// A könyvhöz rendelt címkék neveinek kiadása
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // a címke nevét a link táblán keresztül szerzi be
}

$tag = $explorer->table('tag')->get(1);
// Ellenkező irányban: az adott címkével rendelkező könyvek címeit adja ki.
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // a könyv címének lekérdezése
}

Az Explorer ismét hatékony formára optimalizálja az SQL-lekérdezéseket:

SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- IDs of the selected books
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- IDs of the tags found in book_tag

Kapcsolódó táblákon keresztül történő lekérdezés

A where(), select(), order() és group() metódusokban speciális jelöléseket használhat más táblák oszlopainak eléréséhez. Az Explorer automatikusan létrehozza a szükséges JOIN-okat.

A Pont jelölés (parent_table.column) a szülő tábla szemszögéből nézve az 1:N kapcsolatokhoz használatos:

$books = $explorer->table('book');

// Megkeresi azokat a könyveket, amelyek szerzőinek neve 'Jon' betűvel kezdődik.
$books->where('author.name LIKE ?', 'Jon%');

// A könyveket a szerző neve szerint rendezi csökkenő sorrendbe
$books->order('author.name DESC');

// A könyv címe és a szerző neve
$books->select('book.title, author.name');

Kettőspont jelölés a szülő tábla szemszögéből nézve az 1:N kapcsolatokra használatos:

$authors = $explorer->table('author');

// Megkeresi azokat a szerzőket, akik olyan könyvet írtak, amelynek a címében szerepel a 'PHP' szó
$authors->where(':book.title LIKE ?', '%PHP%');

// Megszámolja az egyes szerzők könyveinek számát
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

A fenti példában a kettőspont jelöléssel (:book.title) az idegen kulcs oszlop nincs kifejezetten megadva. Az Explorer automatikusan felismeri a megfelelő oszlopot a szülő tábla neve alapján. Ebben az esetben a book.author_id oszlopon keresztül csatlakozik, mivel a forrás tábla neve author. Ha több lehetséges kapcsolat létezik, az Explorer az AmbiguousReferenceKeyException kivételt dobja.

Az összekötő oszlop kifejezetten megadható zárójelben:

// Találja meg azokat a szerzőket, akik olyan könyvet fordítottak, amelynek a címében szerepel a 'PHP' szó
$authors->where(':book(translator).title LIKE ?', '%PHP%');

A jelölések láncolhatók, hogy több táblában is hozzáférjenek az adatokhoz:

// 'PHP' címkével ellátott könyvek szerzőinek keresése
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

A JOIN feltételeinek kiterjesztése

A joinWhere() módszer a ON kulcsszó után további feltételeket ad az SQL-ben a táblázatok összekapcsolásához.

Tegyük fel például, hogy egy adott fordító által lefordított könyveket szeretnénk megtalálni:

// Megtalálja a 'David' nevű fordító által lefordított könyveket.
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')

A joinWhere() feltételben ugyanazokat a konstrukciókat használhatja, mint a where() módszerben – operátorokat, helyőrzőket, értéktömböket vagy SQL-kifejezéseket.

Összetettebb, több JOIN-t tartalmazó lekérdezésekhez táblázat aliasokat lehet definiálni:

$tags = $explorer->table('tag')
	->joinWhere(':book_tag.book.author', 'book_author.born < ?', 1950)
	->alias(':book_tag.book.author', 'book_author');
// LEFT JOIN `book_tag` ON `tag`.`id` = `book_tag`.`tag_id`
// LEFT JOIN `book` ON `book_tag`.`book_id` = `book`.`id`
// LEFT JOIN `author` `book_author` ON `book`.`author_id` = `book_author`.`id`
//   AND (`book_author`.`born` < 1950)

Vegye figyelembe, hogy míg a where() módszer a WHERE záradékhoz feltételeket ad hozzá, addig a joinWhere() módszer a ON záradékban szereplő feltételeket bővíti ki a táblaösszekötések során.

Az Explorer manuális létrehozása

Ha nem a Nette DI konténert használja, akkor manuálisan is létrehozhatja a Nette\Database\Explorer egy példányát:

use Nette\Database;

// $storage megvalósítja a Nette\Caching\Storage-t, pl.:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// adatbázis-kapcsolat
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// kezeli az adatbázis struktúrájának tükrözését
$structure = new Database\Structure($connection, $storage);
// meghatározza a táblák neveinek, oszlopainak és idegen kulcsainak leképezésére vonatkozó szabályokat.
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);