Database Explorer

Nette Database Explorer significantly simplifies retrieving data from the database without writing SQL queries.

  • uses efficient queries
  • does not transmit unnecessary data
  • has elegant syntax

Using of Database Explorer starts with the table, just call table() on the Nette\Database\Context object. The easiest way to get it is described here, but if we use Nette Database Explorer alone, it can be manually created.

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

It returns object Selection, we can iterate over it and pass through all the books. The rows are ActiveRow instances; you can read row data from their 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 = $context->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 common 1:N relationship. The often used solution is to fetch data by 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 books, and another for the needed authors – and this is just 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.

Database structure for examples

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

$books = $context->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:

$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->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(...). Multiple where or whereOr conditions are connected with the AND operator.

Possible where() usage. Nette Database Explorer can automatically add needed operators for passed values:

$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('id = ? OR name = ?', 1, $name) id = 1 OR name = ‘Jon Snow’
$table->where('field', $context->table($tableName)) field IN (SELECT $primary FROM $tableName)
$table->where('field', $context->table($tableName)->select('col')) field IN (SELECT col FROM $tableName)

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

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

$books = $context->table('book');
$books->where('author.name LIKE ?', '%Jon%');
$books->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.

$authors = $context->table('author');
$authors->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.

$authors = $context->table('author');
$authors->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.

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

Aggregate queries

$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

Escaping & quoting

Database Explorer is smart and escape parameters and quotes identificators for you. You should just follow these basic rules:

  • keywords, functions, procedures uppercase
  • columns and tables lowercase
  • set values via parameters
->where('name like ?', 'John'); // WRONG! generates: `name` `like` ?
->where('name LIKE ?', 'John'); // CORRECT

->where('KEY = ?', $value); // WRONG! KEY is 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! set values via parameters
->select('DATE_FORMAT(created, ?)', '%d.%m.%Y'); // CORRECT

Wrong usage could produce security wholes

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

Insert, Update & Delete

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

$row = $context->table('author')->insert([
    'name' => 'Ramsay Snow',
    'born' => null
]);
// INSERT INTO `author` (`name`, `born`) VALUES ('Ramsay Snow', null)

If primary key is defined in table, it returns inserted row as ActiveRow object.

Multiple insert:

$context->table('author')->insert([
    [
        'name' => 'Sansa Stark',
        'born' => null
    ], [
        'name' => 'Arya Stark',
        'born' => null
    ]
]);
// INSERT INTO `author` (`name`, `born`) VALUES ('Sansa Stark', null), ('Arya Stark', null)

Updating (returns count of affected rows):

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

Deleting (returns count of deleted rows):

$count = $context->table('author')
    ->where('id', 10)
    ->delete();
// DELETE FROM `author` 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 = $context->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 = $context->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 = $context->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 = $context->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

Manual creating Context

If we've created a database connection using the application configuration, we do not have to worry about it. We have created a Nette\Database\Context service that we can pass through the DI.

However, if we use Nette Database Explorer separately, we need to create the Nette\Database\Context instance manually.

// $storage implements Nette\Caching\IStorage:
$storage = new Nette\Caching\FileStorage($tempDir);
$connection = new Nette\Database\Connection($dsn, $user, $password);
$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$context = new Nette\Database\Context($connection, $structure, $conventions, $storage);