Database Explorer: 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 třídy 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 Explorer 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 Explorer 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í IConventions. Doporučujeme použití DiscoveredConventions, 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');
Zapisování dotazů
Database Explorer umí chytře escapovat parametry a identifikátory. Pro správnou funkčnost je ale nutno dodržovat několik pravidel:
- klíčová slova, názvy funkcí, procedur apod. psát velkými písmeny
- názvy sloupečků a tabulek psát malými písmeny
- hodnoty dosazovat přes parametry
//SPATNE!:
//->where("name like ?", "John"); //vygeneruje: `name` `like` ?
//spravne:
->where("name LIKE ?", "John");
//SPATNE!:
//->where("KEY = ?", $value); //KEY je klíčové slovo
//spravne:
->where("key = ?", $value); //vygeneruje: `key` = ?
//SPATNE!:
//->where("name = " . $name); //sql injection!
//spravne:
->where("name = ?", $name);
Špatné použití může vést k bezpečnostním dírám v aplikaci.
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->select("CONCAT(?, ?) AS baz", "foo", "Bar") |
Předá parametry do funkce |
$table->select('CONCAT("foo", "Bar") AS baz') |
Další možnost předání parametrů do funkce – pozor na znaménka uvozovek |
$table->limit($limit[, $offset]) |
Nastaví LIMIT a OFFSET |
$table->group($columns) |
Nastaví GROUP BY |
$table->having($having) |
Nastaví HAVING |
Nejen v metodě where
můžeme použít parametry. Parametry jsou podporovány i v metodách
order, select, group a having
. A pokud do těchto metod dosazujeme hodnotu, je nutné parametry použít, aby Nette
Database Explorer správně dotaz escapovalo (viz zapisování
dotazů:
$table->select('DATE_FORMAT(created, ?)', '%d.%m.%Y')
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 |
Metoda count()
bez uvedeného parametru vybere všechny záznamy a vrátí velikost pole, což je
velmi neefektivní. Pokud potřebujete například spočítat počet řádků pro stránkování, vždy první argument
uveďte.
Č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 |