Acceso SQL

Nette Database ofrece dos vías: puede escribir consultas SQL usted mismo (acceso SQL), o dejar que se generen automáticamente (consulte Explorer). El acceso SQL le da control total sobre las consultas y al mismo tiempo asegura su construcción segura.

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

Consultas básicas

Para consultar la base de datos, se utiliza el método query(). Devuelve un objeto ResultSet, que representa el resultado de la consulta. En caso de fallo, el método lanza una excepción. Podemos recorrer el resultado de la consulta usando un bucle foreach, o usar una de las funciones auxiliares.

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

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

Para insertar valores de forma segura en consultas SQL, usamos consultas parametrizadas. Nette Database las hace extremadamente simples: solo agregue una coma y el valor después de la consulta SQL:

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

Con múltiples parámetros, tiene dos opciones de sintaxis. Puede „intercalar“ la consulta SQL con parámetros:

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

O escribir primero toda la consulta SQL y luego adjuntar todos los parámetros:

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

Protección contra inyección SQL

¿Por qué es importante usar consultas parametrizadas? Porque lo protegen de un ataque llamado inyección SQL, en el que un atacante podría introducir sus propios comandos SQL y así obtener o dañar datos en la base de datos.

¡Nunca inserte variables directamente en la consulta SQL! Siempre use consultas parametrizadas, que lo protegerán 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

Puede escribir condiciones WHERE como un array asociativo, donde las claves son los nombres de las columnas y los valores son los datos para la comparación. Nette Database selecciona automáticamente el operador SQL más adecuado según el tipo de valor.

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

También puede especificar 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 maneja automáticamente casos especiales como valores null o 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 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' => [],                     // se omite
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Para unir condiciones, se utiliza el operador AND. Esto se puede cambiar usando el marcador de posición ?or.

Reglas ORDER BY

La ordenación ORDER BY se puede escribir usando un array. En las claves, especificamos las columnas y el valor será un booleano que indica si ordenar ascendentemente:

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

Inserción de datos (INSERT)

Para insertar registros, se utiliza 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. Para algunas bases de datos (por ejemplo, PostgreSQL), es necesario especificar como parámetro el nombre de la secuencia desde la cual se debe generar el ID usando $database->getInsertId($sequenceId).

También podemos pasar Valores especiales como parámetros, como archivos, objetos DateTime o tipos enumerados.

Insertar múltiples registros a la vez:

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

El INSERT múltiple es mucho más rápido porque se ejecuta una única consulta a la base de datos, en lugar de muchas individuales.

Advertencia de seguridad: Nunca use datos no validados como $values. Familiarícese con los posibles riesgos.

Actualización de datos (UPDATE)

Para actualizar registros, se utiliza el comando SQL UPDATE.

// Actualizar un 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().

Para UPDATE, podemos usar los operadores += y -=:

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

Ejemplo de inserción o modificación de 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

Observe que Nette Database reconoce en qué contexto del comando SQL insertamos el parámetro con el array 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ó a la forma name = 'Jim', year = 1978. Nos ocupamos de esto con más detalle en la sección Pistas para construir SQL.

Eliminación de datos (DELETE)

Para eliminar registros, se utiliza el comando SQL DELETE. Ejemplo con obtención del número de filas eliminadas:

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

Pistas para construir SQL

Una pista es un marcador de posición especial en una consulta SQL que indica cómo se debe reescribir el valor del parámetro en una expresión SQL:

Pista	Descripción	Se usa automáticamente
`?name`	se usa para insertar el nombre de tabla o columna	–
`?values`	genera `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	genera la asignación `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	une condiciones en el array con el operador `AND`	`WHERE ?`, `HAVING ?`
`?or`	une condiciones en el array con el operador `OR`	–
`?order`	genera la cláusula `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Para la inserción dinámica de nombres de tablas y columnas en la consulta, se utiliza el marcador de posición ?name. Nette Database se encarga del tratamiento correcto de los identificadores según las convenciones de la base de datos dada (por ejemplo, encerrándolos entre comillas invertidas 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)

Advertencia: use el símbolo ?name solo para nombres de tablas y columnas de entradas validadas, de lo contrario se expone a un riesgo de seguridad.

Otras pistas generalmente no necesitan ser especificadas, ya que Nette utiliza una detección automática inteligente al construir la consulta SQL (ver la tercera columna de la tabla). Pero puede usarla, por ejemplo, en una situación en la que desee unir condiciones usando 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 comunes (string, int, bool), también puede pasar valores especiales como parámetros:

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

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

Para bases de datos que no tienen soporte nativo para el tipo de dato datetime (como SQLite y Oracle), DateTime se convierte al valor especificado en la configuración de la base de datos por la entrada formatDateTime (el valor predeterminado es U – timestamp unix).

Literales SQL

En algunos casos, necesita especificar directamente código SQL como valor, que no debe entenderse como una cadena y escaparse. Para esto sirven los objetos de la clase Nette\Database\SqlLiteral. Son creados por 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)

Gracias a lo cual podemos crear 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')

Obtención de datos

Atajos para consultas SELECT

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

`fetch($sql, ...$params): ?Row`	Ejecuta la consulta y devuelve la primera fila como un objeto `Row`
`fetchAll($sql, ...$params): array`	Ejecuta la consulta y devuelve todas las filas como un array de objetos `Row`
`fetchPairs($sql, ...$params): array`	Ejecuta la consulta y devuelve un array asociativo, donde la primera columna representa la clave y la segunda el valor
`fetchField($sql, ...$params): mixed`	Ejecuta la consulta y devuelve el valor del primer campo de la primera fila
`fetchList($sql, ...$params): ?array`	Ejecuta la consulta y devuelve la primera fila como un array indexado

Ejemplo:

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

`foreach` – iteración sobre filas

Después de ejecutar la 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 filas es iterando en un bucle foreach. Este método es el más eficiente en cuanto a memoria, ya que devuelve los datos gradualmente y no los almacena todos en la memoria a la vez.

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

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

ResultSet solo se puede iterar una vez. Si necesita iterar repetidamente, primero debe cargar los datos en un array, por ejemplo, usando el método fetchAll().

fetch(): ?Row

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

$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 un 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 los resultados como un array asociativo. El primer argumento especifica el nombre de la columna que se usará como clave en el array, el segundo argumento especifica el nombre de la columna que se usará como valor:

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

Si solo especificamos el primer parámetro, el valor será la fila completa, es decir, 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 utiliza el valor de la última fila. Al usar null como clave, el array se indexará numéricamente desde cero (entonces no hay colisiones):

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

fetchPairs(Closure $callback): array

Alternativamente, puede pasar un callback como parámetro, que devolverá para cada fila ya sea el valor en sí, 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 & valor:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Devuelve el valor del primer campo de la fila actual. Si no hay más filas, devuelve null. Mueve el puntero interno a la siguiente fila.

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

fetchList(): ?array

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

$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. Para SELECT, es el número de filas devueltas, pero este puede no ser conocido; en tal caso, el método devuelve null.

getColumnCount(): ?int

Devuelve el número de columnas en el ResultSet.

Información sobre consultas

Para fines de depuración, 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 una tabla HTML, se puede usar:

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

ResultSet ofrece información sobre los tipos de columnas:

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

foreach ($types as $column => $type) {
	echo "$column es de tipo $type->type"; // por ejemplo, 'id es de tipo int'
}

Registro de 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('Consulta: ' . $result->getQueryString());
	$logger->info('Tiempo: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Conjunto de resultados grande: ' . $result->getRowCount() . ' filas');
	}
};

Nette Documentation Preview