Enfoque SQL

Nette Database ofrece dos formas de trabajar: puede escribir usted mismo las consultas SQL (enfoque SQL) o dejar que se generen automáticamente (véase Explorer). El enfoque SQL le da control total sobre las consultas y a la vez garantiza que se construyan de forma segura.

Los detalles sobre la conexión y la configuración de la base de datos los encontrará en el capítulo Conexión y configuración.

Consultas básicas

Para consultar la base de datos se usa el método query(). Devuelve un objeto ResultSet, que representa el resultado de la consulta. Si la consulta falla, el método lanza una excepción. Puede recorrer el resultado de la consulta con un bucle foreach o usar alguno de los métodos auxiliares.

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

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

Para insertar valores en las consultas SQL de forma segura, use consultas parametrizadas. Nette Database lo hace extremadamente sencillo: basta con añadir una coma y el valor después de la consulta SQL:

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

Con varios parámetros tiene dos opciones. Puede intercalar la consulta SQL con los parámetros:

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

O escribir primero la consulta SQL entera y añadir después todos los parámetros:

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

Protección contra la inyección SQL

¿Por qué es importante usar consultas parametrizadas? Porque le protegen de un ataque llamado inyección SQL, en el que un atacante podría inyectar sus propios comandos SQL y con ello acceder a los datos de la base de datos o dañarlos.

¡Nunca inserte variables directamente en una consulta SQL! Use siempre consultas parametrizadas, que le protegen de la inyección SQL.

// ❌ CÓDIGO PELIGROSO - vulnerable a la inyección SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Familiarícese con los posibles riesgos de seguridad.

Técnicas de consulta

Condiciones WHERE

Las condiciones WHERE se pueden escribir como un array asociativo en el que las claves son los nombres de las columnas y los valores son los datos con los que comparar. Nette Database elige automáticamente el operador SQL más adecuado según el tipo del valor.

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

También puede indicar explícitamente el operador de comparación en la clave:

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

Nette gestiona automáticamente los casos especiales, como los valores null o los arrays.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // usa el 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 las condiciones negativas use el operador NOT:

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

De forma predeterminada, las condiciones se unen con el operador AND. Se puede cambiar con el marcador ?or.

Reglas de ORDER BY

La cláusula ORDER BY se puede escribir con un array. Indique las columnas en las claves y use un valor booleano para señalar el orden ascendente (true) o descendente (false):

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

Insertar datos (INSERT)

Para insertar registros se usa el comando SQL INSERT.

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

El método getInsertId() devuelve el ID de la última fila insertada. En algunas bases de datos (p. ej. PostgreSQL) hay que indicar como parámetro el nombre de la secuencia de la que debe generarse el ID, con $database->getInsertId($sequenceId).

Como parámetros también puede pasar Valores especiales, como archivos, objetos DateTime o tipos enum.

Insertar varios registros a la vez:

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

Un INSERT de varios registros es mucho más rápido, porque se ejecuta una sola consulta a la base de datos en lugar de muchas individuales.

Nota de seguridad: nunca use datos sin validar como $values. Familiarícese con los posibles riesgos.

Actualizar datos (UPDATE)

Para actualizar registros se usa el comando SQL UPDATE.

// Actualiza un solo registro
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

El número de filas afectadas lo devuelve $result->getRowCount().

En UPDATE podemos usar los operadores += y -=:

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

Ejemplo de insertar o actualizar un registro si ya existe. Usamos la 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

Fíjese en que Nette Database reconoce el contexto en el que se usa un parámetro de tipo array dentro del comando SQL y construye el código SQL en consecuencia. Así, del primer array construyó (id, name, year) VALUES (123, 'Jim', 1978), mientras que el segundo lo convirtió en la forma name = 'Jim', year = 1978. Lo tratamos con más detalle en la sección Marcadores para construir SQL.

Borrar datos (DELETE)

Para borrar registros se usa el comando SQL DELETE. Ejemplo de cómo obtener el número de filas borradas:

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

Marcadores para construir SQL

Un marcador es un símbolo especial en una consulta SQL que indica cómo debe convertirse el valor del parámetro en una expresión SQL:

Marcador	Descripción	Se usa automáticamente en
`?name`	Sirve para insertar nombres de tablas o columnas	–
`?values`	Genera `(clave, ...) VALUES (valor, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Genera asignaciones `clave = valor, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Une las condiciones de un array con `AND`	`WHERE ?`, `HAVING ?`
`?or`	Une las condiciones de un array con `OR`	–
`?order`	Genera la cláusula `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

El marcador ?name sirve para insertar dinámicamente nombres de tablas y columnas en la consulta. Nette Database se encarga del entrecomillado correcto de los identificadores según las convenciones de la base de datos (p. ej. encerrándolos entre acentos graves en MySQL).

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

Atención: use el marcador ?name solo para nombres de tablas y columnas validados. De lo contrario se arriesga a sufrir vulnerabilidades de seguridad.

Los demás marcadores no suelen hacer falta indicarlos, porque Nette usa una autodetección inteligente al construir la consulta SQL (véase la tercera columna de la tabla). Pero puede usarlos, por ejemplo, en una situación en la que quiera unir las condiciones con OR en lugar 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 especiales

Además de los tipos escalares habituales (string, int, bool), como parámetros también puede pasar valores especiales:

archivos: fopen('image.gif', 'r') inserta el contenido binario del archivo
fecha y hora: los objetos DateTimeInterface se convierten al formato de la base de datos
tipos enum: las instancias de enum se convierten a su valor
literales SQL: creados con Connection::literal('NOW()'), se insertan directamente en la consulta

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

En las bases de datos que no tienen soporte nativo para el tipo de dato datetime (como SQLite y Oracle), los objetos DateTime y DateTimeImmutable se convierten a un valor indicado en la configuración de la base de datos mediante el elemento formatDateTime (el valor por defecto es U, el timestamp de Unix).

Literales SQL

En algunos casos necesita pasar como valor código SQL en bruto, que no debe tratarse como cadena ni escaparse. Para eso sirven los objetos de la clase Nette\Database\SqlLiteral. Se crean con el 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())

O alternativamente:

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

Los literales SQL pueden contener 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)

Eso permite combinaciones interesantes:

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

Obtener los datos

Atajos para las consultas SELECT

Para simplificar la obtención de datos, Connection ofrece varios atajos que combinan una llamada a query() con la llamada posterior a fetch*(). Estos métodos aceptan los mismos parámetros que query(), es decir, una consulta SQL y parámetros opcionales. La descripción completa de los métodos fetch*() la encontrará más abajo.

`fetch($sql, ...$params): ?Row`	Ejecuta la consulta y devuelve la primera fila como objeto `Row`, o `null`.
`fetchAll($sql, ...$params): array`	Ejecuta la consulta y devuelve todas las filas como array de objetos `Row`.
`fetchPairs($sql, ...$params): array`	Ejecuta la consulta y devuelve un array asociativo (pares clave ⇒ valor).
`fetchField($sql, ...$params): mixed`	Ejecuta la consulta y devuelve el valor de la primera columna de la primera fila.
`fetchList($sql, ...$params): ?array`	Ejecuta la consulta y devuelve la primera fila como array indexado, o `null`.

Ejemplo:

// fetchField() - devuelve el valor de la primera celda
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach`: recorrer las filas

Tras ejecutar una consulta se devuelve un objeto ResultSet, que permite recorrer los resultados de varias maneras. La forma más fácil de ejecutar una consulta y obtener las filas es recorrerla con un bucle foreach. Este método es el más eficiente en memoria, porque obtiene los datos fila a fila y no carga todo el conjunto de resultados en memoria de una vez.

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

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

El ResultSet solo se puede recorrer una vez. Si necesita recorrerlo repetidamente, primero tiene que cargar los datos en un array, por ejemplo con el método fetchAll().

fetch(): ?Row

Devuelve una fila como objeto Row. Si ya no hay más filas, devuelve null. Avanza el puntero interno a la fila siguiente.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // carga la primera fila
if ($row) {
	echo $row->name;
}

fetchAll(): array

Devuelve todas las filas restantes del ResultSet como array de objetos Row.

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

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

Devuelve el conjunto de resultados como array asociativo. El primer argumento indica la columna que se usará como claves y el segundo, la que se usará como valores:

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

Si solo se indica el primer parámetro ($key), como valor se usará la fila entera (el objeto Row):

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

En caso de claves duplicadas se usa el valor de la última fila. Usar null como clave da un array indexado numéricamente (empezando desde cero), lo que evita las colisiones de claves:

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

fetchPairs (Closure $callback): array

Alternativamente puede indicar un callback que procese cada fila. El callback puede devolver un único valor o un par clave-valor.

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

// El callback también puede devolver un array con un par clave y valor:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Devuelve el valor de la primera columna de la fila actual. Si ya no hay más filas, devuelve null. Avanza el puntero interno a la fila siguiente.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // carga el nombre de la primera fila

fetchList(): ?array

Devuelve la fila como array indexado. Si ya no hay más filas, devuelve null. Avanza el puntero interno a la fila siguiente.

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

getRowCount(): ?int

Devuelve el número de filas afectadas por la última consulta UPDATE o DELETE. En las consultas SELECT devuelve el número de filas del conjunto de resultados. Eso, sin embargo, no siempre se puede saber, y en ese caso el método devuelve null.

getColumnCount(): ?int

Devuelve el número de columnas del ResultSet.

Información sobre la consulta

Para depurar podemos obtener información sobre la última consulta ejecutada:

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

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // imprime la consulta SQL
echo $result->getTime();           // imprime el tiempo de ejecución en segundos

Para mostrar el resultado como tabla HTML puede usar:

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

ResultSet ofrece información sobre los tipos de las columnas:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type"; // p. ej. 'id is of type int'
}

Registro de las consultas

Podemos implementar nuestro propio registro de consultas. El evento onQuery es un array de callbacks que se llaman después de cada consulta ejecutada:

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