Direct SQL

Vous pouvez travailler avec Nette Database de deux manières : en écrivant des requêtes SQL directement (Direct Access) ou en laissant SQL être généré automatiquement(Explorer Access). L'accès direct vous permet de construire des requêtes en toute sécurité tout en gardant un contrôle total sur leur structure.

Pour plus d'informations sur la création d'une connexion et sa configuration, voir la page séparée.

Interrogation de base

La méthode query() exécute des requêtes de base de données et renvoie un objet ResultSet représentant le résultat. Si la requête échoue, la méthode lève une exception. Vous pouvez parcourir le résultat de la requête en boucle à 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 en toute sécurité des valeurs dans les requêtes SQL, utilisez des requêtes paramétrées. Nette Database rend cette opération très simple : il suffit d'ajouter une virgule et la valeur à la requête SQL.

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

Pour les paramètres multiples, vous pouvez soit intercaler la requête SQL avec les paramètres :

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

soit écrire d'abord la requête SQL complète, 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 contre les attaques par injection SQL, où les attaquants peuvent injecter des commandes SQL malveillantes pour manipuler ou accéder aux données de la base de données.

N'insérez jamais de variables directement dans une requête SQL Utilisez toujours des requêtes paramétrées pour vous protéger contre les injections SQL.

// ❌ UNSAFE CODE - 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);

Veillez à vous familiariser avec les risques de sécurité potentiels.

Techniques d'interrogation

Conditions WHERE

Vous pouvez écrire les conditions WHERE sous la forme d'un tableau associatif, où les clés sont des noms de colonnes et les valeurs sont les données à comparer. Nette Database sélectionne 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 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%'

Les cas particuliers tels que les valeurs null ou les tableaux sont traités automatiquement :

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

Par défaut, les conditions sont combinées à l'aide de l'opérateur AND. Vous pouvez modifier ce comportement à l'aide de l'espace réservé ?or.

Règles ORDER BY

La clause ORDER BY peut être définie comme un tableau, où les clés représentent les colonnes et les valeurs sont des booléens indiquant l'ordre croissant :

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

Insérer des données (INSERT)

Pour insérer des enregistrements, utilisez l'instruction SQL INSERT.

$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), vous devez spécifier le nom de la séquence en utilisant $database->getInsertId($sequenceId).

Vous pouvez également transmettre des valeurs spéciales, telles que des fichiers, des objets DateTime ou des types d'énumération, en tant que paramètres.

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'insertion par lots est beaucoup plus rapide car une seule requête est exécutée au lieu de plusieurs.

Note de sécurité: N'utilisez jamais de données non validées en tant que $values. Familiarisez-vous avec les risques possibles.

Mise à jour des données (UPDATE)

Pour mettre à jour des enregistrements, utilisez l'instruction SQL UPDATE.

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

Vous pouvez vérifier le nombre de lignes affectées en utilisant $result->getRowCount().

Vous pouvez utiliser les opérateurs += et -= dans UPDATE:

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

Pour insérer ou mettre à jour un enregistrement s'il existe déjà, utilisez 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 de la commande SQL dans lequel un paramètre avec un tableau est utilisé et génère le code SQL en conséquence. Par exemple, il a construit (id, name, year) VALUES (123, 'Jim', 1978) à partir du premier tableau, tandis qu'il a converti le second en name = 'Jim', year = 1978. Ce point est abordé plus en détail dans la section Conseils pour la construction du code SQL.

Suppression de données (DELETE)

Pour supprimer des enregistrements, utilisez l'instruction SQL DELETE. Exemple avec le nombre de lignes supprimées :

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

Conseils de construction SQL

Les caractères de remplissage SQL permettent de contrôler la façon dont les valeurs des paramètres sont incorporées dans les expressions SQL :

Les caractères de remplacement SQL permettent de contrôler la façon dont les valeurs des paramètres sont incorporées dans les expressions SQL.
`?name`	Utilisé pour les noms de table ou de colonne
`?values`	Génère `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Génère les affectations `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Joint les conditions dans un tableau avec `AND`	`WHERE ?`, `HAVING ?`
`?or`	Joint les conditions d'un tableau avec `OR`	– -.
`?order`	Génère la clause `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Pour insérer dynamiquement des noms de tables ou de colonnes, utilisez l'espace réservé ?name. Nette Database assure un échappement correct selon les conventions de la base de données (par exemple, en enfermant dans des crochets pour MySQL).

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

Avertissement: N'utilisez l'espace réservé ?name que pour les noms de tables et de colonnes validés. Dans le cas contraire, vous risquez des failles de sécurité.

Il n'est généralement pas nécessaire de spécifier d'autres indices, car Nette utilise une auto-détection intelligente lors de la construction des requêtes SQL (voir la troisième colonne du tableau). Toutefois, vous pouvez les utiliser dans les situations où vous souhaitez combiner des conditions en utilisant 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

Outre les types scalaires standard (par exemple, string, int, bool), vous pouvez également transmettre des valeurs spéciales en tant que paramètres :

Fichiers : Utilisez fopen('file.png', 'r') pour insérer le contenu binaire d'un fichier.
Date et heure : les objets DateTime sont automatiquement convertis au format de date de la base de données.
Valeurs d'énumération : Les instances de enum sont converties en valeurs correspondantes.
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 DateTime,
	'content' => fopen('image.png', 'r'),
	'state' => Status::Draft,
]);

Pour les bases de données qui ne prennent pas en charge le type datetime (par exemple, SQLite et Oracle), les valeurs DateTime sont converties conformément à l'option de configuration formatDateTime (par défaut : U pour les horodatages Unix).

Litres SQL

Dans certains cas, vous pouvez avoir besoin d'insérer du code SQL brut comme valeur sans le traiter comme une chaîne de caractères ou l'escamoter. Pour cela, utilisez des objets de la classe Nette\Database\SqlLiteral, qui peuvent être créés à l'aide de 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())

Autre solution :

$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 également contenir des paramètres :

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

Cela permet des combinaisons flexibles :

$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ération de données

Raccourcis pour les requêtes SELECT

Pour simplifier la recherche de données, la classe Connection fournit plusieurs raccourcis qui combinent un appel à query() avec un appel ultérieur à fetch*(). Ces méthodes acceptent les mêmes paramètres que query(), c'est-à-dire une requête SQL et des paramètres facultatifs. Une description détaillée des méthodes fetch*() se trouve ci-dessous.

`fetch($sql, ...$params): ?Row`	Exécute la requête et récupère la première ligne sous la forme d'un objet `Row`.
`fetchAll($sql, ...$params): array`	Exécute la requête et récupère toutes les lignes sous la forme d'un tableau d'objets `Row`.
`fetchPairs($sql, ...$params): array`	Exécute la requête et récupère un tableau associatif dont la première colonne est la clé et la seconde la valeur.
`fetchField($sql, ...$params): mixed`	Exécute la requête et récupère la valeur de la première cellule de la première ligne.
`fetchList($sql, ...$params): ?array`	Exécute la requête et récupère la première ligne sous forme de 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 d'une requête, un objet ResultSet est renvoyé, ce qui vous permet d'itérer sur les résultats de différentes manières. La méthode la plus simple et la plus efficace en termes de mémoire pour récupérer des lignes consiste à itérer dans une boucle foreach. Cette méthode traite les lignes une par une et évite de stocker toutes les données en mémoire en même temps.

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

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

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

fetch(): ?Row

Exécute la requête et récupère une seule ligne sous la forme d'un objet Row. S'il n'y a plus de lignes disponibles, elle renvoie null. Cette méthode fait passer le pointeur interne à la ligne suivante.

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

fetchAll(): array

Récupère toutes les lignes restantes de ResultSet sous la forme d'un tableau d'objets Row.

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

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

Récupère les résultats sous la forme d'un tableau associatif. Le premier argument spécifie la colonne à utiliser comme clé, et le second spécifie 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 seul le premier paramètre est fourni, la valeur sera la ligne entière (sous la forme d'un objet Row ) :

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

Si null est fourni comme clé, le tableau sera indexé numériquement à partir de zéro :

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

fetchPairs (Closure $callback): array

Vous pouvez également fournir un rappel qui détermine les paires clé-valeur ou les valeurs pour chaque ligne.

$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

Récupère la valeur de la première cellule de la ligne actuelle. S'il n'y a plus de lignes disponibles, elle renvoie null. Cette méthode fait passer le pointeur interne à la ligne suivante.

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

fetchList(): ?array

Récupère la ligne sous la forme d'un tableau indexé. S'il n'y a plus de lignes disponibles, elle renvoie null. Cette méthode fait passer 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 les requêtes SELECT, il renvoie le nombre de lignes récupérées, mais ce nombre n'est pas toujours connu – dans ce cas, il renvoie null.

getColumnCount(): ?int

Renvoie le nombre de colonnes dans ResultSet.

Informations sur les requêtes

Pour obtenir des détails sur la dernière requête exécutée, utilisez :

echo $database->getLastQueryString(); // sort 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, utilisez :

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

Vous pouvez également obtenir des informations sur les types de colonnes à partir du site ResultSet:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // par exemple, "id is of type int" (l'identifiant est de type int)
}

Enregistrement des requêtes

Vous pouvez mettre en œuvre une journalisation personnalisée des requêtes. L'événement onQuery est un tableau de rappels qui sont invoqués après chaque exécution de requête :

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