Explorador de banco de dados

O Nette Database Explorer é uma extensão da camada de baixo nível do Nette Database Core, que acrescenta uma conveniente abordagem orientada a objetos ao gerenciamento de bancos de dados.

O trabalho com o Explorer começa com a chamada do método table() no objeto Nette\Database\Explorer (a forma de obtê-lo está descrita aqui):

$books = $explorer->table('book'); // "book" é o nome da tabela

O método retorna um objeto Selection, que representa uma consulta SQL. Métodos adicionais podem ser encadeados a esse objeto para filtragem e classificação de resultados. A consulta é montada e executada somente quando os dados são solicitados, por exemplo, por meio da iteração com foreach. Cada linha é representada por um objeto ActiveRow:

foreach ($books as $book) {
	echo $book->title;        // produz a coluna "title" (título)
	echo $book->author_id;    // produz a coluna 'author_id'
}

O Explorer simplifica muito o trabalho com relacionamentos de tabela. O exemplo a seguir mostra a facilidade com que podemos gerar dados de tabelas relacionadas (livros e seus autores). Observe que não é necessário escrever consultas JOIN; o Nette as gera para nós:

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

foreach ($books as $book) {
	echo 'Book: ' . $book->title;
	echo 'Author: ' . $book->author->name; // cria um JOIN para a tabela "author" (autor)
}

O Nette Database Explorer otimiza as consultas para obter o máximo de eficiência. O exemplo acima executa apenas duas consultas SELECT, independentemente do fato de processarmos 10 ou 10.000 livros.

Além disso, o Explorer rastreia quais colunas são usadas no código e busca apenas essas colunas no banco de dados, economizando ainda mais desempenho. Esse comportamento é totalmente automático e adaptável. Se você modificar o código posteriormente para usar colunas adicionais, o Explorer ajustará automaticamente as consultas. Você não precisa configurar nada nem pensar em quais colunas serão necessárias – deixe isso para a Nette.

Filtragem e classificação

A classe Selection fornece métodos para filtragem e classificação de dados.

Os métodos podem ser encadeados (a chamada interface fluente): $table->where(...)->order(...)->limit(...).

Esses métodos também permitem o uso de notações especiais para acessar dados de tabelas relacionadas.

Escapes e identificadores

Os métodos escapam automaticamente dos parâmetros e dos identificadores de citação (nomes de tabelas e colunas), evitando a injeção de SQL. Para garantir a operação adequada, algumas regras devem ser seguidas:

• Escreva palavras-chave, nomes de funções, procedimentos, etc., em maiúsculas.
• Escrever nomes de colunas e tabelas em minúsculas.
• Sempre passe strings usando parâmetros.

where('name = ' . $name);         // **DISASTER**: vulnerável à injeção de SQL
where('name LIKE "%search%"');    // **WRONG**: complica a citação automática
where('name LIKE ?', '%search%'); // **CORRECT**: valor passado como parâmetro

where('name like ?', $name);     // **WRONG**: gera: `name` `like` ?
where('name LIKE ?', $name);     // **CORRECT**: gera: `nome` LIKE ?
where('LOWER(name) = ?', $value);// **CORRETO**: LOWER(`nome`) = ?

where (string|array $condition, …$parameters): static

Filtra os resultados usando as condições WHERE. Sua força reside no tratamento inteligente de vários tipos de valores e na seleção automática de operadores SQL.

Uso básico:

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

Graças à detecção automática de operadores adequados, você não precisa lidar com casos especiais – a Nette lida com eles para você:

$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)
// O espaço reservado ? pode ser usado sem um operador:
$table->where('id ?', 1);        // WHERE `id` = 1

O método também lida corretamente com condições negativas e matrizes vazias:

$table->where('id', []);         // WHERE `id` IS NULL AND FALSE -- não encontra nada
$table->where('id NOT', []);     // WHERE `id` IS NULL OR TRUE -- encontra tudo
$table->where('NOT (id ?)', []); // WHERE NOT (`id` IS NULL AND FALSE) -- encontra tudo
// $table->where('NOT id ?', $ids); // AVISO: Essa sintaxe não é suportada

Você também pode passar o resultado de outra consulta de tabela como parâmetro, criando uma subconsulta:

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

As condições também podem ser passadas como uma matriz, com os itens combinados usando AND:

// WHERE (`preço_final` < `preço_original`) AND (`contagem_de_estoque` > `min_estoque`)
$table->where([
	'price_final &lt; price_original',
	'stock_count > min_stock',
]);

Na matriz, podem ser usados pares de valores-chave, e o Nette novamente escolherá automaticamente os operadores corretos:

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

Também podemos misturar expressões SQL com placeholders e vários parâmetros. Isso é útil para condições complexas com operadores definidos com precisão:

// WHERE (`age` > 18) AND (ROUND(`score`, 2) > 75.5)
$table->where([
	'age > ?' => 18,
	'ROUND(score, ?) > ?' => [2, 75.5], // dois parâmetros são passados como uma matriz
]);

Várias chamadas para where() combinam automaticamente as condições usando AND.

whereOr (array $parameters): static

Semelhante ao where(), mas combina condições usando OR:

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

Expressões mais complexas também podem ser usadas:

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

wherePrimary (mixed $key): static

Adiciona uma condição para a chave primária da tabela:

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

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

Se a tabela tiver uma chave primária composta (por exemplo, foo_id, bar_id), nós a passaremos como uma matriz:

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

Especifica a ordem em que as linhas são retornadas. Você pode classificar por uma ou mais colunas, em ordem crescente ou decrescente, ou por uma expressão personalizada:

$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

Especifica as colunas a serem retornadas do banco de dados. Por padrão, o Nette Database Explorer retorna apenas as colunas que são realmente usadas no código. Use o método select() quando precisar recuperar expressões específicas:

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

Os aliases definidos com o uso de AS podem ser acessados como propriedades do objeto ActiveRow:

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

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

Limita o número de linhas retornadas (LIMIT) e, opcionalmente, define um deslocamento:

$table->limit(10);        // LIMIT 10 (retorna as 10 primeiras linhas)
$table->limit(10, 20);    // LIMITE 10 DESLOCAMENTO 20

Para paginação, é mais apropriado usar o método page().

page (int $page, int $itemsPerPage, &$numOfPages = null): static

Simplifica a paginação dos resultados. Ele aceita o número da página (a partir de 1) e o número de itens por página. Opcionalmente, você pode passar uma referência a uma variável na qual o número total de páginas será armazenado:

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

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

Agrupa as linhas pelas colunas especificadas (GROUP BY). Normalmente, é usado em combinação com funções de agregação:

// Conta o número de produtos em cada categoria
$table->select('category_id, COUNT(*) AS count')
	->group('category_id');

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

Define uma condição para filtragem de linhas agrupadas (HAVING). Pode ser usado em combinação com o método group() e as funções de agregação:

// Localiza categorias com mais de 100 produtos
$table->select('category_id, COUNT(*) AS count')
	->group('category_id')
	->having('count > ?', 100);

Leitura de dados

Para ler dados do banco de dados, há vários métodos úteis disponíveis:

O objeto ActiveRow é somente leitura. Isso significa que você não pode alterar os valores de suas propriedades. Essa restrição garante a consistência dos dados e evita efeitos colaterais inesperados. Os dados são obtidos do banco de dados e todas as alterações devem ser feitas explicitamente e de forma controlada.

foreach – Iteração em todas as linhas

A maneira mais fácil de executar uma consulta e recuperar linhas é por meio da iteração com o loop foreach. Ele executa automaticamente a consulta SQL.

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

get ($key): ?ActiveRow

Executa uma consulta SQL e retorna uma linha por sua chave primária ou null se ela não existir.

$book = $explorer->table('book')->get(123); // Retorna ActiveRow com ID 123 ou nulo
if ($book) {
	echo $book->title;
}

fetch(): ?ActiveRow

Retorna uma linha e avança o ponteiro interno para a próxima. Se não houver mais linhas, ele retorna null.

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

fetchPairs(): array

Retorna os resultados como uma matriz associativa. O primeiro argumento especifica o nome da coluna a ser usado como chave na matriz, e o segundo argumento especifica o nome da coluna a ser usado como valor:

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

Se apenas a coluna chave for especificada, o valor será a linha inteira, ou seja, o objeto ActiveRow:

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

Se null for especificado como a chave, a matriz será indexada numericamente a partir de zero:

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

Você também pode passar um retorno de chamada como parâmetro, que retornará o próprio valor ou um par de valores-chave para cada linha. Se o retorno de chamada retornar apenas um valor, a chave será a chave primária da linha:

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

// A chamada de retorno também pode retornar uma matriz com um par de chave e valor:
$titles = $explorer->table('book')
	->fetchPairs(fn($row) => [$row->title, $row->author->name]);
// ['First Book' => 'Jan Novak', ...]

fetchAll(): array

Retorna todas as linhas como uma matriz associativa de objetos ActiveRow, em que as chaves são os valores da chave primária.

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

count(): int

O método count() sem parâmetros retorna o número de linhas no objeto Selection:

$table->where('category', 1);
$count = $table->count();
$count = count($table); // alternativa

Observação: count() com um parâmetro executa a função de agregação COUNT no banco de dados, conforme descrito abaixo.

ActiveRow::toArray(): array

Converte o objeto ActiveRow em uma matriz associativa em que as chaves são os nomes das colunas e os valores são os dados correspondentes.

$book = $explorer->table('book')->get(1);
$bookArray = $book->toArray();
// $bookArray será ['id' => 1, 'title' => '...', 'author_id' => ..., ...]

Agregação

A classe Selection fornece métodos para executar facilmente funções de agregação (COUNT, SUM, MIN, MAX, AVG, etc.).

count (string $expr): int

Executa uma consulta SQL com a função COUNT e retorna o resultado. Esse método é usado para determinar quantas linhas correspondem a uma determinada condição:

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

Observação: count() sem um parâmetro simplesmente retorna o número de linhas no objeto Selection.

min (string $expr) and max(string $expr)

Os métodos min() e max() retornam os valores mínimo e máximo na coluna ou expressão especificada:

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

sum (string $expr): int

Retorna a soma dos valores na coluna ou expressão especificada:

// SELECT SUM(`preço` * `itens_em_estoque`) FROM `produtos` WHERE `ativo` = 1
$totalPrice = $products->where('active', true)
	->sum('price * items_in_stock');

aggregation (string $function, ?string $groupFunction = null): mixed

Permite a execução de qualquer função de agregação.

// Calcula o preço médio dos produtos em uma categoria
$avgPrice = $products->where('category_id', 1)
	->aggregation('AVG(price)');

// Combina tags de produtos em uma única string
$tags = $products->where('id', 1)
	->aggregation('GROUP_CONCAT(tag.name) AS tags')
	->fetch()
	->tags;

Se precisarmos agregar resultados que resultem de uma agregação e agrupamento (por exemplo, SUM(value) sobre linhas agrupadas), especificaremos a função de agregação a ser aplicada a esses resultados intermediários como o segundo argumento:

// Calcula o preço total dos produtos em estoque para cada categoria e, em seguida, soma esses preços
$totalPrice = $products->select('category_id, SUM(price * stock) AS category_total')
	->group('category_id')
	->aggregation('SUM(category_total)', 'SUM');

Neste exemplo, primeiro calculamos o preço total dos produtos em cada categoria (SUM(price * stock) AS category_total) e agrupamos os resultados por category_id. Em seguida, usamos aggregation('SUM(category_total)', 'SUM') para somar esses subtotais. O segundo argumento 'SUM' especifica a função de agregação a ser aplicada aos resultados intermediários.

Inserir, atualizar e excluir

O Nette Database Explorer simplifica a inserção, a atualização e a exclusão de dados. Todos os métodos mencionados lançam um Nette\Database\DriverException em caso de erro.

Selection::insert (iterable $data): static

Insere novos registros em uma tabela.

Inserção de um único registro:

O novo registro é passado como uma matriz associativa ou objeto iterável (como ArrayHash usado em formulários), em que as chaves correspondem aos nomes das colunas na tabela.

Se a tabela tiver uma chave primária definida, o método retornará um objeto ActiveRow, que é recarregado do banco de dados para refletir quaisquer alterações feitas no nível do banco de dados (por exemplo, acionadores, valores de coluna padrão ou cálculos de incremento automático). Isso garante a consistência dos dados e o objeto sempre contém os dados atuais do banco de dados. Se uma chave primária não for explicitamente definida, o método retornará os dados de entrada como uma matriz.

$row = $explorer->table('users')->insert([
	'name' => 'John Doe',
	'email' => 'john.doe@example.com',
]);
// $row é uma instância do ActiveRow que contém os dados completos da linha inserida,
// incluindo o ID gerado automaticamente e quaisquer alterações feitas por acionadores
echo $row->id;          // Emite o ID do usuário recém-inserido
echo $row->created_at;  // Emite a hora de criação, se definida por um acionador

Inserção de vários registros de uma vez:

O método insert() permite inserir vários registros com uma única consulta SQL. Nesse caso, ele retorna o número de linhas inseridas.

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

Você também pode passar um objeto Selection com uma seleção de dados como parâmetro.

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

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

Inserindo valores especiais:

Os valores podem incluir arquivos, objetos DateTime ou literais SQL:

$explorer->table('users')->insert([
	'name' => 'John',
	'created_at' => new DateTime,           // converte para o formato de banco de dados
	'avatar' => fopen('image.jpg', 'rb'),   // insere o conteúdo do arquivo binário
	'uuid' => $explorer::literal('UUID()'), // chama a função UUID()
]);

Selection::update (iterable $data): int

Atualiza as linhas em uma tabela com base em um filtro especificado. Retorna o número de linhas realmente modificadas.

As colunas a serem atualizadas são passadas como uma matriz associativa ou objeto iterável (como ArrayHash usado em formulários), em que as chaves correspondem aos nomes das colunas na tabela:

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

Para alterar valores numéricos, você pode usar os operadores += e -=:

$explorer->table('users')
	->where('id', 10)
	->update([
		'points+=' => 1,  // aumenta o valor da coluna "pontos" em 1
		'coins-=' => 1,   // diminui o valor da coluna "coins" em 1
	]);
// UPDATE `users` SET `points` = `points` + 1, `coins` = `coins` - 1 WHERE `id` = 10

Selection::delete(): int

Exclui linhas de uma tabela com base em um filtro especificado. Retorna o número de linhas excluídas.

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

Ao chamar update() ou delete(), certifique-se de usar where() para especificar as linhas a serem atualizadas ou excluídas. Se where() não for usado, a operação será executada em toda a tabela!

ActiveRow::update (iterable $data): bool

Atualiza os dados em uma linha do banco de dados representada pelo objeto ActiveRow. Ele aceita dados iteráveis como parâmetro, em que as chaves são nomes de colunas. Para alterar valores numéricos, você pode usar os operadores += e -=:

Após a atualização, o ActiveRow é automaticamente recarregado do banco de dados para refletir quaisquer alterações feitas no nível do banco de dados (por exemplo, acionadores). O método retorna true somente se tiver ocorrido uma alteração real nos dados.

$article = $explorer->table('article')->get(1);
$article->update([
	'views += 1',  // incrementa a contagem de visualizações
]);
echo $article->views; // Emite a contagem de visualizações atual

Esse método atualiza apenas uma linha específica no banco de dados. Para atualizações em massa de várias linhas, use o método Selection::update().

ActiveRow::delete()

Exclui uma linha do banco de dados que é representada pelo objeto ActiveRow.

$book = $explorer->table('book')->get(1);
$book->delete(); // Exclui o livro com ID 1

Esse método exclui apenas uma linha específica no banco de dados. Para a exclusão em massa de várias linhas, use o método Selection::delete().

Relacionamentos entre tabelas

Nos bancos de dados relacionais, os dados são divididos em várias tabelas e conectados por meio de chaves estrangeiras. O Nette Database Explorer oferece uma maneira revolucionária de trabalhar com esses relacionamentos – sem escrever consultas JOIN ou exigir qualquer configuração ou geração de entidades.

Para demonstração, usaremos o banco de dados de exemplo(disponível no GitHub). O banco de dados inclui as seguintes tabelas:

• author – autores e tradutores (colunas id, name, web, born)
• book – livros (colunas id, author_id, translator_id, title, sequel_id)
• tag – tags (colunas id, name)
• book_tag – tabela de links entre livros e tags (colunas book_id, tag_id)

Estrutura do banco de dados

Nesse exemplo de banco de dados de livros, encontramos vários tipos de relacionamentos (simplificados em comparação com a realidade):

• Um-para-muitos (1:N) – Cada livro tem um autor; um autor pode escrever múltiplos livros.
• Zero-para-muitos (0:N)** – Um livro pode ter um tradutor; um tradutor pode traduzir múltiplos livros.
• Zero para um (0:1)** – Um livro pode ter uma sequência.
• Muitos para muitos (M:N)** – Um livro pode ter várias tags, e uma tag pode ser atribuída a vários livros.

Nesses relacionamentos, há sempre uma tabela pai e uma tabela filha. Por exemplo, no relacionamento entre autores e livros, a tabela author é a tabela pai e a tabela book é a tabela filha – você pode pensar nisso como um livro sempre “pertencente” a um autor. Isso também se reflete na estrutura do banco de dados: a tabela filha book contém a chave estrangeira author_id, que faz referência à tabela pai author.

Se quisermos exibir os livros junto com os nomes de seus autores, temos duas opções. Ou recuperamos os dados usando uma única consulta SQL com um JOIN:

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

Ou recuperamos os dados em duas etapas – primeiro os livros, depois seus autores – e os reunimos em PHP:

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

A segunda abordagem é, surpreendentemente, mais eficiente. Os dados são obtidos apenas uma vez e podem ser mais bem utilizados no cache. É exatamente assim que o Nette Database Explorer funciona – ele cuida de tudo nos bastidores e fornece uma API limpa:

$books = $explorer->table('book');
foreach ($books as $book) {
	echo 'title: ' . $book->title;
	echo 'written by: ' . $book->author->name; // $book->author é um registro da tabela "author" (autor)
	echo 'translated by: ' . $book->translator?->name;
}

Acesso à tabela pai

O acesso à tabela pai é simples. Esses são relacionamentos como um livro tem um autor ou um livro pode ter um tradutor. O registro relacionado pode ser acessado por meio da propriedade de objeto ActiveRow – o nome da propriedade corresponde ao nome da coluna da chave estrangeira sem o sufixo id:

$book = $explorer->table('book')->get(1);
echo $book->author->name;      // encontra o autor por meio da coluna "author_id".
echo $book->translator?->name; // encontra o tradutor por meio da coluna "translator_id".

Ao acessar a propriedade $book->author, o Explorer procura uma coluna na tabela book que contenha a string author (ou seja, author_id). Com base no valor dessa coluna, ele recupera o registro correspondente da tabela author e o retorna como um objeto ActiveRow. Da mesma forma, $book->translator usa a coluna translator_id. Como a coluna translator_id pode conter null, o operador ?-> é usado.

Uma abordagem alternativa é fornecida pelo método ref(), que aceita dois argumentos – o nome da tabela de destino e a coluna de vinculação – e retorna uma instância ActiveRow ou null:

echo $book->ref('author', 'author_id')->name;      // link para o autor
echo $book->ref('author', 'translator_id')->name;  // link para o tradutor

O método ref() é útil se o acesso baseado em propriedade não puder ser usado, por exemplo, quando a tabela contém uma coluna com o mesmo nome da propriedade (author). Em outros casos, recomenda-se usar o acesso baseado em propriedades para melhorar a legibilidade.

O Explorer otimiza automaticamente as consultas ao banco de dados. Ao iterar pelos livros e acessar seus registros relacionados (autores, tradutores), o Explorer não gera uma consulta para cada livro individualmente. Em vez disso, ele executa apenas uma consulta SELECT para cada tipo de relacionamento, reduzindo significativamente a carga do banco de dados. Por exemplo:

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

Este código executará apenas três consultas otimizadas ao banco de dados:

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

A lógica para identificar a coluna de vinculação é definida pela implementação de Conventions. Recomendamos o uso do DiscoveredConventions, que analisa chaves estrangeiras e permite que você trabalhe sem problemas com os relacionamentos de tabela existentes.

Acesso à tabela filha

O acesso à tabela secundária funciona na direção oposta. Agora perguntamos quais livros este autor escreveu ou quais livros este tradutor traduziu. Para esse tipo de consulta, usamos o método related(), que retorna um objeto Selection com registros relacionados. Aqui está um exemplo:

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

// Produz todos os livros escritos pelo autor
foreach ($author->related('book.author_id') as $book) {
	echo "Wrote: $book->title";
}

// Gera todos os livros traduzidos pelo autor
foreach ($author->related('book.translator_id') as $book) {
	echo "Translated: $book->title";
}

O método related() aceita a descrição da relação como um único argumento usando a notação de ponto ou como dois argumentos separados:

$author->related('book.translator_id');    // um único argumento
$author->related('book', 'translator_id'); // dois argumentos

O Explorer pode detectar automaticamente a coluna de vinculação correta com base no nome da tabela pai. Nesse caso, ele faz a vinculação por meio da coluna book.author_id porque o nome da tabela de origem é author:

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

Se houver várias conexões possíveis, o Explorer lançará uma exceção AmbiguousReferenceKeyException.

É claro que também podemos usar o método related() ao iterar por vários registros em um loop, e o Explorer também otimizará automaticamente as consultas nesse caso:

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

Esse código gera apenas duas consultas SQL eficientes:

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

Relacionamento de muitos para muitos

Para um relacionamento de muitos para muitos (M:N), é necessária uma tabela de junção (no nosso caso, book_tag). Essa tabela contém duas colunas de chave estrangeira (book_id, tag_id). Cada coluna faz referência à chave primária de uma das tabelas conectadas. Para recuperar os dados relacionados, primeiro buscamos os registros da tabela de ligação usando related('book_tag') e, em seguida, prosseguimos para os dados de destino:

$book = $explorer->table('book')->get(1);
// Emite os nomes das tags atribuídas ao livro
foreach ($book->related('book_tag') as $bookTag) {
	echo $bookTag->tag->name; // obtém o nome da tag por meio da tabela de links
}

$tag = $explorer->table('tag')->get(1);
// Direção oposta: produz os títulos dos livros com essa tag
foreach ($tag->related('book_tag') as $bookTag) {
	echo $bookTag->book->title; // busca o título do livro
}

O Explorer otimiza novamente as consultas SQL em um formato eficiente:

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

Consultas em tabelas relacionadas

Nos métodos where(), select(), order() e group(), é possível usar notações especiais para acessar colunas de outras tabelas. O Explorer cria automaticamente os JOINs necessários.

A notação Dot (parent_table.column) é usada para relacionamentos 1:N, conforme visto da perspectiva da tabela pai:

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

// Encontra livros cujos nomes de autores começam com "Jon
$books->where('author.name LIKE ?', 'Jon%');

// Classifica os livros por nome de autor de forma decrescente
$books->order('author.name DESC');

// Gera o título do livro e o nome do autor
$books->select('book.title, author.name');

A notação de cólon é usada para relacionamentos 1:N da perspectiva da tabela pai:

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

// Encontra autores que escreveram um livro com "PHP" no título
$authors->where(':book.title LIKE ?', '%PHP%');

// Conta o número de livros de cada autor
$authors->select('*, COUNT(:book.id) AS book_count')
	->group('author.id');

No exemplo acima com a notação de dois pontos (:book.title), a coluna de chave estrangeira não é especificada explicitamente. O Explorer detecta automaticamente a coluna correta com base no nome da tabela pai. Nesse caso, ele se une por meio da coluna book.author_id porque o nome da tabela de origem é author. Se houver várias conexões possíveis, o Explorer lançará a exceção AmbiguousReferenceKeyException.

A coluna de vinculação pode ser especificada explicitamente entre parênteses:

// Encontra autores que traduziram um livro com "PHP" no título
$authors->where(':book(translator).title LIKE ?', '%PHP%');

As notações podem ser encadeadas para acessar dados em várias tabelas:

// Encontra autores de livros marcados com "PHP
$authors->where(':book:book_tag.tag.name', 'PHP')
	->group('author.id');

Extensão das condições para JOIN

O método joinWhere() acrescenta condições adicionais às junções de tabelas no SQL após a palavra-chave ON.

Por exemplo, digamos que queiramos encontrar livros traduzidos por um tradutor específico:

// Encontra livros traduzidos por um tradutor chamado 'David'
$books = $explorer->table('book')
	->joinWhere('translator', 'translator.name', 'David');
// LEFT JOIN autor tradutor ON livro.tradutor_id = tradutor.id AND (tradutor.nome = 'David')

Na condição joinWhere(), você pode usar as mesmas construções do método where() – operadores, espaços reservados, matrizes de valores ou expressões SQL.

Para consultas mais complexas com vários JOINs, podem ser definidos aliases de tabela:

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

Observe que, enquanto o método where() adiciona condições à cláusula WHERE, o método joinWhere() estende as condições da cláusula ON durante as uniões de tabelas.

Criação manual do Explorer

Se não estiver usando o contêiner Nette DI, você poderá criar uma instância do Nette\Database\Explorer manualmente:

use Nette\Database;

// $storage implementa Nette\Caching\Storage, por exemplo:
$storage = new Nette\Caching\Storages\FileStorage('/path/to/temp/dir');
// conexão com o banco de dados
$connection = new Database\Connection('mysql:host=127.0.0.1;dbname=mydatabase', 'user', 'password');
// gerencia a reflexão da estrutura do banco de dados
$structure = new Database\Structure($connection, $storage);
// define regras para mapear nomes de tabelas, colunas e chaves estrangeiras
$conventions = new Database\Conventions\DiscoveredConventions($structure);
$explorer = new Database\Explorer($connection, $structure, $conventions, $storage);