You are browsing the unmaintained documentation for old Nette 2.0. See documentation for current Nette.

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  = 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

Calling where() method multiple times joins the conditions by AND operator. If you need OR operator, write your condition at once.

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

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

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

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

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

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

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

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

Filtering by another table value

API

See possibilities how to filter and restrict rows Selection:

$table->where($where[, $param[, ...]]) Set WHERE
$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->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 conditions are connected with the AND operator. For OR operator use just one where() call:

$table->where('born > ? OR born IS NULL', $datetime);

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

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