Approche SQL

Nette Database propose deux façons de travailler : vous pouvez écrire vous-même les requêtes SQL (approche SQL), ou les faire générer automatiquement (voir Explorer). L'approche SQL vous donne le contrôle total sur les requêtes tout en garantissant qu'elles sont construites de façon sûre.

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

Requêtes de base

La méthode query() sert à interroger la base de données. Elle renvoie un objet ResultSet, qui représente le résultat de la requête. Si la requête échoue, la méthode lève une exception. Vous pouvez parcourir le résultat de la requête par une boucle foreach, ou utiliser l'une des méthodes auxiliaires.

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

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

Pour insérer des valeurs dans les requêtes SQL en toute sécurité, utilisez des requêtes paramétrées. Nette Database rend cela extrêmement simple : 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 possibilités. Vous pouvez soit entrelacer la requête SQL et les paramètres :

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

Soit écrire d'abord toute la requête SQL, puis ajouter 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 d'une attaque appelée injection SQL, où un attaquant pourrait injecter ses propres commandes SQL et ainsi accéder aux données de la base ou les endommager.

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

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

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

Prenez connaissance des risques de sécurité possibles.

Techniques de requêtage

Conditions WHERE

Vous pouvez écrire les conditions WHERE sous forme de tableau associatif, où les clés sont les noms des colonnes et les valeurs les données à comparer. Nette Database choisit automatiquement l'opérateur SQL le plus adapté selon le type de la valeur.

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

Vous pouvez aussi indiquer 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 particuliers 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 NOT' => [],                 // ignoré
]);
// WHERE `name` != 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Par défaut, les conditions sont jointes par l'opérateur AND. Cela peut être changé à l'aide du placeholder ?or.

Règles ORDER BY

La clause ORDER BY peut s'écrire à l'aide d'un tableau. Indiquez les colonnes dans les clés et utilisez une valeur booléenne pour préciser l'ordre croissant (true) ou décroissant (false) :

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

Insérer des données (INSERT)

La commande SQL INSERT sert à insérer des enregistrements.

$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 faut indiquer en paramètre le nom de la séquence depuis laquelle l'ID doit être généré, avec $database->getInsertId($sequenceId).

Vous pouvez aussi passer comme paramètres des Valeurs spéciales, par exemple des fichiers, des objets DateTime ou des types enum.

Insertion de plusieurs enregistrements d'un coup :

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

Un INSERT multiple est bien plus rapide, car une seule requête est exécutée au lieu de nombreuses requêtes individuelles.

Note de sécurité : n'utilisez jamais de données non validées comme $values. Prenez connaissance des risques possibles.

Mettre à jour des données (UPDATE)

La commande SQL UPDATE sert à mettre à jour des enregistrements.

// Met à jour 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émente login_count
], 1);

Exemple d'insertion, ou de mise à jour 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

Remarquez que Nette Database reconnaît le contexte dans lequel un paramètre tableau est utilisé au sein de la commande SQL et construit le code SQL en conséquence. Du premier tableau, il a donc construit (id, name, year) VALUES (123, 'Jim', 1978), tandis qu'il a converti le second en name = 'Jim', year = 1978. Nous en parlons plus en détail dans la section Indications de construction SQL.

Supprimer des données (DELETE)

La commande SQL DELETE sert à supprimer des enregistrements. Exemple d'obtention du nombre de lignes supprimées :

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

Indications de construction SQL

Une indication est un placeholder particulier dans une requête SQL, qui précise comment la valeur du paramètre doit être convertie en expression SQL :

Indication	Description	Utilisée automatiquement pour
`?name`	Sert à insérer des noms de tables ou de colonnes	–
`?values`	Génère `(clé, ...) VALUES (valeur, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Génère des affectations `clé = valeur, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Joint les conditions d'un tableau par `AND`	`WHERE ?`, `HAVING ?`
`?or`	Joint les conditions d'un tableau par `OR`	–
`?order`	Génère la clause `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Le placeholder ?name sert à insérer dynamiquement des noms de tables et de colonnes dans la requête. Nette Database se charge de la mise entre quotes correcte des identifiants selon les conventions de la base (par exemple entre backticks dans MySQL).

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

Attention : n'utilisez le placeholder ?name que pour des noms de tables et de colonnes validés. Sinon, vous vous exposez à des failles de sécurité.

Les autres indications n'ont généralement pas besoin d'être précisées, car Nette utilise une détection automatique intelligente lors de la construction de la requête SQL (voir la troisième colonne du tableau). Vous pouvez cependant vous en servir, par exemple lorsque vous voulez joindre les conditions par OR au lieu d'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

Outre les types scalaires courants (string, int, bool), vous pouvez aussi passer comme paramètres des valeurs particulières :

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

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

Pour les bases de données qui n'ont pas de prise en charge native du type datetime (comme SQLite et Oracle), les objets DateTime et DateTimeImmutable sont convertis vers une valeur définie dans la configuration de la base de données par l'entrée formatDateTime (la valeur par défaut est U, le timestamp Unix).

Littéraux SQL

Dans certains cas, vous devez passer comme valeur du code SQL brut, qui ne doit pas être traité comme une chaîne ni échappé. Les objets de la classe Nette\Database\SqlLiteral servent à cela. 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 bien :

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

Cela permet 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')

Récupérer les données

Raccourcis pour les requêtes SELECT

Pour simplifier la récupération des données, Connection offre plusieurs raccourcis qui combinent un appel à query() et un appel fetch*() qui suit. Ces méthodes acceptent les mêmes paramètres que query(), c'est-à-dire une requête SQL et des paramètres facultatifs. La description complète des méthodes fetch*() se trouve plus bas.

`fetch($sql, ...$params): ?Row`	Exécute la requête et renvoie la première ligne comme objet `Row`, ou `null`.
`fetchAll($sql, ...$params): array`	Exécute la requête et renvoie toutes les lignes sous forme de tableau d'objets `Row`.
`fetchPairs($sql, ...$params): array`	Exécute la requête et renvoie un tableau associatif (paires clé ⇒ valeur).
`fetchField($sql, ...$params): mixed`	Exécute la requête et renvoie la valeur de la première colonne de la première ligne.
`fetchList($sql, ...$params): ?array`	Exécute la requête et renvoie la première ligne comme tableau indexé, ou `null`.

Exemple :

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

`foreach` – parcourir les lignes

Après l'exécution d'une requête, un objet ResultSet est renvoyé, qui permet de parcourir les résultats de plusieurs façons. La plus simple pour exécuter une requête et récupérer les lignes est de les parcourir dans une boucle foreach. C'est la méthode la plus économe en mémoire, car elle récupère les données ligne par ligne et ne charge pas tout le jeu de résultats en mémoire d'un coup.

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

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

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

fetch(): ?Row

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

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // charge 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(); // charge toutes les lignes
foreach ($rows as $row) {
	echo $row->name;
}

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

Renvoie le jeu de résultats sous forme de tableau associatif. Le premier argument indique la colonne à utiliser comme clés, le second la colonne à utiliser comme valeurs :

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

Si seul le premier paramètre ($key) est fourni, c'est la ligne entière (objet Row) qui sera utilisée comme valeur :

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

En cas de clés en double, c'est la valeur de la dernière ligne qui est utilisée. Utiliser null comme clé donne un tableau indexé numériquement (à partir de zéro), ce qui évite les collisions de clés :

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

fetchPairs (Closure $callback): array

Vous pouvez aussi fournir un callback qui traite chaque ligne. Le callback peut renvoyer une seule valeur ou 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 aussi renvoyer un tableau formant une paire clé & valeur :
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Renvoie la valeur de la première colonne de la ligne courante. S'il n'y a plus de lignes, renvoie null. Déplace le pointeur interne sur la ligne suivante.

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

fetchList(): ?array

Renvoie la ligne sous forme de tableau indexé. S'il n'y a plus de lignes, renvoie null. Déplace le pointeur interne sur 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 les requêtes SELECT, renvoie le nombre de lignes du jeu de résultats. Celui-ci n'est cependant pas toujours connu, auquel cas la méthode renvoie null.

getColumnCount(): ?int

Renvoie le nombre de colonnes du ResultSet.

Informations sur la requête

À 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, vous pouvez utiliser :

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

Le ResultSet fournit des informations sur les types des colonnes :

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

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

Journalisation des requêtes

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

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Requête : ' . $result->getQueryString());
	$logger->info('Temps : ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Jeu de résultats volumineux : ' . $result->getRowCount() . ' lignes');
	}
};