Database Explorer: 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.
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 Explorer 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
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
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 –
name is column in
author table. You can also create condition for book translator
(which is connected by
$selection = $context->table('book'); $selection->where('author.name LIKE ?', '%Jon%'); $selection->where('translator.name', 'David Grudl');
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
: (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
$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
translator_id. In the example above Selection joins over the
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');
Database Explorer is smart and escape parameters and identificators for you. You should just follow these basic rules:
- keywords, functions, procedures uppercase
- columns and tables lowercase
- set values via parameters
//WRONG!: //->where("name like ?", "John"); //generates: `name` `like` ? //CORRECT: ->where("name LIKE ?", "John"); //WRONG!: //->where("KEY = ?", $value); //KEY is keyword //CORRECT: ->where("key = ?", $value); //generates: `key` = ? //WRONG!: //->where("name = " . $name); //sql injection! //CORRECT: ->where("name = ?", $name);
Wrong usage could produce security wholes
See possibilities how to filter and restrict rows Selection:
||Set ORDER BY, can be expression
||Set retrieved columns, can be expression
||Passing parameters to function|
||Different way of passing parameters to function – mind the quotation marks|
||Set LIMIT and OFFSET|
||Set GROUP BY|
You can use fluent interface, for example
where conditions are connected with the
AND operator. For
OR operator use just one
$table->where('born > ? OR born IS NULL', $datetime);
Possible where() method arguments:
||field = $value|
||field IS NULL|
||field > $val|
||field IN (1, 2)|
||field IN (SELECT $primary FROM $tableName)|
||field IN (SELECT col FROM $tableName)|
Aggregate queries can also be created:
||Get number of rows|
||Get number of distinct values|
||Get minimum value|
||Get maximum value|
||Get the sum of all values|
||Run any aggregation function|
||Iterate all rows in result|
||Get single row with ID $id from table|
||Get next row from the result|
||Fetch all values to associative array|
||Fetch all rows to associative array|
||Get number of rows in result set|