Accès SQL

Nette Database offre deux voies : vous pouvez écrire vous-même des requêtes SQL (accès SQL), ou les laisser générer automatiquement (voir Explorer). L'accès SQL vous donne un contrôle total sur les requêtes tout en assurant leur construction sécurisée.

Les détails sur la connexion et la configuration de la base de données se trouvent dans le chapitre Connexion et configuration.

Requêtes de base

Pour interroger la base de données, utilisez la méthode query(). Elle renvoie un objet ResultSet qui représente le résultat de la requête. En cas d'échec, la méthode lève une exception. Nous pouvons parcourir le résultat de la requête à l'aide d'une boucle foreach, ou utiliser l'une des fonctions d'aide.

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

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

Pour insérer des valeurs en toute sécurité dans les requêtes SQL, nous utilisons des requêtes paramétrées. Nette Database les rend extrêmement simples – il suffit d'ajouter une virgule et la valeur après la requête SQL :

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

Avec plusieurs paramètres, vous avez deux options d'écriture. Vous pouvez soit “intercaler” les paramètres dans la requête SQL :

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

Soit écrire d'abord la requête SQL complète, puis joindre tous les paramètres :

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

Protection contre l'injection SQL

Pourquoi est-il important d'utiliser des requêtes paramétrées ? Parce qu'elles vous protègent contre une attaque appelée injection SQL, où un attaquant pourrait injecter ses propres commandes SQL et ainsi obtenir ou endommager des données dans la base de données.

N'insérez jamais de variables directement dans la requête SQL ! Utilisez toujours des requêtes paramétrées, qui vous protègent contre l'injection SQL.

// ❌ CODE DANGEREUX - vulnérable à l'injection SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ Requête paramétrée sécurisée
$database->query('SELECT * FROM users WHERE name = ?', $name);

Familiarisez-vous avec les risques de sécurité possibles.

Techniques de requête

Conditions WHERE

Les conditions WHERE peuvent être écrites sous forme de tableau associatif, où les clés sont les noms des colonnes et les valeurs sont les données à comparer. Nette Database choisit automatiquement l'opérateur SQL le plus approprié en fonction du type de valeur.

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

Vous pouvez également spécifier explicitement l'opérateur de comparaison dans la clé :

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

Nette gère automatiquement les cas spéciaux comme les valeurs null ou les tableaux.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // utilise l'opérateur =
	'category_id' => [1, 2, 3], // utilise IN
	'description' => null,      // utilise IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

Pour les conditions négatives, utilisez l'opérateur NOT :

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // utilise l'opérateur <>
	'category_id NOT' => [1, 2, 3], // utilise NOT IN
	'description NOT' => null,      // utilise IS NOT NULL
	'id' => [],                     // sera omis
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Pour joindre des conditions, l'opérateur AND est utilisé. Cela peut être modifié à l'aide du placeholder ?or.

Règles ORDER BY

Le tri ORDER BY peut être écrit à l'aide d'un tableau. Dans les clés, nous indiquons les colonnes et la valeur sera un booléen indiquant s'il faut trier par ordre croissant :

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

Insertion de données (INSERT)

Pour insérer des enregistrements, la commande SQL INSERT est utilisée.

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

La méthode getInsertId() renvoie l'ID de la dernière ligne insérée. Pour certaines bases de données (par exemple PostgreSQL), il est nécessaire de spécifier en paramètre le nom de la séquence à partir de laquelle l'ID doit être généré à l'aide de $database->getInsertId($sequenceId).

Nous pouvons également passer des valeurs-speciales comme paramètres, telles que des fichiers, des objets DateTime ou des types enum.

Insertion de plusieurs enregistrements à la fois :

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

L'INSERT multiple est beaucoup plus rapide car une seule requête de base de données est exécutée, au lieu de plusieurs requêtes individuelles.

Avertissement de sécurité : N'utilisez jamais de données non validées comme $values. Familiarisez-vous avec les risques possibles.

Mise à jour des données (UPDATE)

Pour mettre à jour des enregistrements, la commande SQL UPDATE est utilisée.

// Mise à jour d'un seul enregistrement
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Le nombre de lignes affectées est renvoyé par $result->getRowCount().

Pour UPDATE, nous pouvons utiliser les opérateurs += et -= :

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

Exemple d'insertion ou de modification d'un enregistrement s'il existe déjà. Nous utilisons la technique 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

Notez que Nette Database reconnaît le contexte dans lequel le paramètre avec le tableau est inséré dans la commande SQL et construit le code SQL en conséquence. Ainsi, à partir du premier tableau, il a construit (id, name, year) VALUES (123, 'Jim', 1978), tandis que le second a été converti sous la forme name = 'Jim', year = 1978. Nous en discutons plus en détail dans la section Conseils pour la construction SQL.

Suppression de données (DELETE)

Pour supprimer des enregistrements, la commande SQL DELETE est utilisée. Exemple d'obtention du nombre de lignes supprimées :

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

Conseils pour la construction SQL

Un hint est un placeholder spécial dans la requête SQL qui indique comment la valeur du paramètre doit être réécrite en expression SQL :

Hint	Description	S'applique automatiquement
`?name`	utilisé pour insérer le nom de la table ou de la colonne	–
`?values`	génère `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	génère l'affectation `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	joint les conditions du tableau avec l'opérateur `AND`	`WHERE ?`, `HAVING ?`
`?or`	joint les conditions du tableau avec l'opérateur `OR`	–
`?order`	génère la clause `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Pour insérer dynamiquement des noms de tables et de colonnes dans la requête, le placeholder ?name est utilisé. Nette Database s'occupe du traitement correct des identifiants selon les conventions de la base de données donnée (par exemple, en les entourant de backticks 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)

Avertissement : utilisez le symbole ?name uniquement pour les noms de tables et de colonnes provenant d'entrées validées, sinon vous vous exposez à un risque de sécurité.

Les autres hints n'ont généralement pas besoin d'être spécifiés, car Nette utilise une autodétection intelligente lors de la composition de la requête SQL (voir la troisième colonne du tableau). Mais vous pouvez l'utiliser, par exemple, dans une situation où vous souhaitez joindre des conditions à l'aide de OR au lieu 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'

Valeurs spéciales {#valeurs-speciales}

En plus des types scalaires courants (string, int, bool), vous pouvez également passer des valeurs spéciales comme paramètres :

fichiers : fopen('image.gif', 'r') insère le contenu binaire du fichier
date et heure : les objets DateTime sont convertis au format de la base de données
types enum : les instances enum sont converties en leur valeur
littéraux SQL : créés à l'aide de Connection::literal('NOW()') sont insérés directement dans la requête

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

Pour les bases de données qui n'ont pas de support natif pour le type de données datetime (comme SQLite et Oracle), DateTime est converti en une valeur spécifiée dans la configuration de la base de données par l'élément formatDateTime (la valeur par défaut est U – timestamp unix).

Littéraux SQL

Dans certains cas, vous devez spécifier directement du code SQL comme valeur, mais celui-ci ne doit pas être interprété comme une chaîne et échappé. C'est à cela que servent les objets de la classe Nette\Database\SqlLiteral. Ils sont créés par la méthode 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 alternativement :

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

Les littéraux SQL peuvent contenir des paramètres :

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

Grâce à cela, nous pouvons créer des combinaisons intéressantes :

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

Obtention des données {#Získání dat}

Raccourcis pour les requêtes SELECT

Pour simplifier la récupération des données, Connection propose plusieurs raccourcis qui combinent l'appel de query() avec le fetch*() suivant. Ces méthodes acceptent les mêmes paramètres que query(), c'est-à-dire la requête SQL et les paramètres optionnels. Une description complète des méthodes fetch*() se trouve ci-dessous.

`fetch($sql, ...$params): ?Row`	Exécute la requête et renvoie la première ligne comme objet `Row`
`fetchAll($sql, ...$params): array`	Exécute la requête et renvoie toutes les lignes comme tableau d'objets `Row`
`fetchPairs($sql, ...$params): array`	Exécute la requête et renvoie un tableau associatif, où la première colonne représente la clé et la seconde la valeur
`fetchField($sql, ...$params): mixed`	Exécute la requête et renvoie la valeur du premier champ de la première ligne
`fetchList($sql, ...$params): ?array`	Exécute la requête et renvoie la première ligne comme tableau indexé

Exemple :

// fetchField() - renvoie la valeur de la première cellule
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – itération sur les lignes

Après l'exécution de la requête, un objet ResultSet est renvoyé, permettant de parcourir les résultats de plusieurs manières. La manière la plus simple d'exécuter une requête et d'obtenir les lignes est d'itérer dans une boucle foreach. Cette méthode est la plus économe en mémoire car elle renvoie les données progressivement et ne les stocke pas toutes en mémoire en même temps.

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

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

ResultSet ne peut être itéré qu'une seule fois. Si vous avez besoin d'itérer plusieurs fois, vous devez d'abord charger les données dans un tableau, par exemple à l'aide de la méthode fetchAll().

fetch(): ?Row .{toc: fetch()}wiki-method {#fetch()}

Renvoie une ligne comme objet Row. S'il n'y a plus de lignes, renvoie null. Déplace le pointeur interne à la ligne suivante.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // lit la première ligne
if ($row) {
	echo $row->name;
}

fetchAll(): array

Renvoie toutes les lignes restantes du ResultSet sous forme de tableau d'objets Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // lit toutes les lignes
foreach ($rows as $row) {
	echo $row->name;
}

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

Renvoie les résultats sous forme de tableau associatif. Le premier argument spécifie le nom de la colonne à utiliser comme clé dans le tableau, le second argument spécifie le nom de la colonne à utiliser comme valeur :

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

Si nous ne spécifions que le premier paramètre, la valeur sera la ligne entière, c'est-à-dire l'objet Row :

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

En cas de clés dupliquées, la valeur de la dernière ligne est utilisée. En utilisant null comme clé, le tableau sera indexé numériquement à partir de zéro (il n'y a alors pas de collisions) :

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

fetchPairs (Closure $callback): array

Alternativement, vous pouvez spécifier un callback comme paramètre, qui renverra pour chaque ligne soit la valeur elle-même, soit une paire clé-valeur.

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

// Le callback peut également renvoyer un tableau avec une paire clé & valeur :
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Renvoie la valeur du premier champ de la ligne actuelle. S'il n'y a plus de lignes, renvoie null. Déplace le pointeur interne à la ligne suivante.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // lit le nom de la première ligne

fetchList(): ?array

Renvoie une ligne comme tableau indexé. S'il n'y a plus de lignes, renvoie null. Déplace le pointeur interne à la ligne suivante.

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

getRowCount(): ?int

Renvoie le nombre de lignes affectées par la dernière requête UPDATE ou DELETE. Pour SELECT, c'est le nombre de lignes renvoyées, mais celui-ci peut ne pas être connu – dans ce cas, la méthode renvoie null.

getColumnCount(): ?int

Renvoie le nombre de colonnes dans le ResultSet.

Informations sur les requêtes

À des fins de débogage, nous pouvons obtenir des informations sur la dernière requête exécutée :

echo $database->getLastQueryString();   // affiche la requête SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // affiche la requête SQL
echo $result->getTime();           // affiche le temps d'exécution en secondes

Pour afficher le résultat sous forme de tableau HTML, on peut utiliser :

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

ResultSet fournit des informations sur les types de colonnes :

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

foreach ($types as $column => $type) {
	echo "$column est de type $type->type"; // par ex. 'id est de type int'
}

Journalisation des requêtes

Nous pouvons implémenter notre propre journalisation des requêtes. L'événement onQuery est un tableau de callbacks qui sont appelés après chaque requête exécutée :

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