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

<div class=perex>

Nette Database Explorer significantly simplifies retrieving data from the database without writing SQL queries.

- uses efficient queries
- no data is transmitted unnecessarily
- features elegant syntax

</div>

To use Database Explorer, start with a table - call `table()` on a [api:Nette\Database\Explorer] object. The easiest way to get a context object instance is [described here |core#Connection and Configuration], or, for case when Nette Database Explorer is used as a standalone tool, it can be [created manually|#Creating Explorer Manually].

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

The call returns an instance of [Selection |api:Nette\Database\Table\Selection] object, that can be iterated over to retrieve all the books. Each item (a row) is represented by an instance of [ActiveRow |api:Nette\Database\Table\ActiveRow] with data mapped to its properties:

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

Getting just one specific row is done by `get()` method, which directly returns an ActiveRow instance.

```php
$book = $explorer->table('book')->get(2); // returns book with id 2
echo $book->title;
echo $book->author_id;
```

Let's take a look at common use-case. You need to fetch books and their authors. It is a common 1:N relationship. The often used solution is to fetch data using 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 the books, and another for the needed authors - and that is exactly the way how Nette Database Explorer does it.

In the examples below, we will work with the database schema in the figure. There are OneHasMany (1:N) links (author of book `author_id` and possible translator `translator_id`, which may be `null`) and ManyHasMany (M:N) link between book and its tags.

[An example, including a schema, is found on GitHub |https://github.com/nette-examples/books].

[* db-schema-1-.webp *] *** Database structure used in the examples .<>

The following code lists the author's name for each book and all its tags. We will [discuss in a moment |#Working with relationships] how this works internally.

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

foreach ($books as $book) {
	echo 'title:      ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author is row from table 'author'

	echo 'tags: ';
	foreach ($book->related('book_tag') as $bookTag) {
		echo $bookTag->tag->name . ', '; // $bookTag->tag is row from table 'tag'
	}
}
```

You will be pleased how efficiently the database layer works. The example above makes a constant number of requests that look like this:

```sql
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 |caching:] (defaults on), no columns will be queried unnecessarily. After the first query, cache will store the used column names and Nette Database Explorer will run queries only with the needed columns:

```sql
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))
```


Selections
==========

See possibilities how to filter and restrict rows [api:Nette\Database\Table\Selection]:

.[language-php]
| `$table->where($where[, $param[, ...]])` | Set WHERE using AND as a glue if two or more conditions are supplied
| `$table->whereOr($where)` | Set WHERE using OR as a glue if two or more conditions are supplied
| `$table->order($columns)` | Set ORDER BY, can be expression `('column DESC, id DESC')`
| `$table->select($columns)` | Set retrieved columns, can be expression `('col, MD5(col) AS hash')`
| `$table->limit($limit[, $offset])` | Set LIMIT and OFFSET
| `$table->page($page, $itemsPerPage[, &$lastPage])` | Enables pagination
| `$table->group($columns)` | Set GROUP BY
| `$table->having($having)` | Set HAVING

We can use a so-called [fluent interface |nette:introduction-to-object-oriented-programming#fluent-interfaces], for example `$table->where(...)->order(...)->limit(...)`. Multiple `where` or `whereOr` conditions are linked by the `AND` operator.


where()
-------

Nette Database Explorer can automatically add needed operators for passed values:

.[language-php]
| `$table->where('field', $value)` | field = $value
| `$table->where('field', null)` | field IS NULL
| `$table->where('field > ?', $val)` | field > $val
| `$table->where('field', [1, 2])` | field IN (1, 2)
| `$table->where('id = ? OR name = ?', 1, $name)` | id = 1 OR name = 'Jon Snow'
| `$table->where('field', $explorer->table($tableName))` | field IN (SELECT $primary FROM $tableName)
| `$table->where('field', $explorer->table($tableName)->select('col'))` | field IN (SELECT col FROM $tableName)

You can provide placeholder even without column operator. These calls are the same.

```php
$table->where('id = ? OR id = ?', 1, 2);
$table->where('id ? OR id ?', 1, 2);
```

This feature allows to generate correct operator based on value:

```php
$table->where('id ?', 2);    // id = 2
$table->where('id ?', null); // id IS NULL
$table->where('id', $ids);   // id IN (...)
```

Selection correctly handles also negative conditions, works for empty arrays too:

```php
$table->where('id', []);       // id IS NULL AND FALSE
$table->where('id NOT', []);   // id IS NULL OR TRUE
$table->where('NOT (id ?)', $ids);  // NOT (id IS NULL AND FALSE)

// this will throws an exception, this syntax is not supported
$table->where('NOT id ?', $ids);
```


whereOr()
---------

Example of use without parameters:

```php
// WHERE (user_id IS NULL) OR (SUM(`field1`) > SUM(`field2`))
$table->whereOr([
	'user_id IS NULL',
	'SUM(field1) > SUM(field2)',
]);
```

We use the parameters. If you do not specify an operator, Nette Database Explorer will automatically add the appropriate one:

```php
// WHERE (`field1` IS NULL) OR (`field2` IN (3, 5)) OR (`amount` > 11)
$table->whereOr([
	'field1' => null,
	'field2' => [3, 5],
	'amount >' => 11,
]);
```

The key can contain an expression containing wildcard question marks and then pass parameters in the value:

```php
// WHERE (`id` > 12) OR (ROUND(`id`, 5) = 3)
$table->whereOr([
	'id > ?' => 12,
	'ROUND(id, ?) = ?' => [5, 3],
]);
```


order()
-------

Examples of use:

```php
$table->order('field1');               // ORDER BY `field1`
$table->order('field1 DESC, field2');  // ORDER BY `field1` DESC, `field2`
$table->order('field = ? DESC', 123);  // ORDER BY `field` = 123 DESC
```


select()
--------

Examples of use:

```php
$table->select('field1');                 // SELECT `field1`
$table->select('col, UPPER(col) AS abc'); // SELECT `col`, UPPER(`col`) AS abc
$table->select('SUBSTR(title, ?)', 3);    // SELECT SUBSTR(`title`, 3)
```


limit()
-------

Examples of use:

```php
$table->limit(1);     // LIMIT 1
$table->limit(1, 10); // LIMIT 1 OFFSET 10
```


page()
------

An alternative way to set the limit and offset:

```php
$page = 5;
$itemsPerPage = 10;
$table->page($page, $itemsPerPage); // LIMIT 10 OFFSET 40
```

Getting the last page number, passed to the `$lastPage` variable:

```php
$table->page($page, $itemsPerPage, $lastPage);
```


group()
-------

Examples of use:

```php
$table->group('field1');          // GROUP BY `field1`
$table->group('field1, field2');  // GROUP BY `field1`, `field2`
```


having()
--------

Examples of use:

```php
$table->having('COUNT(items) >', 100);  // HAVING COUNT(`items`) > 100
```


Filtering by Another Table Value .[#toc-joining-key]
----------------------------------------------------

Quite often you need filter results by some condition which involves another database table. These types of condition require table join. However, you don't need to write them anymore.

Let's say you need to get all books whose author's name is 'Jon'. All you need to write is the joining key of the relation and the column name in the joined table. The joining key is derived from the column which refers to the table you want to join. In our example (see the db schema) it is the column `author_id`, and it is sufficient to use just the first part of it - `author` (the `_id` suffix can be omitted). `name` is a column in the `author` table we would like to use. A condition for book translator (which is connected by `translator_id` column) can be created just as easily.

```php
$books = $explorer->table('book');
$books->where('author.name LIKE ?', '%Jon%');
$books->where('translator.name', 'David Grudl');
```

The joining key logic is driven by implementation of [Conventions |api:Nette\Database\Conventions]. We encourage to use [DiscoveredConventions |api:Nette\Database\Conventions\DiscoveredConventions], which analyzes your foreign keys and allows you to easily work with these relationships.

The relationship between book and its author is 1:N. The reverse relationship is also possible. We call it **backjoin**. Take a look at another example. We would like to fetch all authors, who have written more than 3 books. To make the join reverse we use `:` (colon). Colon means that the joined relationship means hasMany (and it's quite logical too, as two dots are more than one dot). Unfortunately, the Selection class isn't smart enough, so we have to help with the aggregation and provide a `GROUP BY` statement, also the condition has to be written in form of `HAVING` statement.

```php
$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book.id) > 3');
```

You may have noticed that the joining expression refers to the book, but it's not clear, whether we are joining through `author_id` or `translator_id`. In the example above, Selection joins through the `author_id` column because a match with the source table has been found - the `author` table. If there was no such a match and there would be more possibilities, Nette would throw [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException].

To make a join through `translator_id` column, provide an optional parameter within the joining expression.

```php
$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book(translator).id) > 3');
```

Let's take a look at some more difficult joining expression.

We would like to find all authors who have written something about PHP. All books have tags so we should select those authors who have written any book with the PHP tag.

```php
$authors = $explorer->table('author');
$authors->where(':book:book_tags.tag.name', 'PHP')
	->group('author.id')
	->having('COUNT(:book:book_tags.tag.id) > 0');
```


Aggregate Queries
-----------------

| `$table->count('*')` | Get number of rows
| `$table->count("DISTINCT $column")` | Get number of distinct values
| `$table->min($column)` | Get minimum value
| `$table->max($column)` | Get maximum value
| `$table->sum($column)` | Get the sum of all values
| `$table->aggregation("GROUP_CONCAT($column)")` | Run any aggregation function

.[caution]
The `count()` method without any specified parameters selects all records and returns the array size, which is very inefficient. For example, if you need to calculate the number of rows for paging, always specify the first argument.


Escaping & Quoting
==================

Database Explorer is smart and escape parameters and quotes identificators for you. These basic rules need to be followed, though:

- keywords, functions, procedures must be uppercase
- columns and tables must be lowercase
- pass variables as parameters, do not concatenate

```php
->where('name like ?', 'John'); // WRONG! generates: `name` `like` ?
->where('name LIKE ?', 'John'); // CORRECT

->where('KEY = ?', $value); // WRONG! KEY is a keyword
->where('key = ?', $value); // CORRECT. generates: `key` = ?

->where('name = ' . $name); // WRONG! sql injection!
->where('name = ?', $name); // CORRECT

->select('DATE_FORMAT(created, "%d.%m.%Y")'); // WRONG! pass variables as parameters, do not concatenate
->select('DATE_FORMAT(created, ?)', '%d.%m.%Y'); // CORRECT
```

.[warning]
Wrong usage can produce security holes


Fetching Data
=============

| `foreach ($table as $id => $row)` | Iterate over all rows in result
| `$row = $table->get($id)` | Get single row with ID $id from table
| `$row = $table->fetch()` | Get next row from the result
| `$array = $table->fetchPairs($key, $value)` | Fetch all values to associative array
| `$array = $table->fetchPairs($value)` | Fetch all rows to associative array
| `count($table)` | Get number of rows in result set


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

Method `insert()` accepts array or Traversable objects (for example [ArrayHash |utils:arrays#ArrayHash] which returns [forms|forms:]):

```php
$row = $explorer->table('users')->insert([
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)
```

If primary key is defined on the table, an ActiveRow object containing the inserted row is returned.

Multiple insert:

```php
$explorer->table('users')->insert([
	[
		'name' => 'Jim',
		'year' => 1978,
	], [
		'name' => 'Jack',
		'year' => 1987,
	],
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)
```

Files or DateTime objects can be passed as parameters:

```php
$explorer->table('users')->insert([
	'name' => $name,
	'created' => new DateTime, // or $explorer::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserts the file
]);
```

Updating (returns the count of affected rows):

```php
$count = $explorer->table('users')
	->where('id', 10) // must be called before update()
	->update([
		'name' => 'Ned Stark'
	]);
// UPDATE `users` SET `name`='Ned Stark' WHERE (`id` = 10)
```

For update we can use operators `+=` a `-=`:

```php
$explorer->table('users')
	->update([
		'age+=' => 1, // see +=
	]);
// UPDATE users SET `age` = `age` + 1
```

Deleting (returns the count of deleted rows):

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


Working with Relationships
==========================


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. It accepts two arguments: target table name and source joining column. See example:

```php
$book = $explorer->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.

```php
$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.
```php
$book->ref('author', 'translator_id')->name
```

All of this is fine, but it's somewhat cumbersome, don't you think? Database Explorer 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:

```php
$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 |#joining-key] chapter.

```php
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 Explorer will fetch authors and translators for all the fetched books at once.

```php
$books = $explorer->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:
```sql
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:

```php
$author = $explorer->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;
}
```

Method `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.

```php
$author->related('book.translator_id');
// same as
$author->related('book', 'translator_id');
```

You can use Nette Database Explorer 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 Explorer will use this foreign key, otherwise it will throw [Nette\InvalidArgumentException|api:Nette\InvalidArgumentException] or [AmbiguousReferenceKeyException |api:Nette\Database\Conventions\AmbiguousReferenceKeyException]. You can find more about the key matching logic in [Joining expressions |#joining-key] chapter.

Of course you can call related methods for all fetched authors, Nette Database Explorer will again fetch the appropriate books at once.

```php
$authors = $explorer->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:

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


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

A database connection can be created using the application configuration. In such cases a `Nette\Database\Explorer` service is created and can be passed as a dependency using the DI container.

However, if Nette Database Explorer is used as a standalone tool, an instance of `Nette\Database\Explorer` object needs to be created manually.

```php
// $storage implements Nette\Caching\Storage:
$storage = new Nette\Caching\Storages\FileStorage($tempDir);
$connection = new Nette\Database\Connection($dsn, $user, $password);
$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$explorer = new Nette\Database\Explorer($connection, $structure, $conventions, $storage);
```