Edit
Lang

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 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 automaticky přidá vhodné operátory podle toho, jaká data dostane:

$name = 'Jon Snow';
$ids  = [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.

// id = 1 AND name = 'Jon Snow'
$selection->where('id', 1)->where('name', $name);
$selection->where('id = ? AND name = ?', 1, $name);

Pokud potřebujeme operátor OR, musíme podmínku napsat najednou nebo použijeme metodu whereOr().

// id = 1 OR name = 'Jon Snow'
$selection->where('id = ? OR name = ?', 1, $name);
$selection->whereOr([
          'id' => 1,
          'name' => $name,
]);

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);

Opatrně, pokud nepřidáme operátor pro sloupec, může předaná hodnota ovlivnit zamýšlené chování.

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 = [];
$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í 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');

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 s použitím AND jako spojovatele při více než jedné podmínce
$table->whereOr($where) Nastaví WHERE s použitím OR jako spojovatele při více než jedné podmínce
$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 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 nebo whereOr podmínek je spojeno pomocí operátoru AND. Pro operátor OR musíme použít pouze jedno volání where():

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", [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