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