Database Explorer
*****************

<div class=perex>

With Nette Database, you can work in two ways: either let SQL queries be generated automatically (Explorer approach) or write them yourself ([Direct access|direct-sql]). The Explorer significantly simplifies data access. It handles relationships between tables so you can focus on the logic of your application.

- Working with data is natural and easy to understand
- Generates optimized SQL queries that fetch only the necessary data
- Provides easy access to related data without the need to write JOIN queries
- Works immediately without any configuration or entity generation

</div>

Working with the Explorer begins by calling the table() method on the [api:Nette\Database\Explorer] object (for information on creating connections and configuration, see the [dedicated page |guide#Connection and Configuration]):

.[warning]
In version 3.0 the class was called `Nette\Database\Context`.

```php
$books = $explorer->table('book'); // 'book' is the table name
```

The method returns a [Selection |api:Nette\Database\Table\Selection] object, which represents an SQL query. Additional methods can be chained to this object for filtering and sorting results. The query is assembled and executed only when the data is requested, for example, by iterating with `foreach`. Each row is represented by an [ActiveRow |api:Nette\Database\Table\ActiveRow] object:

```php
foreach ($books as $book) {
	echo $book->title;        // outputs 'title' column
	echo $book->author_id;    // outputs 'author_id' column
}
```

Explorer greatly simplifies working with [table relationships |#Relationships Between Tables]. The following example shows how easily we can output data from related tables (books and their authors). Notice that no JOIN queries need to be written; Nette generates them for us:

```php
$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // creates a JOIN to the 'author' table
}
```

Nette Database Explorer optimizes queries for maximum efficiency. The above example performs only two SELECT queries, regardless of whether we process 10 or 10,000 books.

Additionally, Explorer tracks which columns are used in the code and fetches only those from the database, saving further performance. This behavior is fully automatic and adaptive. If you later modify the code to use additional columns, Explorer automatically adjusts the queries. You don’t need to configure anything or think about which columns will be needed — leave that to Nette.


Filtering and Sorting
=====================

The `Selection` class provides methods for filtering and sorting data.

.[language-php]
| `where($condition, ...$params)` | Adds a WHERE condition. Multiple conditions are combined using AND |
| `whereOr(array $conditions)`    | Adds a group of WHERE conditions combined using OR                |
| `wherePrimary($value)`          | Adds a WHERE condition based on the primary key                   |
| `order($columns, ...$params)`   | Sets sorting with ORDER BY                                        |
| `select($columns, ...$params)`  | Specifies which columns to fetch                                  |
| `limit($limit, $offset = null)` | Limits the number of rows (LIMIT) and optionally sets OFFSET      |
| `page($page, $itemsPerPage, &$total = null)` | Sets pagination                                      |
| `group($columns, ...$params)`   | Groups rows (GROUP BY)                                            |
| `having($condition, ...$params)`| Adds a HAVING condition for filtering grouped rows                |

Methods can be chained (so-called fluent interface): `$table->where(...)->order(...)->limit(...)`.

These methods also allow the use of special notations for accessing [data from related tables|#Querying Through Related Tables].


Escaping and Identifiers
------------------------

The methods automatically escape parameters and quote identifiers (table and column names), preventing SQL injection. To ensure proper operation, a few rules must be followed:

- Write keywords, function names, procedures, etc., in **uppercase**.
- Write column and table names in **lowercase**.
- Always pass strings using **parameters**.

```php
where('name = ' . $name);         // DANGEROUS CODE - vulnerable to SQL injection
where('name LIKE "%search%"');    // WRONG: complicates automatic quoting
where('name LIKE ?', '%search%'); // CORRECT: value passed as a parameter

where('name like ?', $name);     // WRONG: generates: `name` `like` ?
where('name LIKE ?', $name);     // CORRECT: generates: `name` LIKE ?
where('LOWER(name) = ?', $value);// CORRECT: LOWER(`name`) = ?
```


where(string|array $condition, ...$parameters): static .[method]
----------------------------------------------------------------

Filters results using WHERE conditions. Its strength lies in intelligently handling various value types and automatically selecting SQL operators.

Basic usage:

```php
$table->where('id', $value);     // WHERE `id` = 123
$table->where('id > ?', $value); // WHERE `id` > 123
$table->where('id = ? OR name = ?', $id, $name); // WHERE `id` = 1 OR `name` = 'Jon Snow'
```

Thanks to automatic detection of suitable operators, you don’t need to handle special cases — Nette handles them for you:

```php
$table->where('id', 1);          // WHERE `id` = 1
$table->where('id', null);       // WHERE `id` IS NULL
$table->where('id', [1, 2, 3]);  // WHERE `id` IN (1, 2, 3)
// Placeholder ? can be used without an operator:
$table->where('id ?', 1);        // WHERE `id` = 1
```

The method also handles negative conditions and empty arrays correctly:

```php
$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- finds nothing
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- finds everything
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- finds everything
// $table->where('NOT id ?', $ids); // WARNING: This syntax is not supported
```

You can also pass the result of another table query as a parameter, creating a subquery:

```php
// WHERE `id` IN (SELECT `id` FROM `tableName`)
$table->where('id', $explorer->table($tableName));

// WHERE `id` IN (SELECT `col` FROM `tableName`)
$table->where('id', $explorer->table($tableName)->select('col'));
```

Conditions can also be passed as an array, with the items combined using AND:

```php
// WHERE (`price_final` < `price_original`) AND (`stock_count` > `min_stock`)
$table->where([
	'price_final < price_original',
	'stock_count > min_stock',
]);
```

In the array, key-value pairs can be used, and Nette will again automatically choose the correct operators:

```php
// WHERE (`status` = 'active') AND (`id` IN (1, 2, 3))
$table->where([
	'status' => 'active',
	'id' => [1, 2, 3],
]);
```

We can also mix SQL expressions with placeholders and multiple parameters. This is useful for complex conditions with precisely defined operators:

```php
// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // two parameters are passed as an array
]);
```

Multiple calls to `where()` automatically combine conditions using AND.


whereOr(array $parameters): static .[method]
--------------------------------------------

Similar to `where()`, but combines conditions using OR:

```php
// WHERE (`status` = 'active') OR (`deleted` = 1)
$table->whereOr([
	'status' => 'active',
	'deleted' => true,
]);
```

More complex expressions can also be used:

```php
// WHERE (`price` > 1000) OR (`price_with_tax` > 1500)
$table->whereOr([
	'price > ?' => 1000,
	'price_with_tax > ?' => 1500,
]);
```


wherePrimary(mixed $key): static .[method]
------------------------------------------

Adds a condition for the table's primary key:

```php
// WHERE `id` = 123
$table->wherePrimary(123);

// WHERE `id` IN (1, 2, 3)
$table->wherePrimary([1, 2, 3]);
```

If the table has a composite primary key (e.g., `foo_id`, `bar_id`), we pass it as an array:

```php
// WHERE `foo_id` = 1 AND `bar_id` = 5
$table->wherePrimary(['foo_id' => 1, 'bar_id' => 5])->fetch();

// WHERE (`foo_id`, `bar_id`) IN ((1, 5), (2, 3))
$table->wherePrimary([
	['foo_id' => 1, 'bar_id' => 5],
	['foo_id' => 2, 'bar_id' => 3],
])->fetchAll();
```


order(string $columns, ...$parameters): static .[method]
--------------------------------------------------------

Specifies the order in which rows are returned. You can sort by one or more columns, in ascending or descending order, or by a custom expression:

```php
$table->order('created');                   // ORDER BY `created`
$table->order('created DESC');              // ORDER BY `created` DESC
$table->order('priority DESC, created');    // ORDER BY `priority` DESC, `created`
$table->order('status = ? DESC', 'active'); // ORDER BY `status` = 'active' DESC
```


select(string $columns, ...$parameters): static .[method]
---------------------------------------------------------

Specifies the columns to be returned from the database. By default, Nette Database Explorer returns only the columns that are actually used in the code. Use the `select()` method when you need to retrieve specific expressions:

```php
// SELECT *, DATE_FORMAT(`created_at`, "%d.%m.%Y") AS `formatted_date`
$table->select('*, DATE_FORMAT(created_at, ?) AS formatted_date', '%d.%m.%Y');
```

Aliases defined using `AS` are then accessible as properties of the `ActiveRow` object:

```php
foreach ($table as $row) {
	echo $row->formatted_date;   // access the alias
}
```


limit(?int $limit, ?int $offset = null): static .[method]
---------------------------------------------------------

Limits the number of rows returned (LIMIT) and optionally sets an offset:

```php
$table->limit(10);        // LIMIT 10 (returns the first 10 rows)
$table->limit(10, 20);    // LIMIT 10 OFFSET 20
```

For pagination, it is more appropriate to use the `page()` method.


page(int $page, int $itemsPerPage, &$numOfPages = null): static .[method]
-------------------------------------------------------------------------

Simplifies the pagination of results. It accepts the page number (starting from 1) and the number of items per page. Optionally, you can pass a reference to a variable where the total number of pages will be stored:

```php
$numOfPages = null;
$table->page(page: 3, itemsPerPage: 10, $numOfPages);
echo "Total pages: $numOfPages";
```


group(string $columns, ...$parameters): static .[method]
--------------------------------------------------------

Groups rows by the specified columns (GROUP BY). It is typically used in combination with aggregate functions:

```php
// Counts the number of products in each category
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');
```


having(string $having, ...$parameters): static .[method]
--------------------------------------------------------

Sets a condition for filtering grouped rows (HAVING). It can be used in combination with the `group()` method and aggregate functions:

```php
// Finds categories with more than 100 products
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);
```


Reading Data
============

For reading data from the database, several useful methods are available:

.[language-php]
| `foreach ($table as $key => $row)` | Iterates through all rows, `$key` is the primary key value, `$row` is an ActiveRow object |
| `$row = $table->get($key)`         | Returns a single row by primary key |
| `$row = $table->fetch()`           | Returns the current row and advances the pointer to the next one |
| `$array = $table->fetchPairs()`    | Creates an associative array from the results |
| `$array = $table->fetchAll()`      | Returns all rows as an array |
| `count($table)`                    | Returns the number of rows in the Selection object |

The [ActiveRow |api:Nette\Database\Table\ActiveRow] object is read-only. This means you cannot change its properties' values. This restriction ensures data consistency and prevents unexpected side effects. Data is fetched from the database, and any changes should be made explicitly and in a controlled manner.


`foreach` - Iterating Through All Rows
--------------------------------------

The easiest way to execute a query and retrieve rows is by iterating with the `foreach` loop. It automatically executes the SQL query.

```php
$books = $explorer->table('book');
foreach ($books as $key => $book) {
	// $key = primary key, $book = ActiveRow
	echo "$book->title ({$book->author->name})";
}
```


get($key): ?ActiveRow .[method]
-------------------------------

Executes an SQL query and returns a row by its primary key or `null` if it does not exist.

```php
$book = $explorer->table('book')->get(123); // returns ActiveRow with ID 123 or null
if ($book) {
	echo $book->title;
}
```


fetch(): ?ActiveRow .[method]
-----------------------------

Returns one row and advances the internal pointer to the next one. If there are no more rows, it returns `null`.

```php
$books = $explorer->table('book');
while ($book = $books->fetch()) {
	$this->processBook($book);
}
```


fetchPairs(string|int|null $key = null, string|int|null $value = null): array .[method]
---------------------------------------------------------------------------------------

Returns results as an associative array. The first argument specifies the name of the column to be used as the array key, and the second argument specifies the name of the column to be used as the value:

```php
$authors = $explorer->table('author')->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]
```

If only the first parameter is provided, the entire row will be used as the value, represented as an `ActiveRow` object:

```php
$authors = $explorer->table('author')->fetchPairs('id');
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]
```

If `null` is provided as the key, the array will be numerically indexed starting from zero:

```php
$authors = $explorer->table('author')->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]
```


fetchPairs(Closure $callback): array .[method]
----------------------------------------------

Alternatively, you can pass a callback as a parameter. The callback will be applied to each row to return either a single value or a key-value pair.

```php
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => "$row->title ({$row->author->name})");
// ['First Book (John Novak)', ...]

// The callback can also return an array containing a key-value pair:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['First Book' => 'John Novak', ...]
```


fetchAll(): array .[method]
---------------------------

Returns all rows as an associative array of `ActiveRow` objects, where the keys are the primary key values.

```php
$allBooks = $explorer->table('book')->fetchAll();
// [1 => ActiveRow(id: 1, ...), 2 => ActiveRow(id: 2, ...), ...]
```


count(): int .[method]
----------------------

The `count()` method without parameters returns the number of rows in the `Selection` object:

```php
$table->where('category', 1);
$count = $table->count();
$count = count($table); // alternative
```

Note: `count()` with a parameter performs the COUNT aggregation function in the database, as described below.


ActiveRow::toArray(): array .[method]
-------------------------------------

Converts the `ActiveRow` object into an associative array where keys are column names and values are the corresponding data.

```php
$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray will be ['id' => 1, 'title' => '...', 'author_id' => ..., ...]
```


Aggregation
===========

The `Selection` class provides methods for easily performing aggregation functions (COUNT, SUM, MIN, MAX, AVG, etc.).

.[language-php]
| `count($expr)`           | Counts the number of rows |
| `min($expr)`             | Returns the minimum value in a column |
| `max($expr)`             | Returns the maximum value in a column |
| `sum($expr)`             | Returns the sum of values in a column |
| `aggregation($function)` | Allows any aggregation function, such as `AVG()` or `GROUP_CONCAT()` |


count(string $expr): int .[method]
----------------------------------

Executes an SQL query with the COUNT function and returns the result. This method is used to determine how many rows match a certain condition:

```php
$count = $table->count('*');                 // SELECT COUNT(*) FROM `table`
$count = $table->count('DISTINCT column');   // SELECT COUNT(DISTINCT `column`) FROM `table`
```

Note: [#count()] without a parameter simply returns the number of rows in the `Selection` object.


min(string $expr) and max(string $expr) .[method]
-------------------------------------------------

The `min()` and `max()` methods return the minimum and maximum values in the specified column or expression:

```php
// SELECT MAX(`price`) FROM `products` WHERE `active` = 1
$maxPrice = $products->where('active', true)
	->max('price');
```


sum(string $expr): int .[method]
--------------------------------

Returns the sum of values in the specified column or expression:

```php
// SELECT SUM(`price` * `items_in_stock`) FROM `products` WHERE `active` = 1
$totalPrice = $products->where('active', true)
	->sum('price * items_in_stock');
```


aggregation(string $function, ?string $groupFunction = null): mixed .[method]
-----------------------------------------------------------------------------

Allows the execution of any aggregation function.

```php
// Calculates the average price of products in a category
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// Combines product tags into a single string
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;
```

If we need to aggregate results that themselves result from an aggregation and grouping (e.g., `SUM(value)` over grouped rows), we specify the aggregation function to be applied to these intermediate results as the second argument:

```php
// Calculates the total price of products in stock for each category, then sums these prices
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');
```

In this example, we first calculate the total price of products in each category (`SUM(price * stock) AS category_total`) and group the results by `category_id`. We then use `aggregation('SUM(category_total)', 'SUM')` to sum these subtotals. The second argument `'SUM'` specifies the aggregation function to apply to intermediate results.


Insert, Update & Delete
=======================

Nette Database Explorer simplifies inserting, updating, and deleting data. All the methods mentioned throw a `Nette\Database\DriverException` in case of an error.


Selection::insert(iterable $data): static .[method]
---------------------------------------------------

Inserts new records into a table.

**Inserting a single record:**

The new record is passed as an associative array or iterable object (such as `ArrayHash` used in [forms |forms:]), where the keys match the column names in the table.

If the table has a defined primary key, the method returns an `ActiveRow` object, which is reloaded from the database to reflect any changes made at the database level (e.g., triggers, default column values, or auto-increment calculations). This ensures data consistency, and the object always contains the current database data. If a primary key is not explicitly defined, the method returns the input data as an array.

```php
$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row is an instance of ActiveRow containing the full data of the inserted row,
// including auto-generated ID and any changes made by triggers
echo $row->id;          // Outputs the ID of the newly inserted user
echo $row->created_at;  // Outputs the creation time if set by a trigger
```

**Inserting multiple records at once:**

The `insert()` method allows you to insert multiple records with a single SQL query. In this case, it returns the number of inserted rows.

```php
$insertedRows = $explorer->table('users')->insert([
	[
		'name' => 'John',
		'year' => 1994,
	],
	[
		'name' => 'Jack',
		'year' => 1995,
	]
]);
// INSERT INTO `users` (`name`, `year`) VALUES ('John', 1994), ('Jack', 1995)
// $insertedRows will be 2
```

You can also pass a `Selection` object with a selection of data as a parameter.

```php
$newUsers = $explorer->table('potential_users')
	->where('approved', 1)
	->select('name, email');

$insertedRows = $explorer->table('users')->insert($newUsers);
```

**Inserting special values:**

Values can include files, `DateTime` objects, or SQL literals:

```php
$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // converts to the database format
	'avatar' => fopen('image.jpg', 'rb'),   // inserts binary file content
	'uuid' => $explorer::literal('UUID()'), // calls the UUID() function
]);
```


Selection::update(iterable $data): int .[method]
------------------------------------------------

Updates rows in a table based on a specified filter. Returns the number of rows actually modified.

The columns to be updated are passed as an associative array or iterable object (such as `ArrayHash` used in [forms |forms:]), where the keys match the column names in the table:

```php
$affected = $explorer->table('users')
	->where('id', 10)
	->update([
		'name' => 'John Smith',
		'year' => 1994,
	]);
// UPDATE `users` SET `name` = 'John Smith', `year` = 1994 WHERE `id` = 10
```

To change numeric values, you can use the `+=` and `-=` operators:

```php
$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // increases the value of the 'points' column by 1
		'coins-=' => 1,   // decreases the value of the 'coins' column by 1
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10
```


Selection::delete(): int .[method]
----------------------------------

Deletes rows from a table based on a specified filter. Returns the number of deleted rows.

```php
$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE `id` = 10
```

.[caution]
When calling `update()` or `delete()`, be sure to use `where()` to specify the rows to be updated or deleted. If `where()` is not used, the operation will be performed on the entire table!


ActiveRow::update(iterable $data): bool .[method]
-------------------------------------------------

Updates the data in a database row represented by the `ActiveRow` object. It accepts iterable data as a parameter, where the keys are column names. To change numeric values, you can use the `+=` and `-=` operators:

After the update is performed, the `ActiveRow` is automatically reloaded from the database to reflect any changes made at the database level (e.g., triggers). The method returns `true` only if a real data change occurred.

```php
$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // increments the view count
]);
echo $article->views; // Outputs the current view count
```

This method updates only one specific row in the database. For bulk updates of multiple rows, use the [#Selection::update()] method.


ActiveRow::delete() .[method]
-----------------------------

Deletes a row from the database that is represented by the `ActiveRow` object.

```php
$book = $explorer->table('book')->get(1);
$book->delete(); // Deletes the book with ID 1
```

This method deletes only one specific row in the database. For bulk deletion of multiple rows, use the [#Selection::delete()] method.


Relationships Between Tables
============================

In relational databases, data is split into multiple tables and connected through foreign keys. Nette Database Explorer offers a revolutionary way to work with these relationships — without writing JOIN queries or requiring any configuration or entity generation.

For demonstration, we'll use the **example database** ([available on GitHub |https://github.com/nette-examples/books]). The database includes the following tables:

- `author` – authors and translators (columns `id`, `name`, `web`, `born`)
- `book` – books (columns `id`, `author_id`, `translator_id`, `title`, `sequel_id`)
- `tag` – tags (columns `id`, `name`)
- `book_tag` – link table between books and tags (columns `book_id`, `tag_id`)

[* db-schema-1-.webp *] *** Database structure .<>

In this book database example, we find several types of relationships (simplified compared to reality):

- **One-to-many (1:N)** – Each book **has one** author; an author can write **multiple** books.
- **Zero-to-many (0:N)** – A book **can have** a translator; a translator can translate **multiple** books.
- **Zero-to-one (0:1)** – A book **can have** a sequel.
- **Many-to-many (M:N)** – A book **can have several** tags, and a tag can be assigned to **several** books.

In these relationships, there is always a **parent table** and a **child table**. For example, in the relationship between authors and books, the `author` table is the parent, and the `book` table is the child — you can think of it as a book always "belonging" to an author. This is also reflected in the database structure: the child table `book` contains the foreign key `author_id`, which references the parent table `author`.

If we want to display books along with their authors' names, we have two options. Either we retrieve the data using a single SQL query with a JOIN:

```sql
SELECT book.*, author.name FROM book LEFT JOIN author ON book.author_id = author.id;
```

Or we retrieve the data in two steps — first the books, then their authors — and assemble them in PHP:

```sql
SELECT * FROM book;
SELECT * FROM author WHERE id IN (1, 2, 3); -- IDs of authors retrieved from books
```

The second approach is, surprisingly, **more efficient**. The data is fetched only once and can be better utilized in cache. This is exactly how Nette Database Explorer works — it handles everything under the hood and provides you with a clean API:

```php
$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author is a record from the 'author' table
	echo 'translated by: ' . $book->translator?->name;
}
```


Accessing the Parent Table
--------------------------

Accessing the parent table is straightforward. These are relationships like *a book has an author* or *a book may have a translator*. The related record can be accessed through the `ActiveRow` object property — the property name matches the column name of the foreign key without the `id` suffix:

```php
$book = $explorer->table('book')->get(1);
echo $book->author->name;      // finds the author via the 'author_id' column
echo $book->translator?->name; // finds the translator via the 'translator_id' column
```

When accessing the `$book->author` property, Explorer looks for a column in the `book` table that contains the string `author` (i.e., `author_id`). Based on the value in this column, it retrieves the corresponding record from the `author` table and returns it as an `ActiveRow` object. Similarly, `$book->translator` uses the `translator_id` column. Since the `translator_id` column can contain `null`, the `?->` operator is used.

An alternative approach is provided by the `ref()` method, which accepts two arguments — the name of the target table and the linking column — and returns an `ActiveRow` instance or `null`:

```php
echo $book->ref('author', 'author_id')->name;      // link to the author
echo $book->ref('author', 'translator_id')->name;  // link to the translator
```

The `ref()` method is useful if property-based access cannot be used, for example, when the table contains a column with the same name as the property (`author`). In other cases, using property-based access is recommended for better readability.

Explorer automatically optimizes database queries. When iterating through books and accessing their related records (authors, translators), Explorer does not generate a query for each book individually. Instead, it executes only **one SELECT query for each type of relationship**, significantly reducing the database load. For example:

```php
$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	echo $book->translator?->name;
}
```

This code will execute only three optimized database queries:

```sql
SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- IDs from 'author_id' column in selected books
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- IDs from 'translator_id' column in selected books
```

.[note]
The logic for identifying the linking column is defined by the implementation of [Conventions |api:Nette\Database\Conventions]. We recommend using [DiscoveredConventions |api:Nette\Database\Conventions\DiscoveredConventions], which analyzes foreign keys and allows you to work seamlessly with existing table relationships.


Accessing the Child Table
-------------------------

Accessing the child table works in the opposite direction. Now we ask *which books did this author write* or *which books did this translator translate*. For this type of query, we use the `related()` method, which returns a `Selection` object with related records. Here's an example:

```php
$author = $explorer->table('author')->get(1);

// Outputs all books written by the author
foreach ($author->related('book.author_id') as $book) {
	echo "Wrote: $book->title";
}

// Outputs all books translated by the author
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}
```

The `related()` method accepts the relationship description as a single argument using dot notation or as two separate arguments:

```php
$author->related('book.translator_id');    // single argument
$author->related('book', 'translator_id'); // two arguments
```

Explorer can automatically detect the correct linking column based on the name of the parent table. In this case, it links via the `book.author_id` column because the name of the source table is `author`:

```php
$author->related('book'); // uses book.author_id
```

If multiple possible connections exist, Explorer will throw an exception [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException].

We can, of course, also use the `related()` method when iterating through multiple records in a loop, and Explorer will automatically optimize the queries in this case as well:

```php
$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' wrote:';
	foreach ($author->related('book') as $book) {
		echo $book->title;
	}
}
```

This code generates only two efficient SQL queries:

```sql
SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- IDs of the selected authors
```


Many-to-Many Relationship
-------------------------

For a many-to-many (M:N) relationship, a **junction table** (in our case, `book_tag`) is required. This table contains two foreign key columns (`book_id`, `tag_id`). Each column references the primary key of one of the connected tables. To retrieve related data, we first fetch records from the link table using `related('book_tag')`, and then continue to the target data:

```php
$book = $explorer->table('book')->get(1);
// Outputs the names of tags assigned to the book
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // fetches tag name through the link table
}

$tag = $explorer->table('tag')->get(1);
// Opposite direction: outputs the titles of books with this tag
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // fetches book title
}
```

Explorer again optimizes the SQL queries into an efficient form:

```sql
SELECT * FROM `book`;
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 2, ...));  -- IDs of the selected books
SELECT * FROM `tag` WHERE (`tag`.`id` IN (1, 2, ...));                 -- IDs of the tags found in book_tag
```


Querying Through Related Tables
-------------------------------

In the methods `where()`, `select()`, `order()`, and `group()`, you can use special notations to access columns from other tables. Explorer automatically creates the required JOINs.

**Dot notation** (`parent_table.column`) is used for 1:N relationships  as viewed from the parent table's perspective:

```php
$books = $explorer->table('book');

// Finds books whose authors' names start with 'Jon'
$books->where('author.name LIKE ?', 'Jon%');

// Sorts books by author name descending
$books->order('author.name DESC');

// Outputs book title and author name
$books->select('book.title, author.name');
```

**Colon notation** is used for 1:N relationships from the perspective of the parent table:

```php
$authors = $explorer->table('author');

// Finds authors who wrote a book with 'PHP' in the title
$authors->where(':book.title LIKE ?', '%PHP%');

// Counts the number of books for each author
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');
```

In the above example with colon notation (`:book.title`), the foreign key column is not explicitly specified. Explorer automatically detects the correct column based on the parent table name. In this case, it joins through the `book.author_id` column because the name of the source table is `author`. If multiple possible connections exist, Explorer throws the exception [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException].

The linking column can be explicitly specified in parentheses:

```php
// Finds authors who translated a book with 'PHP' in the title
$authors->where(':book(translator).title LIKE ?', '%PHP%');
```

Notations can be chained to access data across multiple tables:

```php
// Finds authors of books tagged with 'PHP'
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');
```


Extending Conditions for JOIN
-----------------------------

The `joinWhere()` method adds additional conditions to table joins in SQL after the `ON` keyword.

For example, let's say we want to find books translated by a specific translator:

```php
// Finds books translated by a translator named 'David'
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN author translator ON book.translator_id = translator.id AND (translator.name = 'David')
```

In the `joinWhere()` condition, you can use the same constructs as in the `where()` method — operators, placeholders, arrays of values, or SQL expressions.

For more complex queries with multiple JOINs, table aliases can be defined:

```php
$tags = $explorer->table('tag')
	->joinWhere(':book_tag.book.author', 'book_author.born < ?', 1950)
	->alias(':book_tag.book.author', 'book_author');
// LEFT JOIN `book_tag` ON `tag`.`id` = `book_tag`.`tag_id`
// LEFT JOIN `book` ON `book_tag`.`book_id` = `book`.`id`
// LEFT JOIN `author` `book_author` ON `book`.`author_id` = `book_author`.`id`
//    AND (`book_author`.`born` < 1950)
```

Note that while the `where()` method adds conditions to the `WHERE` clause, the `joinWhere()` method extends the conditions in the `ON` clause during table joins.


Manually Creating Explorer
==========================

If you are not using the Nette DI container, you can create an instance of `Nette\Database\Explorer` manually:

```php
use Nette\Database;

// $storage implements Nette\Caching\Storage, e.g.:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// database connection
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// manages reflection of the database structure
$structure = new Database\Structure($connection, $storage);
// defines rules for mapping table names, columns, and foreign keys
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);
```