Database Explorer

Az Explorerrel a Nette\Database\Explorer objektum table() metódusának meghívásával kezdhet (a csatlakozás részleteit a Csatlakozás és konfiguráció fejezetben találja):

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

A metódus egy Selection objektumot ad vissza, amely egy SQL lekérdezést képvisel. Erre az objektumra további metódusokat láncolhatunk az eredmények szűrésére és rendezésére. A lekérdezés csak akkor áll össze és fut le, amikor elkezdjük kérni az adatokat. Például egy foreach ciklussal történő bejáráskor. Minden sort egy ActiveRow objektum képvisel:

foreach ($books as $book) {
	echo $book->title;        // a 'title' oszlop kiírása
	echo $book->author_id;    // az 'author_id' oszlop kiírása
}

Az Explorer alapvetően megkönnyíti a táblák közötti kapcsolatokkal való munkát. A következő példa bemutatja, milyen könnyen tudunk adatokat kiírni összekapcsolt táblákból (könyvek és szerzőik). Figyelje meg, hogy nem kell semmilyen JOIN lekérdezést írnunk, a Nette létrehozza őket helyettünk:

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

foreach ($books as $book) {
	echo 'Könyv: ' . $book->title;
	echo 'Szerző: ' . $book->author->name; // JOIN-t hoz létre az 'author' táblára
}

A Nette Database Explorer optimalizálja a lekérdezéseket, hogy a lehető leghatékonyabbak legyenek. 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.

Ráadásul az Explorer figyeli, hogy mely oszlopokat használják a kódban, és csak azokat tölti be az adatbázisból, ezzel további teljesítményt takarítva meg. Ez a viselkedés teljesen automatikus és adaptív. Ha később módosítja a kódot, és elkezd további oszlopokat használni, az Explorer automatikusan módosítja a lekérdezéseket. Nem kell semmit beállítania, sem azon gondolkodnia, mely oszlopokra lesz szüksége – bízza ezt a Nette-re.

Szűrés és rendezés

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

A metódusok láncolhatók (ún. fluent interface): $table->where(...)->order(...)->limit(...).

Ezekben a metódusokban speciális jelölést is használhat a kapcsolódó táblákból származó adatokhoz való hozzáféréshez.

Escapelés és azonosítók

A metódusok automatikusan escapelik a paramétereket és idézőjelek közé teszik az azonosítókat (tábla- és oszlopneveket), ezzel megakadályozva az SQL injectiont. A helyes működéshez néhány szabályt be kell tartani:

• A kulcsszavakat, függvényneveket, eljárásneveket stb. nagybetűkkel írja.
• Az oszlop- és táblaneveket kisbetűkkel írja.
• A stringeket mindig paramétereken keresztül adja át.

where('name = ' . $name);         // KRITIKUS SEBEZHETŐSÉG: SQL injection
where('name LIKE "%search%"');    // ROSSZ: bonyolítja az automatikus idézőjelezést
where('name LIKE ?', '%search%'); // HELYES: érték paraméteren keresztül átadva

where('name like ?', $name);     // ROSSZ: generálja: `name` `like` ?
where('name LIKE ?', $name);     // HELYES: generálja: `name` LIKE ?
where('LOWER(name) = ?', $value);// HELYES: LOWER(`name`) = ?

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

Szűri az eredményeket WHERE feltételekkel. Erőssége az intelligens munka különböző típusú értékekkel és az SQL operátorok automatikus kiválasztása.

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 különböző speciális esetekkel foglalkoznunk. A Nette megoldja őket helyettünk:

$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)
// operátor nélküli helyettesítő kérdőjelet is használhatunk:
$table->where('id ?', 1);        // WHERE `id` = 1

A metódus helyesen kezeli a negált feltételeket és az üres tömböket is:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- semmit sem talál
$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

Paraméterként átadhatunk egy másik tábla eredményét is – al-lekérdezés jön létre:

// 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ételeket tömbként is átadhatjuk, amelynek elemei AND-del lesznek összekötve:

// WHERE (`price_final` < `price_original`) AND (`stock_count` > `min_stock`)
$table->where([
	'price_final < price_original',
	'stock_count > min_stock',
]);

A tömbben használhatunk kulcs ⇒ érték párokat, é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],
]);

A tömbben kombinálhatunk SQL kifejezéseket helyettesítő kérdőjelekkel és több paraméterrel. Ez alkalmas komplex feltételekhez pontosan definiált operátorokkal:

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

A where() többszöri hívása automatikusan AND-del köti össze a feltételeket.

whereOr (array $parameters): static

Hasonlóan a where()-hez, feltételeket ad hozzá, de azzal a különbséggel, hogy OR-ral köti össze őket:

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

Itt is használhatunk komplexebb kifejezéseket:

// WHERE (`price` > 1000) OR (`price_with_tax` > 1500)
$table->whereOr([
	'price > ?' => 1000,
	'price_with_tax > ?' => 1500,
]);

wherePrimary (mixed $key): static

Feltételt ad hozzá a tábla 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), 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

Meghatározza a sorok visszaadási sorrendjét. Rendezhetünk egy vagy több oszlop szerint, csökkenő vagy növekvő sorrendben, vagy saját kifejezés szerint:

$table->order('created');                   // ORDER BY `created`
$table->order('created DESC');              // ORDER BY `created` DESC
$table->order('priority DESC, created');    // ORDER BY `priority` DESC, `created`
$table->order('status = ? DESC', 'active'); // ORDER BY `status` = 'active' DESC

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

Meghatározza az adatbázisból visszaadandó oszlopokat. Alapértelmezés szerint a Nette Database Explorer csak azokat az oszlopokat adja vissza, amelyeket ténylegesen használnak a kódban. A select() metódust olyan esetekben használjuk, amikor specifikus kifejezéseket kell visszaadnunk:

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

Az AS segítségével definiált aliasok ezután elérhetők az ActiveRow objektum tulajdonságaként:

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 lehetővé teszi az offset beállítását:

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

Lapozáshoz célszerűbb a page() metódust használni.

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

Megkönnyíti az eredmények lapozását. Elfogadja az oldal számát (1-től számolva) és az oldalankénti elemek számát. Opcionálisan átadható egy referencia egy változóra, amelybe az oldalak teljes száma kerül mentésre:

$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, $numOfPages);
echo "Összesen oldalak: $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 a termékek számát minden kategóriában
$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éhez (HAVING). Használható a group() metódussal és aggregáló függvényekkel együtt:

// Megtalálja azokat a kategóriákat, amelyek több mint 100 termékkel rendelkeznek
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Adatok olvasása

Az adatok adatbázisból történő olvasásához számos hasznos metódus áll rendelkezésre:

Az ActiveRow objektum csak olvasásra szolgál. Ez azt jelenti, hogy nem lehet módosítani a tulajdonságainak értékeit. 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 töltődnek be, és bármilyen változtatást explicit módon és ellenőrzötten kell végrehajtani.

foreach – iteráció az összes soron

A legegyszerűbb módja a lekérdezés végrehajtásának és a sorok megszerzésének a foreach ciklussal történő iterálás. Automatikusan elindítja az SQL lekérdezést.

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

get ($key): ?ActiveRow

Végrehajtja az SQL lekérdezést és visszaadja a sort az elsődleges kulcs alapján, vagy null-t, ha nem létezik.

$book = $explorer->table('book')->get(123);  // visszaadja az ActiveRow-t 123 ID-vel vagy null-t
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Visszaadja a sort és a belső mutatót a következőre lépteti. Ha már nincsenek további sorok, null-t ad vissza.

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

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

Visszaadja az eredményeket asszociatív tömbként. Az első argumentum határozza meg annak az oszlopnak a nevét, amely kulcsként lesz használva a tömbben, a második argumentum pedig annak az oszlopnak a nevét, amely értékként lesz használva:

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

Ha csak az első paramétert adjuk meg, az érték az egész sor lesz, azaz az ActiveRow objektum:

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

Duplikált kulcsok esetén az utolsó sor értéke lesz használva. Ha null-t használunk kulcsként, a tömb numerikusan lesz indexelve nullától kezdve (ekkor nem történik ütközés):

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

fetchPairs (Closure $callback): array

Alternatívaként megadhat egy callbacket paraméterként, amely minden sorhoz vagy magát az értéket, vagy egy kulcs-érték párt ad vissza.

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

// A callback visszaadhat egy tömböt is kulcs & érték párral:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['Első könyv' => 'János Novak', ...]

fetchAll(): array

Visszaadja az összes sort 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éter nélkül visszaadja a sorok számát a Selection objektumban:

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

Figyelem, a count() paraméterrel aggregáló COUNT függvényt hajt végre az adatbázisban, lásd alább.

ActiveRow::toArray(): array

Átalakítja az ActiveRow objektumot asszociatív tömbbé, 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' => ..., ...]

Aggregáció

A Selection osztály metódusokat biztosít az aggregáló függvények (COUNT, SUM, MIN, MAX, AVG stb.) egyszerű végrehajtásához.

count (string $expr): int

Végrehajt egy SQL lekérdezést a COUNT függvénnyel és visszaadja az eredményt. A metódus arra használatos, hogy megállapítsuk, hány sor felel meg egy bizonyos feltételnek:

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

Figyelem, a count() paraméter nélkül csak a sorok számát adja vissza a Selection objektumban.

min (string $expr) és max(string $expr)

A min() és max() metódusok visszaadják a minimális és maximális értéket a megadott oszlopban vagy kifejezésben:

// SELECT MAX(`price`) FROM `products` WHERE `active` = 1
$maxPrice = $products->where('active', true)
	->max('price');

sum (string $expr)

Visszaadja az értékek összegét a megadott oszlopban vagy kifejezésben:

// SELECT SUM(`price` * `items_in_stock`) FROM `products` WHERE `active` = 1
$totalPrice = $products->where('active', true)
	->sum('price * items_in_stock');

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

Lehetővé teszi tetszőleges aggregáló függvény végrehajtását.

// termékek átlagos ára egy kategóriában
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// összekapcsolja a termék címkéit egyetlen stringgé
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Ha olyan eredményeket kell aggregálnunk, amelyek már maguk is valamilyen aggregáló függvényből és csoportosításból származnak (pl. SUM(érték) csoportosított sorokon keresztül), második argumentumként megadjuk azt az aggregáló függvényt, amelyet ezekre a köztes eredményekre kell alkalmazni:

// Kiszámítja a raktáron lévő termékek teljes árát az egyes kategóriákra, majd összeadja 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 a termékek teljes árát minden kategóriában (SUM(price * stock) AS category_total), és csoportosítjuk az eredményeket a category_id szerint. Ezután az aggregation('SUM(category_total)', 'SUM') segítségével összeadjuk ezeket a category_total köztes összegeket. A második argumentum 'SUM' azt mondja, hogy a köztes eredményekre a SUM függvényt kell alkalmazni.

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. Minden említett metódus kivételt Nette\Database\DriverException dob hiba esetén.

Selection::insert (iterable $data)

Új rekordokat szúr be a táblába.

Egy rekord beszúrása:

Az új rekordot asszociatív tömbként vagy iterable objektumként (például az űrlapokban használt ArrayHash) adjuk át, ahol a kulcsok megfelelnek a tábla oszlopneveinek.

Ha a táblának definiált elsődleges kulcsa van, a metódus egy ActiveRow objektumot ad vissza, amely újra betöltődik az adatbázisból, hogy figyelembe vegye az adatbázis szintjén végrehajtott esetleges változásokat (triggerek, oszlopok alapértelmezett értékei, auto-increment oszlopok számításai). Ez biztosítja az adatok konzisztenciáját, és az objektum mindig az aktuális adatokat tartalmazza az adatbázisból. Ha nincs egyértelmű elsődleges kulcsa, a átadott adatokat tömb formájában adja vissza.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row egy ActiveRow példány, és tartalmazza a beszúrt sor teljes adatait,
// beleértve az automatikusan generált ID-t és a triggerek által végrehajtott esetleges változásokat
echo $row->id; // Kiírja az újonnan beszúrt felhasználó ID-ját
echo $row->created_at; // Kiírja a létrehozás idejét, ha trigger állította be

Több rekord beszúrása egyszerre:

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

$insertedRows = $explorer->table('users')->insert([
	[
		'name' => 'John',
		'year' => 1994,
	],
	[
		'name' => 'Jack',
		'year' => 1995,
	],
]);
// INSERT INTO `users` (`name`, `year`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows értéke 2 lesz

Paraméterként átadható egy Selection objektum is adatkiválasztással.

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

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

Speciális értékek beszúrása:

Értékként átadhatunk fájlokat, DateTime objektumokat vagy SQL literálokat is:

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

Selection::update (iterable $data): int

Frissíti a tábla sorait a megadott szűrő szerint. Visszaadja a ténylegesen megváltozott sorok számát.

A módosítandó oszlopokat asszociatív tömbként vagy iterable objektumként (például az űrlapokban használt ArrayHash) adjuk át, ahol a kulcsok megfelelnek a tábla oszlopneveinek:

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

Numerikus értékek módosításához használhatjuk a += és -= operátorokat:

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

Selection::delete(): int

Törli a tábla sorait a megadott szűrő szerint. Visszaadja a törölt sorok számát.

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

Az update() és delete() hívásakor ne felejtse el a where() segítségével megadni a módosítandó/törlendő sorokat. Ha nem használja a where()-t, a művelet az egész táblán végrehajtódik!

ActiveRow::update (iterable $data): bool

Frissíti az adatokat az ActiveRow objektum által képviselt adatbázis-sorban. Paraméterként egy iterable-t fogad el a frissítendő adatokkal (a kulcsok az oszlopnevek). Numerikus értékek módosításához használhatjuk a += és -= operátorokat:

A frissítés végrehajtása után az ActiveRow automatikusan újra betöltődik az adatbázisból, hogy figyelembe vegye az adatbázis szintjén végrehajtott esetleges változásokat (pl. triggerek). A metódus csak akkor ad vissza true-t, ha tényleges adatváltozás történt.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // növeljük a megtekintések számát
]);
echo $article->views; // Kiírja az aktuális megtekintések számát

Ez a metódus csak egyetlen konkrét 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örli az adatbázisból azt a sort, amelyet az ActiveRow objektum képvisel.

$book = $explorer->table('book')->get(1);
$book->delete(); // Törli az 1-es ID-jű könyvet

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

Kapcsolatok a táblák között

Relációs adatbázisokban az adatok több táblára vannak osztva, és idegen kulcsok segítségével kapcsolódnak egymáshoz. A Nette Database Explorer forradalmi módot kínál ezekkel a kapcsolatokkal való munkára – JOIN lekérdezések írása és bármi konfigurálása vagy generálása nélkül.

A kapcsolatokkal való munka illusztrálására egy könyvadatbázis példáját használjuk (megtalálható a GitHubon). Az adatbázisban a következő táblák vannak:

• author – írók és fordítók (oszlopok: id, name, web, born)
• book – könyvek (oszlopok: id, author_id, translator_id, title, sequel_id)
• tag – címkék (oszlopok: id, name)
• book_tag – kapcsolótábla a könyvek és címkék között (oszlopok: book_id, tag_id)

A könyvadatbázis példánkban több típusú kapcsolatot találunk (bár a modell egyszerűsített a valósághoz képest):

• Egy-a-többhöz 1:N – minden könyvnek egy szerzője van, egy szerző több könyvet írhat
• Nulla-a-többhöz 0:N – egy könyvnek lehet fordítója, egy fordító több könyvet fordíthat
• Nulla-az-egyhez 0:1 – egy könyvnek lehet folytatása
• Több-a-többhöz M:N – egy könyvnek több címkéje lehet, és egy címke több könyvhöz rendelhető

Ezekben a kapcsolatokban mindig van egy szülő és egy gyermek tábla. Például a szerző és a könyv közötti kapcsolatban az author tábla a szülő, a book pedig a gyermek – elképzelhetjük úgy, hogy a könyv mindig “tartozik” valamilyen szerzőhöz. Ez megmutatkozik az adatbázis struktúrájában is: a gyermek book tábla tartalmaz egy author_id idegen kulcsot, amely a szülő author táblára hivatkozik.

Ha ki kell listáznunk a könyveket a szerzőik nevével együtt, két lehetőségünk van. Vagy egyetlen SQL lekérdezéssel szerezzük meg az adatokat JOIN segítségével:

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

Vagy két lépésben töltjük be az adatokat – először a könyveket, majd a szerzőiket – és utána PHP-ban összerakjuk őket:

SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3);  -- a megszerzett könyvek szerzőinek id-jai

A második megközelítés valójában hatékonyabb, bár ez meglepő lehet. Az adatok csak egyszer töltődnek be, és jobban felhasználhatók a cache-ben. Pontosan így működik a Nette Database Explorer – mindent a felszín alatt old meg, és elegáns API-t kínál Önnek:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'cím: ' . $book->title;
	echo 'írta: ' . $book->author->name; // $book->author egy rekord az 'author' táblából
	echo 'fordította: ' . $book->translator?->name;
}

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

A szülő táblához való hozzáférés egyszerű. Olyan kapcsolatokról van szó, mint a könyvnek van szerzője vagy a könyvnek lehet fordítója. A kapcsolódó rekordot az ActiveRow objektum property-jén keresztül érjük el – a neve megegyezik az idegen kulcsot tartalmazó oszlop nevével id nélkül:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // megtalálja a szerzőt az author_id oszlop alapján
echo $book->translator?->name; // megtalálja a fordítót a translator_id alapján

Amikor hozzáférünk a $book->author property-hez, az Explorer a book táblában keres egy oszlopot, amelynek neve tartalmazza az author stringet (tehát author_id). Az ebben az oszlopban lévő érték alapján betölti a megfelelő rekordot az author táblából, és ActiveRow-ként adja vissza. Hasonlóan működik a $book->translator is, amely a translator_id oszlopot használja. Mivel a translator_id oszlop tartalmazhat null-t, a kódban a ?-> operátort használjuk.

Alternatív utat kínál a ref() metódus, amely két argumentumot fogad el, a cél tábla nevét és a kapcsoló oszlop nevét, és egy ActiveRow példányt vagy null-t ad vissza:

echo $book->ref('author', 'author_id')->name;      // kapcsolat a szerzővel
echo $book->ref('author', 'translator_id')->name;  // kapcsolat a fordítóval

A ref() metódus akkor hasznos, ha nem lehet a property-n keresztüli hozzáférést használni, mert a tábla tartalmaz egy azonos nevű oszlopot (azaz author). Más esetekben a property-n keresztüli hozzáférés használata javasolt, amely olvashatóbb.

Az Explorer automatikusan optimalizálja az adatbázis-lekérdezéseket. Amikor ciklusban járjuk be a könyveket, és hozzáférünk a kapcsolódó rekordjaikhoz (szerzők, fordítók), az Explorer nem generál lekérdezést minden egyes könyvhöz külön. Ehelyett csak egy SELECT-et hajt végre minden kapcsolattípushoz, ezzel jelentősen csökkentve 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 ezt a három villámgyors lekérdezést hívja meg az adatbázisba:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- id-k a kiválasztott könyvek author_id oszlopából
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- id-k a kiválasztott könyvek translator_id oszlopából

A kapcsoló oszlop megtalálá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 a táblák közötti meglévő kapcsolatokkal való egyszerű munkát.

Hozzáférés a gyermek táblához

A gyermek táblához való hozzáférés fordított irányban működik. Most azt kérdezzük, milyen könyveket írt ez a szerző vagy fordított ez a fordító. Ehhez a lekérdezéstípushoz a related() metódust használjuk, amely egy Selection-t ad vissza a kapcsolódó rekordokkal. Nézzünk egy példát:

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

// Kiírja a szerző összes könyvét
foreach ($author->related('book.author_id') as $book) {
	echo "Írta: $book->title";
}

// Kiírja az összes könyvet, amelyet a szerző fordított
foreach ($author->related('book.translator_id') as $book) {
	echo "Fordította: $book->title";
}

A related() metódus a kapcsolat leírását egyetlen argumentumként pont-jelöléssel vagy két különálló argumentumként fogadja el:

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

Az Explorer képes automatikusan felismerni a helyes kapcsoló oszlopot a szülő tábla neve alapján. Ebben az esetben a book.author_id oszlopon keresztül kapcsolódik, mivel a forrástábla neve author:

$author->related('book');  // a book.author_id-t használja

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

A related() metódust természetesen használhatjuk több rekord ciklusban történő bejárásakor is, é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 . ' írta:';
	foreach ($author->related('book') as $book) {
		echo $book->title;
	}
}

Ez a kód csak két villámgyors SQL lekérdezést generál:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- a kiválasztott szerzők id-jai

Több-a-többhöz kapcsolat

A több-a-többhöz (M:N) kapcsolathoz szükség van egy kapcsolótábla létezésére (esetünkben book_tag), amely két idegen kulcsot tartalmazó oszlopot (book_id, tag_id) tartalmaz. Ezen oszlopok mindegyike az összekapcsolt táblák egyikének elsődleges kulcsára hivatkozik. A kapcsolódó adatok megszerzéséhez először a kapcsolótábla rekordjait szerezzük meg a related('book_tag') segítségével, majd tovább haladunk a céladatokhoz:

$book = $explorer->table('book')->get(1);
// kiírja a könyvhöz rendelt címkék neveit
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name;  // kiírja a címke nevét a kapcsolótáblán keresztül
}

$tag = $explorer->table('tag')->get(1);
// vagy fordítva: kiírja az ezzel a címkével megjelölt könyvek neveit
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // kiírja a könyv nevét
}

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

SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- a kiválasztott könyvek id-jai
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- a book_tag-ban talált címkék id-jai

Lekérdezés kapcsolódó táblákon keresztül

A where(), select(), order() és group() metódusokban speciális jelöléseket használhatunk más táblák oszlopaihoz való hozzáféréshez. Az Explorer automatikusan létrehozza a szükséges JOIN-okat.

Pont-jelölés (szülő_tábla.oszlop) a gyermek tábla szemszögéből nézett 1:N kapcsolathoz használatos:

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

// Megtalálja azokat a könyveket, amelyek szerzőjének neve 'Jon'-nal kezdődik
$books->where('author.name LIKE ?', 'Jon%');

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

// Kiírja a könyv címét és a szerző nevét
$books->select('book.title, author.name');

Kettőspont-jelölés (:gyermek_tábla.oszlop) a szülő tábla szemszögéből nézett 1:N kapcsolathoz használatos:

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

// Megtalálja azokat a szerzőket, akik 'PHP'-t tartalmazó című könyvet írtak
$authors->where(':book.title LIKE ?', '%PHP%');

// Megszámolja a könyvek számát minden szerzőhöz
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

A fenti példában a kettőspont-jelöléssel (:book.title) nincs megadva az idegen kulcs oszlopa. Az Explorer automatikusan felismeri a helyes oszlopot a szülő tábla neve alapján. Ebben az esetben a book.author_id oszlopon keresztül kapcsolódik, mivel a forrástábla neve author. Ha több lehetséges kapcsolat létezne, az Explorer AmbiguousReferenceKeyException kivételt dob.

A kapcsoló oszlopot explicit módon meg lehet adni zárójelben:

// Megtalálja azokat a szerzőket, akik 'PHP'-t tartalmazó című könyvet fordítottak
$authors->where(':book(translator).title LIKE ?', '%PHP%');

A jelölések láncolhatók több táblán keresztüli hozzáféréshez:

// Megtalálja a 'PHP' címkével megjelölt könyvek szerzőit
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

JOIN feltételek bővítése

A joinWhere() metódus kibővíti azokat a feltételeket, amelyeket a táblák összekapcsolásakor az SQL-ben az ON kulcsszó után adunk meg.

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

// Megtalálja a 'David' nevű fordító által fordí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álhatjuk, mint a where() metódusban – operátorokat, helyettesítő kérdőjeleket, értékek tömbjét vagy SQL kifejezéseket.

Összetettebb lekérdezésekhez több JOIN-nal definiálhatunk tábla aliasokat:

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

Figyelje meg, hogy míg a where() metódus feltételeket ad hozzá a WHERE záradékhoz, a joinWhere() metódus kibővíti a feltételeket az ON záradékban a táblák összekapcsolásakor.