Edit
Lang

Database: ActiveRow

Every row returned by Selection is represented by ActiveRow instance. This class provides standard interface for accessing fetched columns. You can read data as properties or by array access. If column does not exist, MemberAccessException is thrown. You can check if column is set by standard isset construct.

echo $book->id;
echo $book['title'];

isset($book->id);
isset($book['title']);

Be careful, isset will return FALSE even if the column was fetched, but the value is NULL.

Relations

The main Nette\Database\Table principle is to fetch data from different tables separately. This behavior is maintained by Nette\Database itself, row's related data will be fetched for all other rows at once. We have two simple types of relation: “has one” and “has many”.

We will use DiscoveredConventions, which fetches relationships right from your database.

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. Ref() method 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 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 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;
}

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