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 = $connection->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.

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.

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.

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.

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: