SQL diretto

È possibile lavorare con Nette Database in due modi: scrivendo direttamente le query SQL (Direct Access) o lasciando che l'SQL venga generato automaticamente(Explorer Access). L'accesso diretto consente di creare query in tutta sicurezza, mantenendo il pieno controllo sulla loro struttura.

Per informazioni sulla creazione di una connessione e sulla sua configurazione, consultare la pagina separata.

Interrogazione di base

Il metodo query() esegue query al database e restituisce un oggetto ResultSet che rappresenta il risultato. Se la query fallisce, il metodo lancia un'eccezione. È possibile eseguire il ciclo dei risultati della query utilizzando un ciclo foreach o utilizzare una delle funzioni di aiuto.

$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, si possono usare le query parametrizzate. Nette Database lo rende molto semplice: basta aggiungere una virgola e il valore alla query SQL.

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

Per i parametri multipli, è possibile intercalare 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 l'iniezione SQL

Perché è importante utilizzare query parametrizzate? Perché proteggono da attacchi di tipo SQL injection, in cui gli aggressori possono iniettare comandi SQL dannosi per manipolare o accedere ai dati del database.

**Non inserite mai variabili direttamente in una query SQL! Usate sempre query parametrizzate per proteggervi da SQL injection.

// ❌ CODICE NON SICURO - vulnerabile all'iniezione SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Assicuratevi di conoscere i potenziali rischi per la sicurezza.

Tecniche di interrogazione

Condizioni WHERE

È possibile scrivere le condizioni di WHERE come un array associativo, dove le chiavi sono nomi di colonne e i valori sono i dati da confrontare. 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,
]);
// DOVE `nome` = 'Giovanni' E `attivo` = 1

È anche possibile specificare esplicitamente l'operatore nella chiave:

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

I casi speciali, come i valori null o gli array, vengono gestiti automaticamente:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // utilizza l'operatore =
	'category_id' => [1, 2, 3], // utilizza IN
	'description' => null,      // usi è nullo
]);
// DOVE `nome` = 'Laptop' E `category_id` IN (1, 2, 3) E `description` IS NULL

Per le condizioni negative, utilizzare l'operatore NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // utilizza l'operatore <>
	'category_id NOT' => [1, 2, 3], // utilizza NOT IN
	'description NOT' => null,      // utilizza IS NOT NULL
	'id' => [],                     // saltato
]);
// DOVE `nome` <> 'Laptop' E `categoria_id` NON IN (1, 2, 3) E `descrizione` NON È NULL

Per impostazione predefinita, le condizioni sono combinate utilizzando l'operatore AND. È possibile modificare questo comportamento utilizzando il segnaposto ?or.

Ordina per regole

La clausola ORDER BY può essere definita come un array, dove le chiavi rappresentano le colonne e i valori sono booleani che indicano l'ordine crescente:

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

Inserimento di dati (INSERT)

Per inserire i record, utilizzare 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 esempio, PostgreSQL), è necessario specificare il nome della sequenza utilizzando $database->getInsertId($sequenceId).

È anche possibile passare come parametri 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'esecuzione di una INSERZIONE in batch è molto più veloce perché viene eseguita una sola query del database invece di più query individuali.

Nota di sicurezza: Non utilizzare mai dati non validati come $values. Informatevi sui possibili rischi.

Aggiornamento dei dati (UPDATE)

Per aggiornare i record, utilizzare l'istruzione SQL UPDATE.

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

È possibile verificare il numero di righe interessate utilizzando $result->getRowCount().

È possibile utilizzare gli operatori += e -= in UPDATE:

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

Per inserire o aggiornare un record se esiste già, utilizzare 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)
//  SU CHIAVE DUPLICATA AGGIORNARE `nome` = 'Jim', `anno` = 1978

Si noti che Nette Database riconosce il contesto del comando SQL in cui viene utilizzato un parametro con un array e genera il codice SQL di conseguenza. Ad esempio, ha costruito (id, name, year) VALUES (123, 'Jim', 1978) dal primo array, mentre ha convertito il secondo in name = 'Jim', year = 1978. Questo aspetto è trattato in modo più dettagliato nella sezione Suggerimenti per la costruzione di SQL.

Eliminazione di dati (DELETE)

Per cancellare i record, utilizzare l'istruzione SQL DELETE. Esempio con il numero di righe eliminate:

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

Suggerimenti per la costruzione di SQL

I segnaposto SQL consentono di controllare il modo in cui i valori dei parametri vengono incorporati nelle espressioni SQL:

Suggerimento	Descrizione	Usato automaticamente per
`?name`	Utilizzato per i nomi di tabelle o colonne
`?values`	Genera `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Genera assegnazioni `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Unisce le condizioni in un array con `AND`	`WHERE ?`, `HAVING ?`
`?or`	Unisce le condizioni di una matrice con `OR`	– – –
`?order`	Genera la clausola `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Per inserire dinamicamente nomi di tabelle o colonne, utilizzare il segnaposto ?name. Nette Database assicura il corretto escape in base alle convenzioni del database (ad esempio, racchiudendo il tutto in backtick per MySQL).

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

**Attenzione: ** Usare il segnaposto ?name solo per nomi di tabelle e colonne convalidati. In caso contrario, si rischiano vulnerabilità di sicurezza.

Di solito non è necessario specificare altri suggerimenti, poiché Nette utilizza il rilevamento automatico intelligente quando costruisce le query SQL (vedere la terza colonna della tabella). Tuttavia, è possibile utilizzarli in situazioni in cui si desidera combinare le condizioni utilizzando OR invece di AND:

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELEZIONARE * DA UTENTI DOVE `nome` = 'Giovanni' O `email` = 'john@example.com'

Valori speciali

Oltre ai tipi scalari standard (ad esempio, string, int, bool), è possibile passare come parametri anche valori speciali:

File: Utilizzare fopen('file.png', 'r') per inserire il contenuto binario di un file.
Data e ora: gli oggetti di DateTime vengono automaticamente convertiti nel formato di data del database.
Valori enum: Le istanze di enum vengono convertite nei valori corrispondenti.
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 supportano in modo nativo il tipo datetime (ad esempio, SQLite e Oracle), i valori DateTime vengono convertiti in base all'opzione di configurazione formatDateTime (predefinita: U per Unix timestamp).

Letterali SQL

In alcuni casi, potrebbe essere necessario inserire codice SQL grezzo come valore, senza trattarlo come una stringa o senza eseguire l'escape. A tale scopo, si possono utilizzare oggetti della classe Nette\Database\SqlLiteral, che possono essere creati con il metodo Connection::literal().

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELEZIONARE * DA UTENTI DOVE (`nome` = 'Jim') E (`anno` > ANNO())

In alternativa:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM utenti DOVE (`nome` = 'Jim') E (anno > ANNO())

I letterali SQL possono anche contenere parametri:

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

In questo modo è possibile ottenere combinazioni flessibili:

$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 dei dati

Scorciatoie per le query SELECT

Per semplificare il recupero dei dati, la classe Connection fornisce diverse scorciatoie che combinano una chiamata a query() con una successiva chiamata a fetch*(). Questi metodi accettano gli stessi parametri di query(), cioè una query SQL e parametri opzionali. Una descrizione dettagliata dei metodi di fetch*() è riportata di seguito.

`fetch($sql, ...$params): ?Row`	Esegue la query e recupera la prima riga come oggetto `Row`.
`fetchAll($sql, ...$params): array`	Esegue la query e recupera tutte le righe come array di oggetti `Row`.
`fetchPairs($sql, ...$params): array`	Esegue la query e recupera un array associativo in cui la prima colonna è la chiave e la seconda è il valore.
`fetchField($sql, ...$params): mixed`	Esegue la query e recupera il valore della prima cella della prima riga.
`fetchList($sql, ...$params): ?array`	Esegue la query e recupera la prima riga come array indicizzato.

Esempio:

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

`foreach` – Iterazione su righe

Dopo l'esecuzione di una query, viene restituito un oggetto ResultSet, che consente di iterare sui risultati in vari modi. Il modo più semplice e più efficiente in termini di memoria per recuperare le righe è quello di iterare in un ciclo foreach. Questo metodo elabora le righe una alla volta ed evita di memorizzare tutti i dati in una volta sola.

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

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

Il ciclo ResultSet può essere iterato una sola volta. Se si ha bisogno di iterare più volte, è necessario caricare prima i dati in un array, ad esempio con il metodo fetchAll().

fetch(): ?Row

Esegue la query e recupera una singola riga come oggetto Row. Se non sono disponibili altre righe, restituisce null. Questo metodo fa avanzare il puntatore interno alla riga successiva.

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

fetchAll(): array

Recupera tutte le righe rimanenti da ResultSet come array di oggetti Row.

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

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

Recupera i risultati come array associativo. Il primo argomento specifica la colonna da usare come chiave e il secondo specifica 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 fornito solo il primo parametro, il valore sarà l'intera riga (come oggetto Row ):

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

Se null viene passato come chiave, l'array sarà indicizzato numericamente a partire da zero:

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

fetchPairs (Closure $callback): array

In alternativa, è possibile fornire un callback che determina le coppie chiave-valore o i valori per ogni riga.

$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 di chiavi e valori:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Recupera il valore della prima cella della riga corrente. Se non sono disponibili altre righe, restituisce null. Questo metodo fa avanzare il puntatore interno alla riga successiva.

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

fetchList(): ?array

Recupera la riga come array indicizzato. Se non sono disponibili altre righe, restituisce null. Questo metodo fa avanzare 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 recuperate, ma questo potrebbe non essere sempre noto; in questi casi, restituisce null.

getColumnCount(): ?int

Restituisce il numero di colonne di ResultSet.

Informazioni sulla query

Per recuperare i dettagli della query eseguita più di recente, utilizzare:

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

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

Per visualizzare il risultato come tabella HTML, utilizzare:

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

È inoltre possibile recuperare informazioni sui tipi di colonna dal sito ResultSet:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // Ad esempio, "id è di tipo int".
}

Registrazione delle query

È possibile implementare una registrazione personalizzata delle query. L'evento onQuery è un array di callback che vengono invocati dopo ogni esecuzione di query:

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