SQL direto

Você pode trabalhar com o Nette Database de duas maneiras: escrevendo consultas SQL diretamente (Direct Access) ou permitindo que o SQL seja gerado automaticamente(Explorer Access). O Direct Access permite criar consultas com segurança e manter controle total sobre sua estrutura.

Para obter informações sobre como criar uma conexão e configurá-la, consulte a página separada.

Consulta básica

O método query() executa consultas ao banco de dados e retorna um objeto ResultSet que representa o resultado. Se a consulta falhar, o método lançará uma exceção. Você pode percorrer o resultado da consulta usando um loop foreach ou usar uma das funções auxiliares.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

Para inserir valores com segurança em consultas SQL, use consultas parametrizadas. O Nette Database torna isso muito simples: basta acrescentar uma vírgula e o valor à consulta SQL.

$database->query('SELECT * FROM users WHERE name = ?', $name);

Para vários parâmetros, você pode intercalar a consulta SQL com os parâmetros:

$database->query('SELECT * FROM users WHERE name = ?', $name, 'AND age > ?', $age);

Ou escrever a consulta SQL inteira primeiro e depois anexar todos os parâmetros:

$database->query('SELECT * FROM users WHERE name = ? AND age > ?', $name, $age);

Proteção contra injeção de SQL

Por que é importante usar consultas parametrizadas? Porque elas o protegem contra ataques de injeção de SQL, em que os invasores podem injetar comandos SQL mal-intencionados para manipular ou acessar dados do banco de dados.

**Nunca insira variáveis diretamente em uma consulta SQL! Sempre use consultas parametrizadas para se proteger contra injeção de SQL.

// CÓDIGO INSEGURO - vulnerável à injeção de SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

// Consulta parametrizada segura
$database->query('SELECT * FROM users WHERE name = ?', $name);

Não deixe de se familiarizar com os possíveis riscos de segurança.

Técnicas de consulta

Condições WHERE

Você pode escrever as condições WHERE como uma matriz associativa, em que as chaves são os nomes das colunas e os valores são os dados a serem comparados. O Nette Database seleciona automaticamente o operador SQL mais adequado com base no tipo de valor.

$database->query('SELECT * FROM users WHERE', [
	'name' => 'John',
	'active' => true,
]);
// WHERE `name` = 'John' AND `active` = 1

Você também pode especificar explicitamente o operador na chave:

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,           // usa o operador >
	'name LIKE' => '%John%', // usa o operador LIKE
	'email NOT LIKE' => '%example.com%', // usa o operador NOT LIKE
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Casos especiais como null valores ou matrizes são tratados automaticamente:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // usa o operador =
	'category_id' => [1, 2, 3], // usa IN
	'description' => null,      // uses IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Para condições negativas, use o operador NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // usa o operador <>
	'category_id NOT' => [1, 2, 3], // usa NOT IN
	'description NOT' => null,      // usa IS NOT NULL
	'id' => [],                     // ignorado
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Por padrão, as condições são combinadas usando o operador AND. Você pode alterar esse comportamento usando o espaço reservado ?or.

Regras ORDER BY

A cláusula ORDER BY pode ser definida como uma matriz, em que as chaves representam colunas e os valores são booleanos que indicam a ordem crescente:

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true,  // ascendente
	'name' => false, // descendente
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

Inserção de dados (INSERT)

Para inserir registros, use a instrução SQL INSERT.

$values = [
	'name' => 'John Doe',
	'email' => 'john@example.com',
];
$database->query('INSERT INTO users ?', $values);
$userId = $database->getInsertId();

O método getInsertId() retorna o ID da última linha inserida. Para determinados bancos de dados (por exemplo, PostgreSQL), você deve especificar o nome da sequência usando $database->getInsertId($sequenceId).

Você também pode passar valores especiais, como arquivos, objetos DateTime ou tipos de enum, como parâmetros.

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

$database->query('INSERT INTO users ?', [
	['name' => 'User 1', 'email' => 'user1@mail.com'],
	['name' => 'User 2', 'email' => 'user2@mail.com'],
]);

A execução de um INSERT em lote é muito mais rápida porque apenas uma única consulta ao banco de dados é executada em vez de várias consultas individuais.

Nota de segurança: Nunca use dados não validados como $values. Familiarize-se com os possíveis riscos.

Atualização de dados (UPDATE)

Para atualizar registros, use a instrução SQL UPDATE.

// Atualizar um único registro
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Você pode verificar o número de linhas afetadas usando $result->getRowCount().

Você pode usar os operadores += e -= em UPDATE:

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // incrementar login_count
], 1);

Para inserir ou atualizar um registro se ele já existir, use a técnica ON DUPLICATE KEY UPDATE:

$values = [
	'name' => $name,
	'year' => $year,
];
$database->query('INSERT INTO users ? ON DUPLICATE KEY UPDATE ?',
	$values + ['id' => $id],
	$values,
);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//  ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Observe que o Nette Database reconhece o contexto do comando SQL no qual um parâmetro com uma matriz é usado e gera o código SQL de acordo. Por exemplo, ele construiu (id, name, year) VALUES (123, 'Jim', 1978) a partir da primeira matriz, enquanto converteu a segunda em name = 'Jim', year = 1978. Isso é abordado em mais detalhes na seção Dicas para a construção de SQL.

Exclusão de dados (DELETE)

Para excluir registros, use a instrução SQL DELETE. Exemplo com o número de linhas excluídas:

$count = $database->query('DELETE FROM users WHERE id = ?', 1)
	->getRowCount();

Dicas de construção de SQL

Os placeholders de SQL permitem que você controle como os valores de parâmetros são incorporados às expressões SQL:

Dica	Descrição	Usado automaticamente para
`?name`	Usado para nomes de tabelas ou colunas	–
`?values`	Gera `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Gera atribuições `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Une condições em uma matriz com `AND`	`WHERE ?`, `HAVING ?`
`?or`	Une condições em um array com `OR`	–
`?order`	Gera a cláusula `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Para inserir dinamicamente nomes de tabelas ou colunas, use o espaço reservado ?name. O Nette Database garante o escape adequado de acordo com as convenções do banco de dados (por exemplo, entre aspas para MySQL).

$table = 'users';
$column = 'name';
$database->query('SELECT ?name FROM ?name WHERE id = 1', $column, $table);
// SELECT `name` FROM `users` WHERE id = 1 (no MySQL)

Aviso: Use apenas o espaço reservado ?name para nomes validados de tabelas e colunas. Caso contrário, você corre o risco de sofrer vulnerabilidades de segurança.

Normalmente, não é necessário especificar outras dicas, pois a Nette usa a detecção automática inteligente ao construir consultas SQL (consulte a terceira coluna da tabela). No entanto, você pode usá-las em situações em que deseja combinar condições usando OR em vez de AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

Valores especiais

Além dos tipos escalares padrão (por exemplo, string, int, bool), você também pode passar valores especiais como parâmetros:

Files (Arquivos): Use fopen('file.png', 'r') para inserir o conteúdo binário de um arquivo.
Data e hora: os objetos DateTime são convertidos automaticamente para o formato de data do banco de dados.
Valores de enum: As instâncias de enum são convertidas em seus valores correspondentes.
Literais SQL: Criados usando Connection::literal('NOW()'), são inseridos diretamente na consulta.

$database->query('INSERT INTO articles ?', [
	'title' => 'My Article',
	'published_at' => new DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Para bancos de dados que não têm suporte nativo para o tipo datetime (por exemplo, SQLite e Oracle), os valores DateTime são convertidos de acordo com a opção de configuração formatDateTime (padrão: U para carimbo de data/hora Unix).

Literais SQL

Em alguns casos, talvez seja necessário inserir o código SQL bruto como um valor sem tratá-lo como uma cadeia de caracteres ou escapar dele. Para isso, use objetos da classe Nette\Database\SqlLiteral, que podem ser criados usando o método Connection::literal().

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

Como alternativa:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

Os literais SQL também podem conter parâmetros:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year &lt; ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Isso permite combinações flexíveis:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Obtenção de dados

Atalhos para consultas SELECT

Para simplificar a recuperação de dados, a classe Connection oferece vários atalhos que combinam uma chamada query() com uma chamada fetch*() subsequente. Esses métodos aceitam os mesmos parâmetros que query(), ou seja, uma consulta SQL e parâmetros opcionais. Uma descrição detalhada dos métodos fetch*() pode ser encontrada abaixo.

`fetch($sql, ...$params): ?Row`	Executa a consulta e obtém a primeira linha como um objeto `Row`.
`fetchAll($sql, ...$params): array`	Executa a consulta e obtém todas as linhas como uma matriz de objetos `Row`.
`fetchPairs($sql, ...$params): array`	Executa a consulta e obtém uma matriz associativa em que a primeira coluna é a chave e a segunda é o valor.
`fetchField($sql, ...$params): mixed`	Executa a consulta e obtém o valor da primeira célula na primeira linha.
`fetchList($sql, ...$params): ?array`	Executa a consulta e obtém a primeira linha como uma matriz indexada.

Exemplo:

// fetchField() - retorna o valor da primeira célula
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – Iteração sobre linhas

Após a execução de uma consulta, um objeto ResultSet é retornado, o que permite iterar sobre os resultados de várias maneiras. A maneira mais simples e mais eficiente em termos de memória para buscar linhas é a iteração em um loop foreach. Esse método processa as linhas uma de cada vez e evita o armazenamento de todos os dados na memória de uma só vez.

$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
	//...
}

O ResultSet só pode ser iterado uma vez. Se precisar iterar várias vezes, você deve primeiro carregar os dados em uma matriz, por exemplo, usando o método fetchAll().

fetch(): ?Row

Executa a consulta e obtém uma única linha como um objeto Row. Se não houver mais linhas disponíveis, ele retorna null. Esse método avança o ponteiro interno para a próxima linha.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // busca a primeira linha
if ($row) {
	echo $row->name;
}

fetchAll(): array

Obtém todas as linhas restantes do site ResultSet como uma matriz de objetos Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // obtém todas as linhas
foreach ($rows as $row) {
	echo $row->name;
}

fetchPairs (string|int|null $key = null, string|int|null $value = null): array

Obtém os resultados como uma matriz associativa. O primeiro argumento especifica a coluna a ser usada como chave e o segundo especifica a coluna a ser usada como valor:

$result = $database->query('SELECT id, name FROM users');
$names = $result->fetchPairs('id', 'name');
// [1 => 'John Doe', 2 => 'Jane Doe', ...]

Se apenas o primeiro parâmetro for fornecido, o valor será a linha inteira (como um objeto Row ):

$rows = $result->fetchPairs('id');
// [1 => Row(id: 1, name: 'John'), 2 => Row(id: 2, name: 'Jane'), ...]

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

$names = $result->fetchPairs(null, 'name');
// [0 => 'John Doe', 1 => 'Jane Doe', ...]

fetchPairs (Closure $callback): array

Como alternativa, você pode fornecer um retorno de chamada que determine os pares de valores-chave ou valores para cada linha.

$result = $database->query('SELECT * FROM users');
$items = $result->fetchPairs(fn($row) => "$row->id - $row->name");
// ['1 - John', '2 - Jane', ...]

// A chamada de retorno também pode retornar uma matriz com um par de chave e valor:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Obtém o valor da primeira célula na linha atual. Se não houver mais linhas disponíveis, ele retornará null. Esse método avança o ponteiro interno para a próxima linha.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // obtém o nome da primeira linha

fetchList(): ?array

Obtém a linha como uma matriz indexada. Se não houver mais linhas disponíveis, ele retornará null. Esse método avança o ponteiro interno para a próxima linha.

$result = $database->query('SELECT name, email FROM users');
$row = $result->fetchList(); // ['John', 'john@example.com']

getRowCount(): ?int

Retorna o número de linhas afetadas pela última consulta UPDATE ou DELETE. Para as consultas SELECT, ele retorna o número de linhas obtidas, mas isso nem sempre é conhecido – nesses casos, ele retorna null.

getColumnCount(): ?int

Retorna o número de colunas no site ResultSet.

Informações da consulta

Para recuperar detalhes sobre a consulta executada mais recentemente, use:

echo $database->getLastQueryString(); // gera a consulta SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // gera a consulta SQL
echo $result->getTime();           // gera o tempo de execução em segundos

Para exibir o resultado como uma tabela HTML, use:

$result = $database->query('SELECT * FROM articles');
$result->dump();

Você também pode obter informações sobre os tipos de coluna no site ResultSet:

$result = $database->query('SELECT * FROM articles');
$types = $result->getColumnTypes();

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // Por exemplo, 'id é do tipo int'
}

Registro de consultas

Você pode implementar o registro de consultas personalizado. O evento onQuery é uma matriz de retornos de chamada que são invocados após cada execução de consulta:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Time: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Large result set: ' . $result->getRowCount() . ' rows');
	}
};