Acesso SQL

A Nette Database oferece dois caminhos: você pode escrever consultas SQL você mesmo (acesso SQL), ou deixá-las serem geradas automaticamente (veja Explorer). O acesso SQL dá a você controle total sobre as consultas, garantindo ao mesmo tempo sua construção segura.

Detalhes sobre conexão e configuração do banco de dados podem ser encontrados no capítulo Conexão e configuração.

Consultas básicas

Para consultar o banco de dados, use o método query(). Ele retorna um objeto ResultSet, que representa o resultado da consulta. Em caso de falha, o método lança uma exceção. Podemos 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, usamos consultas parametrizadas. A Nette Database torna isso o mais simples possível – basta adicionar uma vírgula e o valor após a consulta SQL:

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

Com múltiplos parâmetros, você tem duas opções de escrita. Você pode „intercalar“ a consulta SQL com 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 SQL injection

Por que é importante usar consultas parametrizadas? Porque elas protegem você contra um ataque chamado SQL injection, no qual um invasor poderia injetar seus próprios comandos SQL e, assim, obter ou danificar dados no banco de dados.

Nunca insira variáveis diretamente na consulta SQL! Sempre use consultas parametrizadas, que protegem você contra SQL injection.

// ❌ CÓDIGO PERIGOSO - vulnerável a SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Familiarize-se com os possíveis riscos de segurança.

Técnicas de consulta

Condições WHERE

As condições WHERE podem ser escritas como um array associativo, onde as chaves são os nomes das colunas e os valores são os dados para comparação. A Nette Database seleciona automaticamente o operador SQL mais apropriado com base no tipo de valor.

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

Na chave, você também pode especificar explicitamente o operador para comparação:

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

Nette trata automaticamente casos especiais como valores null ou arrays.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // usa o operador =
	'category_id' => [1, 2, 3], // usa IN
	'description' => null,      // usa 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' => [],                     // será omitido
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Para combinar condições, o operador AND é usado. Isso pode ser alterado usando o placeholder ?or.

Regras ORDER BY

A ordenação ORDER BY pode ser escrita usando um array. Nas chaves, especificamos as colunas e o valor será um booleano indicando se a ordenação é ascendente:

$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, usa-se o comando 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. Em alguns bancos de dados (por exemplo, PostgreSQL), é necessário especificar como parâmetro o nome da sequência da qual o ID deve ser gerado usando $database->getInsertId($sequenceId).

Como parâmetros, também podemos passar valores especiais como arquivos, objetos DateTime ou tipos enumerados.

Inserção de múltiplos registros de uma vez:

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

Um INSERT múltiplo é muito mais rápido porque uma única consulta ao banco de dados é executada, em vez de muitas individuais.

Aviso 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, usa-se o comando SQL UPDATE.

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

O número de linhas afetadas é retornado por $result->getRowCount().

Para UPDATE, podemos usar os operadores += e -=:

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

Exemplo de inserção ou atualização de um registro, se ele já existir. Usamos 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 a Nette Database reconhece em qual contexto do comando SQL o parâmetro com o array é inserido e constrói o código SQL a partir dele de acordo. Assim, do primeiro array, ele construiu (id, name, year) VALUES (123, 'Jim', 1978), enquanto o segundo foi convertido para a forma name = 'Jim', year = 1978. Discutimos isso em mais detalhes na seção Dicas para construir SQL.

Exclusão de dados (DELETE)

Para excluir registros, usa-se o comando SQL DELETE. Exemplo com obtenção do número de linhas excluídas:

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

Dicas para construir SQL

Uma dica é um placeholder especial na consulta SQL que diz como o valor do parâmetro deve ser reescrito em uma expressão SQL:

Dica	Descrição	Usado automaticamente
`?name`	usa para inserir nome da tabela ou coluna	–
`?values`	gera `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	gera atribuição `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	combina condições no array com o operador `AND`	`WHERE ?`, `HAVING ?`
`?or`	combina condições no array com o operador `OR`	–
`?order`	gera a cláusula `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Para inserir dinamicamente nomes de tabelas e colunas na consulta, use o placeholder ?name. A Nette Database cuida do tratamento correto dos identificadores de acordo com as convenções do banco de dados específico (por exemplo, envolvendo em crases no 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 o símbolo ?name apenas para nomes de tabelas e colunas de entradas validadas, caso contrário, você se expõe a um risco de segurança.

Outras dicas geralmente não precisam ser especificadas, pois Nette usa detecção automática inteligente ao montar a consulta SQL (veja a terceira coluna da tabela). Mas você pode usá-la, por exemplo, em uma situação 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 comuns (string, int, bool), você também pode passar valores especiais como parâmetros:

arquivos: fopen('image.gif', 'r') insere o conteúdo binário do arquivo
data e hora: objetos DateTime são convertidos para o formato do banco de dados
tipos enumerados: instâncias enum são convertidas para seu valor
literais SQL: criados com 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 de dados datetime (como SQLite e Oracle), DateTime é convertido para o valor especificado na configuração do banco de dados pelo item formatDateTime (o valor padrão é U – timestamp Unix).

Literais SQL

Em alguns casos, você precisa especificar diretamente o código SQL como um valor, que não deve ser entendido como uma string e escapado. Para isso, servem os objetos da classe Nette\Database\SqlLiteral. Eles são criados pelo 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())

Ou alternativamente:

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

Literais SQL podem conter parâmetros:

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

Graças a isso, podemos criar combinações interessantes:

$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, Connection oferece vários atalhos que combinam a chamada query() com a subsequente fetch*(). Esses métodos aceitam os mesmos parâmetros que query(), ou seja, a consulta SQL e parâmetros opcionais. Uma descrição completa dos métodos fetch*() pode ser encontrada abaixo.

`fetch($sql, ...$params): ?Row`	Executa a consulta e retorna a primeira linha como um objeto `Row`
`fetchAll($sql, ...$params): array`	Executa a consulta e retorna todas as linhas como um array de objetos `Row`
`fetchPairs($sql, ...$params): array`	Executa a consulta e retorna um array associativo, onde a primeira coluna representa a chave e a segunda o valor
`fetchField($sql, ...$params): mixed`	Executa a consulta e retorna o valor do primeiro campo da primeira linha
`fetchList($sql, ...$params): ?array`	Executa a consulta e retorna a primeira linha como um array indexado

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 da consulta, é retornado um objeto ResultSet, que permite percorrer os resultados de várias maneiras. A maneira mais fácil de executar uma consulta e obter linhas é iterando em um loop foreach. Este método é o mais eficiente em termos de memória, pois retorna os dados gradualmente e não os armazena na memória de uma vez.

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

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

ResultSet só pode ser iterado uma vez. Se precisar iterar repetidamente, você deve primeiro carregar os dados em um array, por exemplo, usando o método fetchAll().

fetch(): ?Row

Retorna a linha como um objeto Row. Se não houver mais linhas, retorna null. Move o ponteiro interno para a próxima linha.

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

fetchAll(): array

Retorna todas as linhas restantes do ResultSet como um array de objetos Row.

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

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

Retorna os resultados como um array associativo. O primeiro argumento especifica o nome da coluna a ser usada como chave no array, o segundo argumento especifica o nome da 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 especificarmos apenas o primeiro parâmetro, o valor será a linha inteira, ou seja, o objeto Row:

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

Em caso de chaves duplicadas, o valor da última linha é usado. Ao usar null como chave, o array será indexado numericamente a partir de zero (então não ocorrem colisões):

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

fetchPairs(Closure $callback): array

Alternativamente, você pode fornecer um callback como parâmetro, que retornará para cada linha ou o próprio valor, ou um par chave-valor.

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

// O callback também pode retornar um array com um par chave & valor:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Retorna o valor do primeiro campo da linha atual. Se não houver mais linhas, retorna null. Move o ponteiro interno para a próxima linha.

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

fetchList(): ?array

Retorna a linha como um array indexado. Se não houver mais linhas, retorna null. Move 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 SELECT, é o número de linhas retornadas, mas isso pode não ser conhecido – nesse caso, o método retorna null.

getColumnCount(): ?int

Retorna o número de colunas no ResultSet.

Informações sobre consultas

Para fins de depuração, podemos obter informações sobre a última consulta executada:

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

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

Para exibir o resultado como uma tabela HTML, pode-se usar:

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

ResultSet oferece informações sobre os tipos das colunas:

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

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

Registro de consultas

Podemos implementar nosso próprio registro de consultas. O evento onQuery é um array de callbacks que são chamados após cada consulta executada:

$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');
	}
};

Nette Documentation Preview