Database Explorer
Explorer nabízí intuitivní a efektivní způsob práce s databází. Stará se automaticky o vazby mezi tabulkami a optimalizaci dotazů, takže se můžete soustředit na svou aplikaci. Funguje ihned bez nastavování. Pokud potřebujete plnou kontrolu nad SQL dotazy, můžete využít SQL přístup.
- Práce s daty je přirozená a snadno pochopitelná
- Generuje optimalizované SQL dotazy, které načítají pouze potřebná data
- Umožňuje snadný přístup k souvisejícím datům bez nutnosti psát JOIN dotazy
- Funguje okamžitě bez jakékoliv konfigurace či generování entit
S Explorerem začnete voláním metody table()
objektu Nette\Database\Explorer (detaily k připojení najdete
v kapitole Připojení a konfigurace):
Metoda vrací objekt Selection, který
představuje SQL dotaz. Na tento objekt můžeme navazovat další metody pro filtrování a řazení výsledků. Dotaz se
sestaví a spustí až ve chvíli, kdy začneme požadovat data. Například procházením cyklem foreach
. Každý
řádek je reprezentován objektem ActiveRow:
Explorer zásadním způsobem usnadňuje práci s vazbami mezi tabulkami. Následující příklad ukazuje, jak snadno můžeme vypsat data z provázaných tabulek (knihy a jejich autoři). Všimněte si, že nemusíme psát žádné JOIN dotazy, Nette je vytvoří za nás:
Nette Database Explorer optimalizuje dotazy, aby byly co nejefektivnější. Výše uvedený příklad provede pouze dva SELECT dotazy, bez ohledu na to, jestli zpracováváme 10 nebo 10 000 knih.
Navíc Explorer sleduje, které sloupce se v kódu používají, a načítá z databáze pouze ty, čímž šetří další výkon. Toto chování je plně automatické a adaptivní. Pokud později upravíte kód a začnete používat další sloupce, Explorer automaticky upraví dotazy. Nemusíte nic nastavovat, ani přemýšlet nad tím, které sloupce budete potřebovat – nechte to na Nette.
Filtrování a řazení
Třída Selection
poskytuje metody pro filtrování a řazení výběru dat.
where($condition, ...$params) |
Přidá podmínku WHERE. Více podmínek je spojeno operátorem AND |
whereOr(array $conditions) |
Přidá skupinu podmínek WHERE spojených operátorem OR |
wherePrimary($value) |
Přidá podmínku WHERE podle primárního klíče |
order($columns, ...$params) |
Nastaví řazení ORDER BY |
select($columns, ...$params) |
Specifikuje sloupce, které se mají načíst |
limit($limit, $offset = null) |
Omezí počet řádků (LIMIT) a volitelně nastaví OFFSET |
page($page, $itemsPerPage, &$total = null) |
Nastaví stránkování |
group($columns, ...$params) |
Seskupí řádky (GROUP BY) |
having($condition, ...$params) |
Přidá podmínku HAVING pro filtrování seskupených řádků |
Metody lze řetězit (tzv. fluent interface):
$table->where(...)->order(...)->limit(...)
.
V těchto metodách můžete také používat speciální notaci pro přístup k datům ze souvisejících tabulek.
Escapování a identifikátory
Metody automaticky escapují parametry a uvozují identifikátory (názvy tabulek a sloupců), čímž zabraňuje SQL injection. Pro správné fungování je nutné dodržovat několik pravidel:
- Klíčová slova, názvy funkcí, procedur apod. pište velkými písmeny.
- Názvy sloupců a tabulek pište malými písmeny.
- Řetězce vždy dosazujte přes parametry.
where(string|array $condition, …$parameters): static
Filtruje výsledky pomocí podmínek WHERE. Její silnou stránkou je inteligentní práce s různými typy hodnot a automatická volba SQL operátorů.
Základní použití:
Díky automatické detekci vhodných operátorů nemusíme řešit různé speciální případy. Nette je vyřeší za nás:
Metoda správně zpracovává i záporné podmínky a prázdné pole:
Jako parametr můžeme předat také výsledek z jiné tabulky – vytvoří se poddotaz:
Podmínky můžeme předat také jako pole, jehož položky se spojí pomocí AND:
V poli můžeme použít dvojice klíč ⇒ hodnota a Nette opět automaticky zvolí správné operátory:
V poli můžeme kombinovat SQL výrazy se zástupnými otazníky a více parametry. To je vhodné pro komplexní podmínky s přesně definovanými operátory:
Vícenásobné volání where()
podmínky automaticky spojuje pomocí AND.
whereOr(array $parameters): static
Podobně jako where()
přidává podmínky, ale s tím rozdílem, že je spojuje pomocí OR:
I zde můžeme použít komplexnější výrazy:
wherePrimary(mixed $key): static
Přidá podmínku pro primární klíč tabulky:
Pokud má tabulka kompozitní primární klíč (např. foo_id
, bar_id
), předáme jej
jako pole:
order(string $columns, …$parameters): static
Určuje pořadí, v jakém budou řádky vráceny. Můžeme řadit podle jednoho či více sloupců, v sestupném či vzestupném pořadí, nebo podle vlastního výrazu:
select(string $columns, …$parameters): static
Specifikuje sloupce, které se mají vrátit z databáze. Ve výchozím stavu Nette Database Explorer vrací pouze ty sloupce,
které se reálně použijí v kódu. Metodu select()
tak používáme v případech, kdy potřebujeme vrátit
specifické výrazy:
Aliasy definované pomocí AS
jsou pak dostupné jako vlastnosti objektu ActiveRow:
limit(?int $limit, ?int $offset = null): static
Omezuje počet vrácených řádků (LIMIT) a volitelně umožňuje nastavit offset:
Pro stránkování je vhodnější použít metodu page()
.
page(int $page, int $itemsPerPage, &$numOfPages = null): static
Usnadňuje stránkování výsledků. Přijímá číslo stránky (počítané od 1) a počet položek na stránku. Volitelně lze předat referenci na proměnnou, do které se uloží celkový počet stránek:
group(string $columns, …$parameters): static
Seskupuje řádky podle zadaných sloupců (GROUP BY). Používá se obvykle ve spojení s agregačními funkcemi:
having(string $having, …$parameters): static
Nastavuje podmínku pro filtrování seskupených řádků (HAVING). Lze ji použít ve spojení s metodou
group()
a agregačními funkcemi:
Čtení dat
Pro čtení dat z databáze máme k dispozici několik užitečných metod:
foreach ($table as $key => $row) |
Iteruje přes všechny řádky, $key je hodnota primárního klíče, $row je objekt ActiveRow |
$row = $table->get($key) |
Vrátí jeden řádek podle primárního klíče |
$row = $table->fetch() |
Vrátí aktuální řádek a posune ukazatel na další |
$array = $table->fetchPairs() |
Vytvoří asociativní pole z výsledků |
$array = $table->fetchAll() |
Vráti všechny řádky jako pole |
count($table) |
Vrátí počet řádků v objektu Selection |
Objekt ActiveRow je určen pouze pro čtení. To znamená, že nelze měnit hodnoty jeho properties. Toto omezení zajišťuje konzistenci dat a zabraňuje neočekávaným vedlejším efektům. Data se načítají z databáze a jakákoliv změna by měla být provedena explicitně a kontrolovaně.
foreach
– iterace přes všechny řádky
Nejsnazší způsob, jak vykonat dotaz a získat řádky, je iterováním v cyklu foreach
. Automaticky spouští
SQL dotaz.
get($key): ?ActiveRow
Vykoná SQL dotaz a vrátí řádek podle primárního klíče, nebo null
, pokud neexistuje.
fetch(): ?ActiveRow
Vrací řádek a posune interní ukazatel na další. Pokud už neexistují další řádky, vrací null
.
fetchPairs(string|int|null $key = null, string|int|null $value = null): array
Vrátí výsledky jako asociativní pole. První argument určuje název sloupce, který se použije jako klíč v poli, druhý argument určuje název sloupce, který se použije jako hodnota:
Pokud uvedeme pouze první parametr, bude hodnotou celý řadek, tedy objekt ActiveRow
:
V případě duplicitních klíčů se použije hodnota z posledního řádku. Při použití null
jako klíče
bude pole indexováno numericky od nuly (pak ke kolizím nedochází):
fetchPairs(Closure $callback): array
Alternativně můžete jako parametr uvést callback, který bude pro každý řádek vracet buď samotnou hodnotu, nebo dvojici klíč-hodnota.
fetchAll(): array
Vrátí všechny řádky jako asociativní pole objektů ActiveRow
, kde klíče jsou hodnoty
primárních klíčů.
count(): int
Metoda count()
bez parametru vrací počet řádků v objektu Selection
:
Pozor, count()
s parametrem provádí agregační funkci COUNT v databázi, viz níže.
ActiveRow::toArray(): array
Převede objekt ActiveRow
na asociativní pole, kde klíče jsou názvy sloupců a hodnoty jsou
odpovídající data.
Agregace
Třída Selection
poskytuje metody pro snadné provádění agregačních funkcí (COUNT, SUM, MIN, MAX,
AVG atd.).
count($expr) |
Spočítá počet řádků |
min($expr) |
Vrátí minimální hodnotu ve sloupci |
max($expr) |
Vrátí maximální hodnotu ve sloupci |
sum($expr) |
Vrátí součet hodnot ve sloupci |
aggregation($function) |
Umožňuje provést libovolnou agregační funkci. Např. AVG() , GROUP_CONCAT() |
count(string $expr): int
Provede SQL dotaz s funkcí COUNT a vrátí výsledek. Metoda se používá k zjištění, kolik řádků odpovídá určité podmínce:
Pozor, count() bez parametru pouze vrací počet řádků v objektu Selection
.
min(string $expr) a max(string $expr)
Metody min()
a max()
vrací minimální a maximální hodnotu ve specifikovaném sloupci nebo
výrazu:
sum(string $expr)
Vrací součet hodnot ve specifikovaném sloupci nebo výrazu:
aggregation(string $function, ?string $groupFunction = null)
Umožňuje provést libovolnou agregační funkci.
Pokud potřebujeme agregovat výsledky, které už samy o sobě vzešly z nějaké agregační funkce a seskupení (např.
SUM(hodnota)
přes seskupené řádky), jako druhý argument uvedeme agregační funkci, která se má na tyto
mezivýsledky aplikovat:
V tomto příkladu nejprve vypočítáme celkovou cenu produktů v každé kategorii
(SUM(price * stock) AS category_total
) a seskupíme výsledky podle category_id
. Poté použijeme
aggregation('SUM(category_total)', 'SUM')
k sečtení těchto mezisoučtů category_total
. Druhý
argument 'SUM'
říká, že se má na mezivýsledky aplikovat funkce SUM.
Insert, Update & Delete
Nette Database Explorer zjednodušuje vkládání, aktualizaci a mazání dat. Všechny uvedené metody v případě vyhodí
výjimku Nette\Database\DriverException
.
Selection::insert(iterable $data)
Vloží nové záznamy do tabulky.
Vkládání jednoho záznamu:
Nový záznam předáme jako asociativní pole nebo iterable objekt (například ArrayHash používaný ve formulářích), kde klíče odpovídají názvům sloupců v tabulce.
Pokud má tabulka definovaný primární klíč, metoda vrací objekt ActiveRow
, který se znovunačte
z databáze, aby se zohlednily případné změny provedené na úrovni databáze (triggery, výchozí hodnoty sloupců,
výpočty auto-increment sloupců). Tím je zajištěna konzistence dat a objekt vždy obsahuje aktuální data z databáze.
Pokud jednoznačný primární klíč nemá, vrací předaná data ve formě pole.
Vkládání více záznamů najednou:
Metoda insert()
umožňuje vložit více záznamů pomocí jednoho SQL dotazu. V tomto případě vrací počet
vložených řádků.
Jako parametr lze také předat objekt Selection
s výběrem dat.
Vkládání speciálních hodnot:
Jako hodnoty můžeme předávat i soubory, objekty DateTime nebo SQL literály:
Selection::update(iterable $data): int
Aktualizuje řádky v tabulce podle zadaného filtru. Vrací počet skutečně změněných řádků.
Měněné sloupce předáme jako asociativní pole nebo iterable objekt (například ArrayHash používaný ve formulářích), kde klíče odpovídají názvům sloupců v tabulce:
Pro změnu číselných hodnot můžeme použít operátory +=
a -=
:
Selection::delete(): int
Maže řádky z tabulky podle zadaného filtru. Vrací počet smazaných řádků.
Při volání update()
a delete()
nezapomeňte pomocí where()
specifikovat řádky, které se mají upravit/smazat. Pokud where()
nepoužijete, operace se provede na celé
tabulce!
ActiveRow::update(iterable $data): bool
Aktualizuje data v databázovém řádku reprezentovaném objektem ActiveRow
. Jako parametr přijímá iterable
s daty, která se mají aktualizovat (klíče jsou názvy sloupců). Pro změnu číselných hodnot můžeme použít operátory
+=
a -=
:
Po provedení aktualizace se ActiveRow
automaticky znovu načte z databáze, aby se zohlednily případné změny
provedené na úrovni databáze (např. triggery). Metoda vrací true pouze pokud došlo ke skutečné změně dat.
Tato metoda aktualizuje pouze jeden konkrétní řádek v databázi. Pro hromadnou aktualizaci více řádků použijte metodu Selection::update().
ActiveRow::delete()
Smaže řádek z databáze, který je reprezentován objektem ActiveRow
.
Tato metoda maže pouze jeden konkrétní řádek v databázi. Pro hromadné smazání více řádků použijte metodu Selection::delete().
Vazby mezi tabulkami
V relačních databázích jsou data rozdělena do více tabulek a navzájem propojená pomocí cizích klíčů. Nette Database Explorer přináší revoluční způsob, jak s těmito vazbami pracovat – bez psaní JOIN dotazů a nutnosti cokoliv konfigurovat nebo generovat.
Pro ilustraci práce s vazbami použijeme příklad databáze knih (najdete jej na GitHubu). V databázi máme tabulky:
author
– spisovatelé a překladatelé (sloupceid
,name
,web
,born
)book
– knihy (sloupceid
,author_id
,translator_id
,title
,sequel_id
)tag
– štítky (sloupceid
,name
)book_tag
– vazební tabulka mezi knihami a štítky (sloupcebook_id
,tag_id
)

Struktura databáze
V našem příkladu databáze knih najdeme několik typů vztahů (byť model je zjednodušený oproti realitě):
- One-to-many 1:N – každá kniha má jednoho autora, autor může napsat několik knih
- Zero-to-many 0:N – kniha může mít překladatele, překladatel může přeložit několik knih
- Zero-to-one 0:1 – kniha může mít další díl
- Many-to-many M:N – kniha může mít několik tagů a tag může být přiřazen několika knihám
V těchto vztazích vždy existuje tabulka nadřazená a podřízená. Například ve vztahu mezi autorem a knihou je tabulka
author
nadřazená a book
podřízená – můžeme si to představit tak, že kniha vždy
„patří“ nějakému autorovi. To se projevuje i ve struktuře databáze: podřízená tabulka book
obsahuje
cizí klíč author_id
, který odkazuje na nadřazenou tabulku author
.
Potřebujeme-li vypsat knihy včetně jmen jejich autorů, máme dvě možnosti. Buď data získáme jediným SQL dotazem pomocí JOIN:
Nebo načteme data ve dvou krocích – nejprve knihy a pak jejich autory – a potom je v PHP poskládáme:
Druhý přístup je ve skutečnosti efektivnější, i když to může být překvapivé. Data jsou načtena pouze jednou a mohou být lépe využita v cache. Právě tímto způsobem pracuje Nette Database Explorer – vše řeší pod povrchem a vám nabízí elegantní API:
Přístup k nadřazené tabulce
Přístup k nadřazené tabulce je přímočarý. Jde o vztahy jako kniha má autora nebo kniha může mít
překladatele. Související záznam získáme přes property objektu ActiveRow – její název odpovídá názvu sloupce
s cizím klíčem bez id
:
Když přistoupíme k property $book->author
, Explorer v tabulce book
hledá sloupec, jehož
název obsahuje řetězec author
(tedy author_id
). Podle hodnoty v tomto sloupci načte odpovídající
záznam z tabulky author
a vrátí jej jako ActiveRow
. Podobně funguje i
$book->translator
, který využije sloupec translator_id
. Protože sloupec translator_id
může obsahovat null
, použijeme v kódu operátor ?->
.
Alternativní cestu nabízí metoda ref()
, která přijímá dva argumenty, název cílové tabulky a název
spojovacího sloupce, a vrací instanci ActiveRow
nebo null
:
Metoda ref()
se hodí, pokud nelze použít přístup přes property, protože tabulka obsahuje sloupec se
stejným názvem (tj. author
). V ostatních případech je doporučeno používat přístup přes property, který
je čitelnější.
Explorer automaticky optimalizuje databázové dotazy. Když procházíme knihy v cyklu a přistupujeme k jejich souvisejícím záznamům (autorům, překladatelům), Explorer negeneruje dotaz pro každou knihu zvlášť. Místo toho provede pouze jeden SELECT pro každý typ vazby, čímž výrazně snižuje zátěž databáze. Například:
Tento kód zavolá pouze tyto tři bleskové dotazy do databáze:
Logika dohledávání spojovacího sloupce je dána implementací Conventions. Doporučujeme použití DiscoveredConventions, které analyzuje cizí klíče a umožňuje jednoduše pracovat s existujícími vztahy mezi tabulkami.
Přístup k podřízené tabulce
Přístup k podřízené tabulce funguje v opačném směru. Nyní se ptáme jaké knihy napsal tento autor nebo
přeložil tento překladatel. Pro tento typ dotazu používáme metodu related()
, která vrátí
Selection
se souvisejícími záznamy. Podívejme se na příklad:
Metoda related()
přijímá popis spojení jako jeden argument s tečkovou notací nebo jako dva samostatné
argumenty:
Explorer dokáže automaticky detekovat správný spojovací sloupec na základě názvu nadřazené tabulky. V tomto
případě se spojuje přes sloupec book.author_id
, protože název zdrojové tabulky je author
:
Pokud by existovalo více možných spojení, Explorer vyhodí výjimku AmbiguousReferenceKeyException.
Metodu related()
můžeme samozřejmě použít i při procházení více záznamů v cyklu a Explorer
i v tomto případě automaticky optimalizuje dotazy:
Tento kód vygeneruje pouze dva bleskové SQL dotazy:
Vazba Many-to-many
Pro vazbu many-to-many (M:N) je potřeba existence vazební tabulky (v našem případě book_tag
), která
obsahuje dva sloupce s cizími klíči (book_id
, tag_id
). Každý z těchto sloupců odkazuje na
primární klíč jedné z propojovaných tabulek. Pro získání souvisejících dat nejprve získáme záznamy z vazební
tabulky pomocí related('book_tag')
a dále pokračujeme k cílovým datům:
Explorer opět optimalizuje SQL dotazy do efektivní podoby:
Dotazování přes související tabulky
V metodách where()
, select()
, order()
a group()
můžeme používat
speciální notace pro přístup k sloupcům z jiných tabulek. Explorer automaticky vytvoří potřebné JOINy.
Tečková notace (nadřazená_tabulka.sloupec
) se používá pro vztah 1:N z pohledu podřízené
tabulky:
Dvojtečková notace (:podřízená_tabulka.sloupec
) se používá pro vztah 1:N z pohledu nadřazené
tabulky:
Ve výše uvedeném příkladu s dvojtečkovou notací (:book.title
) není specifikován sloupec s cizím
klíčem. Explorer automaticky detekuje správný sloupec na základě názvu nadřazené tabulky. V tomto případě se spojuje
přes sloupec book.author_id
, protože název zdrojové tabulky je author
. Pokud by existovalo více
možných spojení, Explorer vyhodí výjimku AmbiguousReferenceKeyException.
Spojovací sloupec lze explicitně uvést v závorce:
Notace lze řetězit pro přístup přes více tabulek:
Rozšíření podmínek pro JOIN
Metoda joinWhere()
rozšiřuje podmínky, které se uvádějí při propojování tabulek v SQL za klíčovým
slovem ON
.
Dejme tomu, že chceme najít knihy přeložené konkrétním překladatelem:
V podmínce joinWhere()
můžeme používat stejné konstrukce jako v metodě where()
–
operátory, zástupné otazníky, pole hodnot či SQL výrazy.
Pro složitější dotazy s více JOINy můžeme definovat aliasy tabulek:
Všimněte si, že zatímco metoda where()
přidává podmínky do klauzule WHERE
, metoda
joinWhere()
rozšiřuje podmínky v klauzuli ON
při spojování tabulek.