Approccio SQL

Nette Database offre due modi di lavorare: potete scrivere le query SQL da soli (approccio SQL), oppure farle generare automaticamente (vedi Explorer). L'approccio SQL vi dà il pieno controllo sulle query e allo stesso tempo garantisce che vengano costruite in sicurezza.

I dettagli sulla connessione e la configurazione del database li trovate nel capitolo Connessione e configurazione.

Interrogazione di base

Per interrogare il database serve il metodo query(). Restituisce un oggetto ResultSet, che rappresenta il risultato della query. Se la query fallisce, il metodo lancia un'eccezione. Il risultato della query si può percorrere con un ciclo foreach oppure usare uno dei metodi di supporto.

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

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

Per inserire in sicurezza i valori nelle query SQL usate le query parametrizzate. Nette Database lo rende estremamente semplice: basta aggiungere dopo la query SQL una virgola e il valore:

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

Con più parametri avete due possibilità. Potete alternare la query SQL e i parametri:

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

Oppure scrivere prima tutta la query SQL e poi accodare tutti i parametri:

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

Protezione contro la SQL injection

Perché è importante usare query parametrizzate? Perché vi proteggono dall'attacco chiamato SQL injection, con cui un attaccante potrebbe inserire comandi SQL propri e ottenere così l'accesso ai dati del database o danneggiarli.

Non inserite mai le variabili direttamente nella query SQL! Usate sempre query parametrizzate, che vi proteggono dalla SQL injection.

// ❌ CODICE PERICOLOSO - vulnerabile a SQL injection
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Prendete confidenza con i possibili rischi di sicurezza.

Tecniche di interrogazione

Condizioni WHERE

Le condizioni WHERE si possono scrivere come array associativo, dove le chiavi sono i nomi delle colonne e i valori i dati da confrontare. Nette Database sceglie automaticamente l'operatore SQL più adatto in base al tipo del valore.

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

Nella chiave potete anche indicare esplicitamente l'operatore di confronto:

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

Nette gestisce automaticamente i casi particolari come i valori null o gli array.

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // usa l'operatore =
	'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

Per le condizioni negative usate l'operatore NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // usa l'operatore !=
	'category_id NOT' => [1, 2, 3], // usa NOT IN
	'description NOT' => null,      // usa IS NOT NULL
	'id NOT' => [],                 // viene saltato
]);
// WHERE `name` != 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Per impostazione predefinita le condizioni sono unite dall'operatore AND. Lo si può cambiare con il segnaposto ?or.

Regole ORDER BY

La clausola ORDER BY si può scrivere con un array. Nelle chiavi indicate le colonne e il valore booleano determina l'ordine crescente (true) o decrescente (false):

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

Inserimento dei dati (INSERT)

Per inserire i record serve il comando SQL INSERT.

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

Il metodo getInsertId() restituisce l'ID dell'ultima riga inserita. In alcuni database (per esempio PostgreSQL) bisogna indicare come parametro il nome della sequenza da cui generare l'ID, con $database->getInsertId($sequenceId).

Come parametri si possono passare anche Valori speciali, per esempio file, oggetti DateTime o tipi enum.

Inserimento di più record in una volta:

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

L'INSERT multiplo è molto più veloce, perché viene eseguita una sola query al database invece di tante singole.

Nota di sicurezza: non usate mai dati non validati come $values. Prendete confidenza con i possibili rischi.

Aggiornamento dei dati (UPDATE)

Per aggiornare i record serve il comando SQL UPDATE.

// aggiornamento di un singolo record
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Il numero di righe interessate lo restituisce $result->getRowCount().

Per l'UPDATE possiamo usare gli operatori += e -=:

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

Esempio di inserimento o aggiornamento di un record se esiste già. Usiamo la tecnica 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

Notate che Nette Database riconosce il contesto in cui il parametro array viene usato nel comando SQL e costruisce di conseguenza il codice SQL. Dal primo array ha quindi costruito (id, name, year) VALUES (123, 'Jim', 1978), mentre il secondo lo ha convertito nella forma name = 'Jim', year = 1978. Ne parliamo più in dettaglio nella sezione Suggerimenti per la costruzione dell'SQL.

Cancellazione dei dati (DELETE)

Per cancellare i record serve il comando SQL DELETE. Esempio con l'ottenimento del numero di righe cancellate:

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

Suggerimenti per la costruzione dell'SQL

Un suggerimento è un segnaposto particolare nella query SQL che determina come il valore del parametro debba essere convertito in un'espressione SQL:

Suggerimento	Descrizione	Usato automaticamente per
`?name`	Serve a inserire nomi di tabelle o colonne	–
`?values`	Genera `(chiave, ...) VALUES (valore, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Genera le assegnazioni `chiave = valore, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Unisce le condizioni dell'array con `AND`	`WHERE ?`, `HAVING ?`
`?or`	Unisce le condizioni dell'array con `OR`	–
`?order`	Genera la clausola `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Il segnaposto ?name serve a inserire dinamicamente nella query i nomi di tabelle e colonne. Nette Database si occupa di quotare correttamente gli identificatori secondo le convenzioni del database (per esempio racchiudendoli tra backtick in MySQL).

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

Attenzione: usate il segnaposto ?name solo per nomi di tabelle e colonne validati. Altrimenti vi esponete a vulnerabilità di sicurezza.

Gli altri suggerimenti di solito non serve indicarli, perché Nette usa un'autodetezione intelligente quando costruisce la query SQL (vedi la terza colonna della tabella). Potete però usarli per esempio nel caso in cui vogliate unire le condizioni con OR invece che con 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'

Valori speciali

Oltre ai consueti tipi scalari (string, int, bool) potete passare come parametri anche valori speciali:

file: fopen('image.gif', 'r') inserisce il contenuto binario del file
data e ora: gli oggetti DateTimeInterface vengono convertiti nel formato del database
tipi enum: le istanze di enum vengono convertite nel loro valore
letterali SQL: creati con Connection::literal('NOW()') vengono inseriti direttamente nella query

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

Nei database che non hanno supporto nativo per il tipo di dato datetime (come SQLite e Oracle), gli oggetti DateTime e DateTimeImmutable vengono convertiti nel valore indicato nella configurazione del database dalla voce formatDateTime (il valore predefinito è U, cioè il timestamp Unix).

Letterali SQL

In alcuni casi bisogna passare come valore del codice SQL grezzo, che non va trattato come stringa né sottoposto a escaping. A questo servono gli oggetti della classe Nette\Database\SqlLiteral. Si creano con il metodo Connection::literal().

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

Oppure:

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

I letterali SQL possono contenere parametri:

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

Il che permette combinazioni interessanti:

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

Ottenere i dati

Scorciatoie per le query SELECT

Per semplificare l'ottenimento dei dati, Connection offre alcune scorciatoie che combinano la chiamata a query() con la successiva chiamata a fetch*(). Questi metodi accettano gli stessi parametri di query(), cioè la query SQL ed eventuali parametri. La descrizione completa dei metodi fetch*() la trovate più sotto.

`fetch($sql, ...$params): ?Row`	Esegue la query e restituisce la prima riga come oggetto `Row` oppure `null`.
`fetchAll($sql, ...$params): array`	Esegue la query e restituisce tutte le righe come array di oggetti `Row`.
`fetchPairs($sql, ...$params): array`	Esegue la query e restituisce un array associativo (coppie chiave ⇒ valore).
`fetchField($sql, ...$params): mixed`	Esegue la query e restituisce il valore della prima colonna della prima riga.
`fetchList($sql, ...$params): ?array`	Esegue la query e restituisce la prima riga come array indicizzato oppure `null`.

Esempio:

// fetchField() - restituisce il valore della prima cella
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – iterazione sulle righe

Dopo l'esecuzione della query viene restituito un oggetto ResultSet, che permette di percorrere i risultati in vari modi. Il modo più semplice di eseguire una query e ottenere le righe è iterare con un ciclo foreach. Questo metodo è il più parsimonioso in termini di memoria, perché carica i dati riga per riga e non tiene in memoria tutto il risultato in una volta.

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

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

Il ResultSet si può percorrere una sola volta. Se avete bisogno di iterare ripetutamente, dovete prima caricare i dati in un array, per esempio con il metodo fetchAll().

fetch(): ?Row

Restituisce una riga come oggetto Row. Se non esistono altre righe, restituisce null. Sposta il puntatore interno alla riga successiva.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // carica la prima riga
if ($row) {
	echo $row->name;
}

fetchAll(): array

Restituisce tutte le righe rimanenti del ResultSet come array di oggetti Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // carica tutte le righe
foreach ($rows as $row) {
	echo $row->name;
}

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

Restituisce i risultati come array associativo. Il primo argomento indica la colonna da usare come chiave, il secondo la colonna da usare come valore:

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

Se viene indicato solo il primo parametro ($key), come valore verrà usata l'intera riga (l'oggetto Row):

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

In caso di chiavi duplicate viene usato il valore dell'ultima riga. Usando null come chiave si ottiene un array indicizzato numericamente (a partire da zero), il che evita le collisioni di chiavi:

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

fetchPairs (Closure $callback): array

In alternativa potete indicare un callback che elabora ogni riga. Il callback può restituire un singolo valore oppure una coppia chiave-valore.

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

// il callback può anche restituire un array con la coppia chiave e valore:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Restituisce il valore della prima colonna della riga corrente. Se non esistono altre righe, restituisce null. Sposta il puntatore interno alla riga successiva.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // carica il nome dalla prima riga

fetchList(): ?array

Restituisce la riga come array indicizzato. Se non esistono altre righe, restituisce null. Sposta il puntatore interno alla riga successiva.

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

getRowCount(): ?int

Restituisce il numero di righe interessate dall'ultima query UPDATE o DELETE. Per le query SELECT restituisce il numero di righe del risultato. Questo però non è sempre noto, e in tal caso il metodo restituisce null.

getColumnCount(): ?int

Restituisce il numero di colonne del ResultSet.

Informazioni sulle query

Per il debugging possiamo ottenere informazioni sull'ultima query eseguita:

echo $database->getLastQueryString();   // stampa la query SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // stampa la query SQL
echo $result->getTime();           // stampa il tempo di esecuzione in secondi

Per mostrare il risultato come tabella HTML potete usare:

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

Il ResultSet offre informazioni sui tipi delle colonne:

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

foreach ($types as $column => $type) {
	echo "$column è di tipo $type"; // per esempio 'id è di tipo int'
}

Logging delle query

Possiamo realizzare un logging personalizzato delle query. L'evento onQuery è un array di callback richiamati dopo ogni query eseguita:

$database->onQuery[] = function ($database, $result) use ($logger) {
	$logger->info('Query: ' . $result->getQueryString());
	$logger->info('Tempo: ' . $result->getTime());

	if ($result->getRowCount() > 1000) {
		$logger->warning('Risultato di grandi dimensioni: ' . $result->getRowCount() . ' righe');
	}
};