Nette\Database:
chybějící dokumentace

Jan Škrášek
28. dubna, 2012

Jan Škrášek

Nette\Database není

Nette\Database je

Připojení k DB

$connection = new Nette\Database\Connection(
	"mysql:dbname=test;host=localhost",
	"root",
	"" // empty password
);

DSN pro sqlite?

"sqlite:/opt/databases/mydb.sq3"

SQL dotaz 

$data = $connection->query(
            'SELECT * FROM test WHERE x = ?', 'string'
        );
$data = $connection->query(
            'SELECT * FROM test WHERE x = ? AND y IN (?)',
            1,
            array(1, "bcd' OR 1")
        );
$connection->query('INSERT INTO test', array(...));

Zpracování dat 

$data->fetchAll();
$data->fetch();
$data->fetchPairs();

foreach ($data as $row) {
    $row->column;
    $row['column'];
}

Nette\Database\Table

ne-orm

Kritické body

Demo schéma

Výběr

$authors = $connection->table('author');
// $authors instanceof Nette\Database\Table\Selection

foreach ($authors as $author) {
	echo $author->name;
}

$authors->fetch();
$authors->fetchPairs();
// NO fetchAll()!!!

Výběr 1 záznamu

  	
$author = $connection->table('author')
                     ->where('id', $id)
                     ->fetch();


$author = $connection->table('author')
                     ->get($id);

Podmíněný výběr

// WHERE name = 'Petr'
...->where('name', $_GET['name']);

// WHERE web IS NULL
...->where('web', NULL);

// WHERE born > NOW()
...->where('born > NOW()')
...->where('born > ?',
            new Nette\Database\SqlLiteral('NOW()'));

Více podmínek

// WHERE web IS NULL AND born > NOW()
...->where('web', NULL)->where('born > NOW()');


// WHERE web = '...' OR born > '2012-04-28 17:00:00'
...->where(
    'web = ? OR born > ?', '...', new \DateTime(...)
);

Podmínky z jiných tabulek (hasOne)

// knihy s autory narozenymi az v budoucnosti
$connection->table('book')->where('author.born > NOW()');

SELECT `book`.* 
FROM `book` 
INNER JOIN `author` ON `book`.`author_id` = `author`.`id` 
WHERE (`author`.`born` > NOW())

// knihy serazene podle prijmeni autoru
$connection->table('book')->order('author.name ASC');

Backjoin (hasMany)

// autori s vice jak jednou knihou
$connection->table('author')
           ->group('author.id', 'COUNT(book:id) > 1');
  	
SELECT `author`.* 
FROM `author` 
LEFT JOIN `book` ON `author`.`id` = `book`.`author_id` 
GROUP BY `author.id` 
HAVING COUNT(`book`.`id`) > 1

Backjoin složitěji

K seznamu autorů zobrazit počet tagů ze všech jejich knih a autory podle těchto počtů seřadit.

$a = $connection->table('author')
   ->select('author.*,
             COUNT(DISTINCT book:book_tag:tag_id)
                 AS tagsCount')
   ->group('author.id')
   ->order('tagsCount');

foreach ($a as $author) {
    echo $author->name, " - tagu: ", $author->tagsCount;
}

Backjoin složitěji

SELECT
    `author`.*,
    COUNT(DISTINCT `book_tag`.`tag_id`) AS `tagsCount` 
FROM `author` 
LEFT JOIN `book` ON `author`.`id` = `book`.`author_id` 
LEFT JOIN `book_tag` ON `book`.`id` = `book_tag`.`book_id` 
GROUP BY `author`.`id` 
ORDER BY `tagsCount` DESC

Data relací 1:1, 1:N, M:N

Relace 1:1

foreach ($connection->table('book') as $book) {
	$book->title;

	$book->ref('author')->name;
	$book->ref('author', 'author_id')->name;
	$book->ref('author', 'translator_id')->name;

	$book->author->name;
	$book->translator->name;
}

3 dotazy

Relace 1:N

foreach ($connection->table('author') as $author) {
    $author->name;

    foreach ($author->related('book') as $book) {
        $book->title;
    }

    foreach ($author->related('book.translator_id')
                  as $book ) {
        $book->title;
    }
}

3 dotazy

Relace M:N


M:N = 1:N:1 = 1:N-1:1
book HAS_MANY book_tags
book_tag HAS_ONE tag 

foreach ($connection->table('book') as $book) {
    $book->title;

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

Agregační funkce

$connection->table('author')
           ->where('born IS NOT NULL')
           ->count('*');

// SELECT COUNT(*) ...
 
$authors = $connection->table('author')
                      ->where('born IS NOT NULL');

echo "Zaznamu ", $authors->count();
foreach ($authors as $author) {
	// ...
}

Agregační funkce #2

Počet knih jednotlivých autorů

foreach ($connection->table('author') as $author) {
    $author->name;
    $author->related('book')->count('*');
}
  	
SELECT COUNT(*), `book`.`author_id` 
FROM `book` 
WHERE (`book`.`author_id` IN (...)) 
GROUP BY `book`.`author_id`

Výhody konceptu

Co je třeba dopilovat

Co je třeba dopilovat #2

Otázky

Děkuji za pozornost!