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:

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():

Můžeme vytvořit také agregaci výsledků:

Čtení dat: