Edit
Lang

Database: Selection

Nette\Database\Table\Selection allows you to select and filter all table rows. Selection power is hidden in writing conditions, which automatically creates needed table joins. You will be pleased not to write table joins anymore.

Filtering

Let's go through the basic Selection API. You can specify basic conditions for your selection by calling where() method. Substitute all values by ?.

$selection->where('name = ?', $name);
$selection->where('born > ?', $datetime);

Nette\Database can automatically add needed operators for passed values:

$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

Calling where() method multiple times joins the conditions by AND operator.

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

If you need the OR operator, write your condition at once or use the whereOr() method.

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

You can provide placeholder even without column operator. These calls are almost the same.

$selection->where('id = ? OR id = ?', 1, 2);
$selection->where('id ? OR id ?', 1, 2);

Be careful! If you do not provide a column operator, the value could have modify the intended behavior.

Selection correctly handles also negative conditions, works for arrays too.

$selection->where('NOT id', 1);
$selection->where('id NOT', 1); // the same

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

// this will throws an exception, this syntax is not supported
$selection->where('NOT id ?', $ids);

Filtering by another table value

Quite often you need filter results by some condition which involves another database table. These types of condition require table join. However, you don't need write them anymore.

Let's say, you need to get all book which has author whose name is “Jon”. All you need to write is just the “joining key” of the relation and column name in the joined table. “Joining key” is derived from the column which refers to table you want to join. Our example (see db schema) has column author_id, so we can use just part of it – author. name is column in author table. You can also create condition for book translator (which is connected by translator_id column).

$selection = $context->table('book');
$selection->where('author.name LIKE ?', '%Jon%');
$selection->where('translator.name', 'David Grudl');

The “joining key” logic is driven by implementation of IConventions. We encourage to use DiscoveredConventions, which analyze your foreign keys and allows you easily work with these relationships.

The relationship between book and its author is 1:N. The reverse relationships is also possible. We call it backjoin. Take a look on another example. We would like to fetch all authors, who has written more than 3 books. To make the join reverse we use : (colon). Colon means that the joined relationship means “hasMany” (and it's quite logic, two dots is more than one). Unfortunately, Selection isn't smart enough, so we must help with the aggregation and provide some GROUP BY statement, also the condition must be written as the HAVING statement.

$selection = $context->table('author');
$selection->group('author.id')
          ->having('COUNT(:book.id) > 3');

Maybe you have noticed that the joining expression refers to the book, but it's not clear, if we are joining over author_id or translator_id. In the example above Selection joins over the author_id column, because Selection have found match with the source table – table author. If there wouldn't be a match and would there be more possibilities, Nette would throw AmbiguousReferenceKeyException.

To make join over translator_id, just provide optional “parameter” into “joining expression”.

$selection = $context->table('author');
$selection->group('author.id')
          ->having('COUNT(:book(translator).id) > 3')

Let's take a look on some more difficult joining expression.

We would like to find all authors who have written something about PHP. All books have tags so we should select those authors who have written any book with the PHP tag.

$selection = $context->table('author');
$selection->where(':book:book_tags.tag.name', 'PHP')
          ->group('author.id')
          ->having('COUNT(:book:book_tags.tag.id) > 0');

API

See possibilities how to filter and restrict rows Selection:

$table->where($where[, $param[, ...]]) Set WHERE using AND as a glue if two or more conditions are supplied
$table->whereOr($where) Set WHERE using OR as a glue if two or more conditions are supplied
$table->order($columns) Set ORDER BY, can be expression ('column DESC, id DESC')
$table->select($columns) Set retrieved columns, can be expression ('col, MD5(col) AS hash')
$table->select("CONCAT(?, ?) AS baz", "foo", "Bar") Passing parameters to function
$table->select('CONCAT("foo", "Bar") AS baz') Different way of passing parameters to function – mind the quotation marks
$table->limit($limit[, $offset]) Set LIMIT and OFFSET
$table->group($columns) Set GROUP BY
$table->having($having) Set HAVING

You can use fluent interface, for example $table->where(...)->order(...)->limit(...). More where or whereOr conditions are connected with the AND operator.

Possible where() method arguments:

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

Aggregate queries can also be created:

$table->count("*") Get number of rows
$table->count("DISTINCT $column") Get number of distinct values
$table->min($column) Get minimum value
$table->max($column) Get maximum value
$table->sum($column) Get the sum of all values
$table->aggregation("GROUP_CONCAT($column)") Run any aggregation function

Fetching data:

foreach ($table as $id => $row) Iterate all rows in result
$row = $table->get($id) Get single row with ID $id from table
$row = $table->fetch() Get next row from the result
$array = $table->fetchPairs($key, $value) Fetch all values to associative array
$array = $table->fetchPairs($key) Fetch all rows to associative array
count($table) Get number of rows in result set