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

Database Explorer

We will learn the basics about Database Explorer layer in Nette Framework.

Database Explorer layer helps you to fetch database data more easily and in a more optimized way. The primary attitude is to fetch data only from one table and fetch them at once. The data are fetched into ActiveRow instances. Data from other tables connected by relationships are delivered by another queries – this is maintained by Database Explorer layer itself.

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 implementation fetches 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.

Selecting data starts with the table, just call $context->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 |#Manual creating Nette\Database\Context

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

We can simply iterate over the selection and pass through all the books. The rows are fetched as ActiveRow instances; you can read row data from their properties.

$books = $context->table('book');
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;

Working with relationships

As we mentioned in the chapter intro, Database Explorer layer maintains the table relations for you. There are two possibilities how and where you can work with relationships.

  1. Filtering rows fetched by Selection. In the introduction we stated the basic principle to select data only from one database table at once. However, Selection instance can do a table join to filter selected row. For example you need select only that authors who has written more than 2 books. For detailed overview see Selection chapter.
  2. Getting related data for fetched ActiveRows. We denied getting data from more than one table at once. Sadly, printing author_id is not good enough. We need to get full author database row, ideally fetched as ActiveRow. Getting this type of relationships is maintained by ActiveRow. For detailed overview see ActiveRow chapter.

In provided examples we will work with this database schema below. There are common OneHasMany and ManyHasMany relationships. OneHasMany relationship is doubled, a book must have an author and could have a translator (translator_id could be a NULL).

Database structure for examples

In example below we are getting related data for fetched books. In author property (of book ActiveRow instances) is available another ActiveRow instance, which represents author of the book. Getting book_tag instances is done by related() method, which returns collection of this instances. In the cycle we get the tag name from another ActiveRow instance available in book_tag instance. For detailed overview of getting related data see ActiveRow chapter.

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

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

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

You will be pleased how efficiently the database layer works. The example above performs constant number of queries, see following 4 queries:

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

Manual creating Nette\Database\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);