Direct SQL
**********

.[perex]
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 | explorer]). 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.

.[note]
Pour plus d'informations sur la création d'une connexion et sa configuration, voir la [page séparée | guide#Connection and Configuration].


Interrogation de base .[#toc-basic-querying]
============================================

La méthode `query()` exécute des requêtes de base de données et renvoie un objet [ResultSet | api:Nette\Database\ResultSet] représentant le résultat. Si la requête échoue, la méthode [lève une exception | exceptions].
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 | #Fetching Data].

```php
$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.

```php
$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 :

```php
$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 :

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


Protection contre l'injection SQL .[#toc-protection-against-sql-injection]
==========================================================================

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.

.[warning]
**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.

```php
// ❌ 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 | security].


Techniques d'interrogation .[#toc-query-techniques]
===================================================


Conditions WHERE .[#toc-where-conditions]
-----------------------------------------

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.

```php
$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é :

```php
$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 :

```php
$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`:

```php
$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 | #SQL Construction Hints].


Règles ORDER BY .[#toc-order-by-rules]
--------------------------------------

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 :

```php
$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) .[#toc-inserting-data-insert]
----------------------------------------------------------

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

```php
$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 |#special values], telles que des fichiers, des objets DateTime ou des types d'énumération, en tant que paramètres.

Insertion de plusieurs enregistrements à la fois :

```php
$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 | security#Array Keys are Not Secure API].


Mise à jour des données (UPDATE) .[#toc-updating-data-update]
-------------------------------------------------------------

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

```php
// 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`:

```php
$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`:

```php
$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 |#SQL Construction Hints].


Suppression de données (DELETE) .[#toc-deleting-data-delete]
------------------------------------------------------------

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

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


Conseils de construction SQL .[#toc-sql-construction-hints]
-----------------------------------------------------------

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

```php
$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é | security#Dynamic Identifiers].

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`:

```php
$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 .[#toc-special-values]
----------------------------------------

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.

```php
$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 .[#toc-sql-literals]
-------------------------------

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()`.

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

Autre solution :

```php
$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 :

```php
$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 :

```php
$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 .[#toc-fetching-data]
=============================================


Raccourcis pour les requêtes SELECT .[#toc-shortcuts-for-select-queries]
------------------------------------------------------------------------

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()].

| `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 :

```php
// 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 |api:Nette\Database\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.

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

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

.[note]
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 .[method]
-----------------------

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.

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


fetchAll(): array .[method]
---------------------------

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

```php
$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 .[method]
---------------------------------------------------------------------------------------

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 :

```php
$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` ) :

```php
$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 :

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


fetchPairs(Closure $callback): array .[method]
----------------------------------------------

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

```php
$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 .[method]
-----------------------------

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.

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


fetchList(): ?array .[method]
-----------------------------

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.

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


getRowCount(): ?int .[method]
-----------------------------

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 .[method]
--------------------------------

Renvoie le nombre de colonnes dans `ResultSet`.


Informations sur les requêtes .[#toc-query-information]
=======================================================

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

```php
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 :

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

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

```php
$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 .[#toc-query-logging]
-------------------------------------------------

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 :

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