Edit
Lang

Database\Table

We will learn the basics about database “table” layer in Nette Framework.

Nette\Database\Table 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\Table 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\Table does it.

Database\Table layer provides Selection instance which works as a data selection over one database table. Selection instance helps you to filter needed data and fetches them as an ActiveRow instances.

Creating Selection is quite easy, just call table() method on your database context. See how to get or create context instance in Nette\Database chapter.

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

Selection implements Traversable interface: you can just iterate over the instance to get all 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 Selection::get() method. It is “filtering” 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\Table 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 Database: 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 Database: 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 Database: 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 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))