Accesso SQL

Nette Database offre due approcci: puoi scrivere query SQL da solo (accesso SQL), oppure puoi farle generare automaticamente (vedi Explorer). L'accesso SQL ti dà il pieno controllo sulle query, garantendo al contempo la loro costruzione sicura.

I dettagli sulla connessione e la configurazione del database si trovano nel capitolo Connessione e configurazione.

Query di base

Per interrogare il database, si usa il metodo query(). Questo restituisce un oggetto ResultSet, che rappresenta il risultato della query. In caso di fallimento, il metodo lancia un'eccezione. Possiamo scorrere il risultato della query usando un ciclo foreach, oppure usare una delle funzioni ausiliarie.

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

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

Per inserire in modo sicuro i valori nelle query SQL, usiamo query parametrizzate. Nette Database le rende estremamente semplici: basta aggiungere una virgola e il valore dopo la query SQL:

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

Con più parametri, hai due opzioni di scrittura. Puoi “intervallare” la query SQL con i parametri:

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

Oppure scrivere prima l'intera query SQL e poi aggiungere tutti i parametri:

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

Protezione contro SQL injection

Perché è importante usare query parametrizzate? Perché ti proteggono da un attacco chiamato SQL injection, in cui un attaccante potrebbe inserire i propri comandi SQL e quindi ottenere o danneggiare i dati nel database.

Non inserire mai variabili direttamente nella query SQL! Usa sempre query parametrizzate, che ti proteggono da 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);

Familiarizza con i possibili rischi per la sicurezza.

Tecniche di query

Condizioni WHERE

Puoi scrivere le condizioni WHERE come un array associativo, dove le chiavi sono i nomi delle colonne e i valori sono i dati per il confronto. Nette Database seleziona automaticamente l'operatore SQL più appropriato in base al tipo di valore.

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

Nella chiave puoi anche specificare esplicitamente l'operatore per il 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 casi speciali come valori null o 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, usa 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' => [],                     // viene omesso
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Per unire le condizioni si usa l'operatore AND. Questo può essere cambiato usando il segnaposto ?or.

Regole ORDER BY

L'ordinamento ORDER BY può essere scritto usando un array. Nelle chiavi indichiamo le colonne e il valore sarà un booleano che determina se ordinare in modo ascendente:

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

Inserimento dati (INSERT)

Per inserire record si usa l'istruzione 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. Per alcuni database (ad es. PostgreSQL), è necessario specificare come parametro il nome della sequenza da cui generare l'ID tramite $database->getInsertId($sequenceId).

Come parametri possiamo passare anche #valori speciali come file, oggetti DateTime o tipi enum.

Inserimento di più record contemporaneamente:

$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 singola query al database, invece di molte query individuali.

Avviso di sicurezza: Non usare mai dati non validati come $values. Familiarizza con i possibili rischi.

Aggiornamento dati (UPDATE)

Per aggiornare i record si usa l'istruzione 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 viene restituito da $result->getRowCount().

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

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

Esempio di inserimento o modifica 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

Nota che Nette Database riconosce in quale contesto dell'istruzione SQL viene inserito il parametro con l'array e costruisce il codice SQL di conseguenza. Quindi dal primo array ha costruito (id, name, year) VALUES (123, 'Jim', 1978), mentre il secondo lo ha convertito nella forma name = 'Jim', year = 1978. Ne parliamo più dettagliatamente nella sezione Hint per la costruzione di SQL.

Cancellazione dati (DELETE)

Per cancellare i record si usa l'istruzione SQL DELETE. Esempio con ottenimento del numero di righe cancellate:

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

Hint per la costruzione di SQL

Un hint è un segnaposto speciale nella query SQL che indica come il valore del parametro deve essere riscritto nell'espressione SQL:

Hint	Descrizione	Utilizzato automaticamente
`?name`	usa per inserire il nome della tabella o della colonna	–
`?values`	genera `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	genera l'assegnazione `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	unisce le condizioni nell'array con l'operatore `AND`	`WHERE ?`, `HAVING ?`
`?or`	unisce le condizioni nell'array con l'operatore `OR`	–
`?order`	genera la clausola `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Per l'inserimento dinamico di nomi di tabelle e colonne nella query, si usa il segnaposto ?name. Nette Database si occupa della corretta gestione degli identificatori secondo le convenzioni del database specifico (ad es. 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)

Avviso: usa il simbolo ?name solo per nomi di tabelle e colonne provenienti da input validati, altrimenti ti esponi a un rischio per la sicurezza.

Gli altri hint di solito non devono essere specificati, poiché Nette utilizza un'intelligente rilevazione automatica durante la composizione della query SQL (vedi la terza colonna della tabella). Ma puoi usarlo, ad esempio, in una situazione in cui vuoi unire le condizioni usando OR invece di 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 comuni tipi scalari (string, int, bool), puoi passare valori speciali come parametri:

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

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

Per i database che non hanno supporto nativo per il tipo di dati datetime (come SQLite e Oracle), DateTime viene convertito nel valore specificato nella configurazione del database tramite la voce formatDateTime (il valore predefinito è U – timestamp unix).

Letterali SQL

In alcuni casi, è necessario specificare direttamente il codice SQL come valore, che però non deve essere interpretato come stringa ed escapato. A questo servono gli oggetti della classe Nette\Database\SqlLiteral. Li crea 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())

O alternativamente:

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

Grazie a ciò possiamo creare 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')

Recupero dati

Scorciatoie per query SELECT

Per semplificare il recupero dei dati, Connection offre diverse scorciatoie che combinano la chiamata query() con il successivo fetch*(). Questi metodi accettano gli stessi parametri di query(), ovvero la query SQL e parametri opzionali. Una descrizione completa dei metodi fetch*() si trova sotto.

`fetch($sql, ...$params): ?Row`	Esegue la query e restituisce la prima riga come oggetto `Row`
`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, dove la prima colonna rappresenta la chiave e la seconda il valore
`fetchField($sql, ...$params): mixed`	Esegue la query e restituisce il valore del primo campo della prima riga
`fetchList($sql, ...$params): ?array`	Esegue la query e restituisce la prima riga come array indicizzato

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 consente di scorrere i risultati in diversi modi. Il modo più semplice per eseguire una query e ottenere le righe è iterando in un ciclo foreach. Questo metodo è il più efficiente in termini di memoria, poiché restituisce i dati gradualmente e non li memorizza tutti in memoria contemporaneamente.

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

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

ResultSet può essere iterato solo una volta. Se è necessario iterare ripetutamente, è necessario prima caricare i dati in un array, ad esempio utilizzando il metodo fetchAll().

fetch(): ?Row

Restituisce una riga come oggetto Row. Se non ci sono più 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 dal ResultSet come un 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 un array associativo. Il primo argomento specifica il nome della colonna da utilizzare come chiave nell'array, il secondo argomento specifica il nome della colonna da utilizzare come valore:

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

Se specifichiamo solo il primo parametro, il valore sarà l'intera riga, ovvero un oggetto Row:

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

In caso di chiavi duplicate, viene utilizzato il valore dell'ultima riga. Utilizzando null come chiave, l'array sarà indicizzato numericamente a partire da zero (quindi non si verificano collisioni):

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

fetchPairs (Closure $callback): array

In alternativa, puoi specificare come parametro un callback, che per ogni riga restituirà o il valore stesso, o 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 una coppia chiave & valore:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Restituisce il valore del primo campo della riga corrente. Se non ci sono più 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 una riga come array indicizzato. Se non ci sono più 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 SELECT, è il numero di righe restituite, ma questo potrebbe non essere noto – in tal caso il metodo restituirà null.

getColumnCount(): ?int

Restituisce il numero di colonne nel ResultSet.

Informazioni sulle query

A scopo di 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 visualizzare il risultato come tabella HTML, si può usare:

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

ResultSet offre informazioni sui tipi di colonna:

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

foreach ($types as $column => $type) {
	echo "$column è di tipo $type->type"; // ad es. 'id è di tipo int'
}

Logging delle query

Possiamo implementare il nostro logging delle query personalizzato. L'evento onQuery è un array di callback che vengono chiamati dopo ogni query eseguita:

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