Database & ORM

Basic element is a wrapper over PDO called Nette\Database\Connection:

use Nette\Database\Connection;

$database = new Connection($dsn, $user, $password);

A panel in Debugger bar will automatically appear after connection. It informs you about all the performed SQL queries, time of their execution, the number of rows returned and the panel even shows an EXPLAIN form of the queries and places in the code where each query was performed.

Nette\Database (in contrary to PDO) allows Connection to list parameters in commands exec and query:

$database->exec('INSERT INTO users', array( // an array can be a parameter
    'name' => 'Jim',
    'created' => new DateTime, // or a DateTime object
    'avatar' => fopen('image.gif', 'r'), // or a file
), ...); // it is even possible to use multiple inserts

$database->exec('UPDATE users SET ? WHERE id=?', $data, $id);

$database->query('SELECT * FROM categories WHERE id=?', 123)->dump();

Working with tables

For complex selection you can use a tool which builds upon an excellent library NotORM and which offers you a more readable API. Depicted database can be traversed like this:

foreach ($database->table('book')->order('title')->limit(5) as $book)
{
    echo $book->title, ' (', $book->author->name, ')';
    // equivalent to $book['title'], ' (', $book['author']['name'], ')';

    foreach ($book->related('book_tag') as $book_tag) {
        echo $book_tag->tag->name . ', ';
    }
}

In a template you can use Latte syntax:

{foreach $database->table('book')->order('title')->limit(5) as $book}
    <h2>{$book->title} ({$book->author->name})</h2>

    {foreach $book->related('book_tag') as $book_tag}
        {$book_tag->tag->name}{sep}, {/sep}
    {/foreach}
{/foreach}

Efficiency

You will be pleased how efficiently the database layer works. No columns are transmitted unnecessarily. The example above performs following queries:

SELECT * FROM `book` ORDER BY title LIMIT 5

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 (see API) Nette\Database queries only columns which are actually used:

SELECT `id`, `title`, `author_id` FROM `book` ORDER BY title LIMIT 5

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

Table Selection API

$table = $conn->table($tableName)	Get representation of table $tableName
$table->find($value)	Search by primary key
$table->where($where[, $parameters[, ...]])	Set WHERE (explained later)
$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->group($columns[, $having])	Set GROUP BY and HAVING

You can use fluent interface, for example $table->where(...)->order(...)->limit(...). More where conditions can be connected with the AND operator.

$table->where("field", $value)	Means field = $value
$table->where("field", NULL)	Means field IS NULL
$table->where("field > ?", $val)	Means field > $val
$table->where("field", array(1, 2))	Means field IN (1, 2)
$table->where("field", $conn->table($tableName))	Means field IN (SELECT $primary FROM $tableName)
$table->where("field", $conn->table($tableName)->select('col'))	Means field IN (SELECT col FROM $tableName)
$table->where(array("field" => "x", "field2" => "y"))	Same as $table->where("field", "x")->where("field2", "y")

If you use the dot notation ("$table.$column"), a relation with linked table will be automatically created.

Aggregate queries can also be created:

$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->aggregation("GROUP_CONCAT($column)")	Run any aggregation function

Fetching data:

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

Working with single row:

$data = $row[$column] nebo $row->$column	Returns the value of attribute $column or one row from parent table. E.g. $book['title'] == $book->title or $book['author'] == $book->author == $book->ref('author')
$parent = $child->ref($tableName)	Returns one row from foreign table (parent). E.g. $book->ref('author')->name
$tableChildren = $parent->related($tableName)	Returns children table filtered using the value of parent's primary key. Attribute in children table with name derived from parent table is used. E.g. $author->related('book')->count()
$tableChildren = $parent->related($tableName) ->through($column)	Uses concrete attribute in children table to filter. E.g. $author->related('book') ->through('maintainer_id')->count()
(string) $row	Returns primary key value

(Taken from NotORM API)

---

Login to submit a comment