Database Explorer

Nette Database Explorer is an extension of the low-level Nette Database Core layer, which adds a convenient object-oriented approach to database management.

Working with Explorer starts with calling the table() method on the Nette\Database\Explorer object (how to obtain it is described here):

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

The method returns a 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 object:

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

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

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.

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.

where('name = ' . $name);         // **DISASTER**: 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

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

Basic usage:

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

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

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

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

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

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

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

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

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

More complex expressions can also be used:

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

wherePrimary (mixed $key): static

Adds a condition for the table's primary key:

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

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

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:

$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

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:

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

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

limit (?int $limit, ?int $offset = null): static

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

$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

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:

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

group (string $columns, …$parameters): static

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

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

having (string $having, …$parameters): static

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

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

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

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

get ($key): ?ActiveRow

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

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

fetch(): ?ActiveRow

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

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

fetchPairs(): array

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

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

If only the key column is specified, the value will be the entire row, i.e., the ActiveRow object:

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

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

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

You can also pass a callback as a parameter, which will return either the value itself or a key-value pair for each row. If the callback returns only a value, the key will be the row's primary key:

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

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

fetchAll(): array

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

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

count(): int

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

$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

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

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

count (string $expr): int

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:

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

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

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

sum (string $expr): int

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

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

Allows the execution of any aggregation function.

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

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

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

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

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

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

$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

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), where the keys match the column names in the table:

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

$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

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

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

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

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.

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

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

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

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:

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:

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:

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

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

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:

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

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

The logic for identifying the linking column is defined by the implementation of Conventions. We recommend using 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:

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

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

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

If multiple possible connections exist, Explorer will throw an exception 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:

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

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:

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

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:

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

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

The linking column can be explicitly specified in parentheses:

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

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

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

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

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