Database: Selection
Nette\Database\Table\Selection nám umožňuje vybírat a filtrovat řádky databázových tabulek. Síla třídy Selection spočívá v psaní podmínek, které automaticky spojí tabulky pomocí JOIN v SQL dotazu. Už nikdy nebude potřeba spojovat tabulky ručně.
Filtrování
Pojďme se podívat na základní API Selection. Jednoduché podmínky můžeme vytvořit zavoláním metody where(). Otazníky v dotazu budou nahrazeny obsahem proměnných.
$selection->where('name = ?', $name);
$selection->where('born > ?', $datetime);
Nette\Database automaticky přidá vhodné operátory podle toho, jaká data dostane:
$name = 'Jon Snow';
$ids = array(1, 2, 3);
$null = NULL;
$selection->where('name', $name); // name = 'Jon Snow'
$selection->where('id', $ids); // id IN (1, 2, 3)
$selection->where('born', $null); // born IS NULL
Vícenásobné volání metody where()
spojí podmínky pomocí operátoru AND
. Pokud potřebujeme
operátor OR
, musíme podmínku napsat najednou.
// id = 1 AND name = 'Jon Snow'
$selection->where('id', 1)->where('name', $name);
$selection->where('id = ? AND name = ?', 1, $name);
// id = 1 OR name = 'Jon Snow'
$selection->where('id = ? OR name = ?', 1, $name);
Opatrně, pokud nepřidáme operátor pro sloupec, může předaná hodnota ovlivnit zamýšlené chování.
Zástupný symbol (otazník) funguje i bez sloupcového operátoru. Následující volání jsou prakticky stejná.
$selection->where('id = ? OR id = ?', 1, 2);
$selection->where('id ? OR id ?', 1, 2);
Selection správně zpracovává i záporné podmínky a umí pracovat také s poli.
$selection->where('NOT id', 1);
$selection->where('id NOT', 1); // to samé
$ids = array();
$selection->where('id', $ids); // id IS NULL AND FALSE
$selection->where('id NOT', $ids); // id IS NULL OR FALSE
$selection->where('NOT (id ?)', $ids); // NOT (id IS NULL AND FALSE)
// toto způsobí výjimku, tato syntax není podporovaná
$selection->where('NOT id ?', $ids);
Filtrování hodnotou z jiné tabulky
Často potřebujeme filtrovat výsledky pomocí podmínky, která zahrnuje jinou databázovou tabulku. Tento typ podmínek vyžaduje spojení tabulek, s Nette\Database už je ale nikdy nemusíme psát ručně.
Řekněme, že chceme vybrat všechny knihy, které napsal autor jménem „Jon“. Musíme napsat pouze jméno „spojovacího
klíče“ relace a název sloupce spojené tabulky. „Spojovací klíč“ je odvozen od jména sloupce, který odkazuje na
tabulku, se kterou se chceme spojit. V našem příkladu (viz databázové schéma) je to sloupec author_id
, ze
kterého stačí použít část – author
. name
je název sloupce v tabulce author
.
Můžeme vytvořit podmínku také pro překladatele knihy, který je připojen sloupcem translator_id
.
$selection = $context->table('book');
$selection->where('author.name LIKE ?', '%Jon%');
$selection->where('translator.name', 'David Grudl');
Logika vytváření „spojovacího klíče“ je dána implementací IReflection. Doporučujeme použití DiscoveredReflection, které analyzuje cizí klíče a umožňuje jednoduše pracovat se vztahy mezi tabulkami.
Vztah mezi knihou a autorem je 1:N. Obrácený vztah je také možný, nazýváme ho backjoin. Podívejme se na
následující příklad. Chceme vybrat všechny autory, kteří napsali více než tři knihy. Pro vytvoření obráceného
spojení použijeme :
(dvojtečku). Dvojtečka znamená, že jde o vztah „hasMany“ (a je to logické, dvě
tečky jsou více než jedna). Bohužel třída Selection není dostatečně chytrá a musíme mu pomoci s agregací výsledků a
předat mu část GROUP BY
, také podmínka musí být zapsaná jako HAVING
.
$selection = $context->table('author');
$selection->group('author.id')
->having('COUNT(:book.id) > 3');
Možná jste si všimli, že spojovací výraz odkazuje na book, ale není jasné, jestli spojujeme přes
author_id
nebo translator_id
. Ve výše uvedeném příkladu Selection spojuje přes sloupec
author_id
, protože byla nalezena shoda se jménem zdrojové tabulky – tabulky author
. Pokud by
neexistovala shoda a existovalo více možností, Nette vyhodí výjimku AmbiguousReferenceKeyException.
Abychom mohli spojovat přes translator_id
, stačí přidat volitelný „parametr“ do spojovacího výrazu.
$selection = $context->table('author');
$selection->group('author.id')
->having('COUNT(:book(translator).id) > 3')
Teď se podívejme na složitější příklad na skládání tabulek.
Chceme vybrat všechny autory, kteří napsali něco o PHP. Všechny knihy mají štítky, takže chceme vybrat všechny autory, kteří napsali knihu se štítkem „PHP“.
$selection = $context->table('author');
$selection->where(':book:book_tags.tag.name', 'PHP')
->group('author.id')
->having('COUNT(:book:book_tags.tag.id) > 0');
API
Podívejme se na různé možnosti filtrování a omezování výběru pomocí API třídy Selection:
$table->where($where[, $param[, ...]]) |
Nastaví WHERE |
$table->order($columns) |
Nastaví ORDER BY, může být výraz ('column DESC, id DESC') |
$table->select($columns) |
Nastaví vrácené sloupce, může být výraz ('col, MD5(col) AS hash') |
$table->limit($limit[, $offset]) |
Nastaví LIMIT a OFFSET |
$table->group($columns) |
Nastaví GROUP BY |
$table->having($having) |
Nastaví HAVING |
Můžeme použít tzv. fluent interface, například $table->where(...)->order(...)->limit(...)
. Více
where
podmínek je spojeno pomocí operátoru AND
. Pro operátor OR
musíme použít pouze
jedno volání where()
:
$table->where('born > ? OR born IS NULL', $datetime);
Možné argumenty metody where():
$table->where("field", $value) |
field = $value |
$table->where("field", NULL) |
field IS NULL |
$table->where("field > ?", $val) |
field > $val |
$table->where("field", array(1, 2)) |
field IN (1, 2) |
$table->where("field", $conn->table($tableName)) |
field IN (SELECT $primary FROM $tableName) |
$table->where("field", $conn->table($tableName)->select('col')) |
field IN (SELECT col FROM $tableName) |
Můžeme vytvořit také agregaci výsledků:
$table->count("*") |
Vrátí počet řádků |
$table->count("DISTINCT $column") |
Vrátí počet odlišných hodnot |
$table->min($column) |
Vrátí minimální hodnotu |
$table->max($column) |
Vrátí maximální hodnotu |
$table->sum($column) |
Vrátí součet všech hodnot |
$table->aggregation("GROUP_CONCAT($column)") |
Pro jakoukoliv jinou agregační funkci |
Čtení dat:
foreach ($table as $id => $row) |
Iteruje přes všechny řádky výsledku |
$row = $table->get($id) |
Vrátí jeden řádek s ID $id |
$row = $table->fetch() |
Vrátí následující řádek výsledku |
$array = $table->fetchPairs($key, $value) |
Vrátí všechny výsledky jako asociativní pole |
$array = $table->fetchPairs($key) |
Vrátí všechny řádky jako asociativní pole |
count($table) |
Vrátí počet řádků výsledku |