Database Explorer

To use Database Explorer, start with a table – call table() on a Nette\Database\Explorer object. The easiest way to get a context object instance is described here, or, for case when Nette Database Explorer is used as a standalone tool, it can be created manually.

$books = $explorer->table('book'); // db table name is 'book'

The call returns an instance of Selection object, that can be iterated over to retrieve all the books. Each item (a row) is represented by an instance of ActiveRow with data mapped to its properties:

foreach ($books as $book) {
	echo $book->title;
	echo $book->author_id;
}

Getting just one specific row is done by get() method, which directly returns an ActiveRow instance.

$book = $explorer->table('book')->get(2); // returns book with id 2
echo $book->title;
echo $book->author_id;

Let's take a look at common use-case. You need to fetch books and their authors. It is a common 1:N relationship. The often used solution is to fetch data using one SQL query with table joins. The second possibility is to fetch data separately, run one query for getting books and then get an author for each book by another query (e.g. in your foreach cycle). This could be easily optimized to run only two queries, one for the books, and another for the needed authors – and that is exactly the way how Nette Database Explorer does it.

In the examples below, we will work with the database schema in the figure. There are OneHasMany (1:N) links (author of book author_id and possible translator translator_id, which may be null) and ManyHasMany (M:N) link between book and its tags.

An example, including a schema, is found on GitHub.

Database structure used in the examples

The following code lists the author's name for each book and all its tags. We will discuss in a moment how this works internally.

$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'title:      ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author is row from table 'author'

	echo 'tags: ';
	foreach ($book->related('book_tag') as $bookTag) {
		echo $bookTag->tag->name . ', '; // $bookTag->tag is row from table 'tag'
	}
}

You will be pleased how efficiently the database layer works. The example above makes a constant number of requests that look like this:

SELECT * FROM `book`
SELECT * FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT * FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

If you use cache (defaults on), no columns will be queried unnecessarily. After the first query, cache will store the used column names and Nette Database Explorer will run queries only with the needed columns:

SELECT `id`, `title`, `author_id` FROM `book`
SELECT `id`, `name` FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT `book_id`, `tag_id` FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT `id`, `name` FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Selections

See possibilities how to filter and restrict rows Nette\Database\Table\Selection:

We can use a so-called fluent interface, for example $table->where(...)->order(...)->limit(...). Multiple where or whereOr conditions are linked by the AND operator.

where()

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

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

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

This feature allows to generate correct operator based on value:

$table->where('id ?', 2);    // id = 2
$table->where('id ?', null); // id IS NULL
$table->where('id', $ids);   // id IN (...)

Selection correctly handles also negative conditions, works for empty arrays too:

$table->where('id', []);       // id IS NULL AND FALSE
$table->where('id NOT', []);   // id IS NULL OR TRUE
$table->where('NOT (id ?)', $ids);  // NOT (id IS NULL AND FALSE)

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

whereOr()

Example of use without parameters:

// WHERE (user_id IS NULL) OR (SUM(`field1`) > SUM(`field2`))
$table->whereOr([
	'user_id IS NULL',
	'SUM(field1) > SUM(field2)',
]);

We use the parameters. If you do not specify an operator, Nette Database Explorer will automatically add the appropriate one:

// WHERE (`field1` IS NULL) OR (`field2` IN (3, 5)) OR (`amount` > 11)
$table->whereOr([
	'field1' => null,
	'field2' => [3, 5],
	'amount >' => 11,
]);

The key can contain an expression containing wildcard question marks and then pass parameters in the value:

// WHERE (`id` > 12) OR (ROUND(`id`, 5) = 3)
$table->whereOr([
	'id > ?' => 12,
	'ROUND(id, ?) = ?' => [5, 3],
]);

order()

Examples of use:

$table->order('field1');               // ORDER BY `field1`
$table->order('field1 DESC, field2');  // ORDER BY `field1` DESC, `field2`
$table->order('field = ? DESC', 123);  // ORDER BY `field` = 123 DESC

select()

Examples of use:

$table->select('field1');                 // SELECT `field1`
$table->select('col, UPPER(col) AS abc'); // SELECT `col`, UPPER(`col`) AS abc
$table->select('SUBSTR(title, ?)', 3);    // SELECT SUBSTR(`title`, 3)

limit()

Examples of use:

$table->limit(1);     // LIMIT 1
$table->limit(1, 10); // LIMIT 1 OFFSET 10

page()

An alternative way to set the limit and offset:

$page = 5;
$itemsPerPage = 10;
$table->page($page, $itemsPerPage); // LIMIT 10 OFFSET 40

Getting the last page number, passed to the $lastPage variable:

$table->page($page, $itemsPerPage, $lastPage);

group()

Examples of use:

$table->group('field1');          // GROUP BY `field1`
$table->group('field1, field2');  // GROUP BY `field1`, `field2`

having()

Examples of use:

$table->having('COUNT(items) >', 100);  // HAVING COUNT(`items`) > 100

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 to write them anymore.

Let's say you need to get all books whose author's name is ‘Jon’. All you need to write is the joining key of the relation and the column name in the joined table. The joining key is derived from the column which refers to the table you want to join. In our example (see the db schema) it is the column author_id, and it is sufficient to use just the first part of it – author (the _id suffix can be omitted). name is a column in the author table we would like to use. A condition for book translator (which is connected by translator_id column) can be created just as easily.

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

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

The relationship between book and its author is 1:N. The reverse relationship is also possible. We call it backjoin. Take a look at another example. We would like to fetch all authors, who have 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 logical too, as two dots are more than one dot). Unfortunately, the Selection class isn't smart enough, so we have to help with the aggregation and provide a GROUP BY statement, also the condition has to be written in form of HAVING statement.

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

You may have noticed that the joining expression refers to the book, but it's not clear, whether we are joining through author_id or translator_id. In the example above, Selection joins through the author_id column because a match with the source table has been found – the author table. If there was no such a match and there would be more possibilities, Nette would throw AmbiguousReferenceKeyException.

To make a join through translator_id column, provide an optional parameter within the joining expression.

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

Let's take a look at 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.

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

Aggregate Queries

The count() method without any specified parameters selects all records and returns the array size, which is very inefficient. For example, if you need to calculate the number of rows for paging, always specify the first argument.

Escaping & Quoting

Database Explorer is smart and escape parameters and quotes identificators for you. These basic rules need to be followed, though:

• keywords, functions, procedures must be uppercase
• columns and tables must be lowercase
• pass variables as parameters, do not concatenate

->where('name like ?', 'John'); // WRONG! generates: `name` `like` ?
->where('name LIKE ?', 'John'); // CORRECT

->where('KEY = ?', $value); // WRONG! KEY is a keyword
->where('key = ?', $value); // CORRECT. generates: `key` = ?

->where('name = ' . $name); // WRONG! sql injection!
->where('name = ?', $name); // CORRECT

->select('DATE_FORMAT(created, "%d.%m.%Y")'); // WRONG! pass variables as parameters, do not concatenate
->select('DATE_FORMAT(created, ?)', '%d.%m.%Y'); // CORRECT

Wrong usage can produce security holes

Fetching Data

Insert, Update & Delete

Method insert() accepts array or Traversable objects (for example ArrayHash which returns forms):

$row = $explorer->table('users')->insert([
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

If primary key is defined on the table, an ActiveRow object containing the inserted row is returned.

Multiple insert:

$explorer->table('users')->insert([
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)

Files or DateTime objects can be passed as parameters:

$explorer->table('users')->insert([
	'name' => $name,
	'created' => new DateTime, // or $explorer::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts the file
]);

Updating (returns the count of affected rows):

$count = $explorer->table('users')
	->where('id', 10) // must be called before update()
	->update([
		'name' => 'Ned Stark'
	]);
// UPDATE `users` SET `name`='Ned Stark' WHERE (`id` = 10)

For update we can use operators += a -=:

$explorer->table('users')
	->update([
		'age+=' => 1, // see +=
	]);
// UPDATE users SET `age` = `age` + 1

Deleting (returns the count of deleted rows):

$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE (`id` = 10)

Working with Relationships

Has One Relation

Has one relation is a common use-case. Book has one author. Book has one translator. Getting related row is mainly done by ref() method. It accepts two arguments: target table name and source joining column. See example:

$book = $explorer->table('book')->get(1);
$book->ref('author', 'author_id');

In example above we fetch related author entry from author table, the author primary key is searched by book.author_id column. Ref() method returns ActiveRow instance or null if there is no appropriate entry. Returned row is an instance of ActiveRow so we can work with it the same way as with the book entry.

$author = $book->ref('author', 'author_id');
$author->name;
$author->born;

// or directly
$book->ref('author', 'author_id')->name;
$book->ref('author', 'author_id')->born;

Book also has one translator, so getting translator name is quite easy.

$book->ref('author', 'translator_id')->name

All of this is fine, but it's somewhat cumbersome, don't you think? Database Explorer already contains the foreign keys definitions so why not use them automatically? Let's do that!

If we call property, which does not exist, ActiveRow tries to resolve the calling property name as ‘has one’ relation. Getting this property is the same as calling ref() method with just one argument. We will call the only argument the key. Key will be resolved to particular foreign key relation. The passed key is matched against row columns, and if it matches, foreign key defined on the matched column is used for getting data from related target table. See example:

$book->author->name;
// same as
$book->ref('author')->name;

The ActiveRow instance has no author column. All book columns are searched for a match with key. Matching in this case means the column name has to contain the key. So in the example above, the author_id column contains string ‘author’ and is therefore matched by ‘author’ key. If you want to get the book translator, just can use e.g. ‘translator’ as a key, because ‘translator’ key will match the translator_id column. You can find more about the key matching logic in Joining expressions chapter.

echo $book->title . ': ';
echo $book->author->name;
if ($book->translator) {
	echo ' (translated by ' . $book->translator->name . ')';
}

If you want to fetch multiple books, you should use the same approach. Nette Database Explorer will fetch authors and translators for all the fetched books at once.

$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	if ($book->translator) {
		echo ' (translated by ' . $book->translator->name . ')';
	}
}

The code will run only these 3 queries:

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- ids of fetched books from author_id column
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- ids of fetched books from translator_id column

Has Many Relation

‘Has many’ relation is just reversed ‘has one’ relation. Author has written many books. Author has translated many books. As you can see, this type of relation is a little bit more difficult because the relation is ‘named’ (‘written’, ‘translated’). ActiveRow instance has related() method, which will return array of related entries. Entries are also ActiveRow instances. See example bellow:

$author = $explorer->table('author')->get(11);
echo $author->name . ' has written:';

foreach ($author->related('book.author_id') as $book) {
	echo $book->title;
}

echo 'and translated:';
foreach ($author->related('book.translator_id') as $book) {
	echo $book->title;
}

Method related() method accepts full join description passed as two arguments or as one argument joined by dot. The first argument is the target table, the second is the target column.

$author->related('book.translator_id');
// same as
$author->related('book', 'translator_id');

You can use Nette Database Explorer heuristics based on foreign keys and provide only key argument. Key will be matched against all foreign keys pointing into the current table (author table). If there is a match, Nette Database Explorer will use this foreign key, otherwise it will throw Nette\InvalidArgumentException or AmbiguousReferenceKeyException. You can find more about the key matching logic in Joining expressions chapter.

Of course you can call related methods for all fetched authors, Nette Database Explorer will again fetch the appropriate books at once.

$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' has written:';
	foreach ($author->related('book') as $book) {
		$book->title;
	}
}

Example above will run only two queries:

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- ids of fetched authors

Creating Explorer Manually

A database connection can be created using the application configuration. In such cases a Nette\Database\Explorer service is created and can be passed as a dependency using the DI container.

However, if Nette Database Explorer is used as a standalone tool, an instance of Nette\Database\Explorer object needs to be created manually.

// $storage implements Nette\Caching\Storage:
$storage = new Nette\Caching\Storages\FileStorage($tempDir);
$connection = new Nette\Database\Connection($dsn, $user, $password);
$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$explorer = new Nette\Database\Explorer($connection, $structure, $conventions, $storage);