SQL Way

Możesz pracować z Nette Database na dwa sposoby: pisząc zapytania SQL(sposób SQL) lub pozwalając, aby SQL był generowany automatycznie(sposób Explorer). Sposób SQL pozwala na bezpieczne tworzenie zapytań przy zachowaniu pełnej kontroli nad ich strukturą.

Zobacz Połączenie i konfiguracja, aby uzyskać szczegółowe informacje na temat konfiguracji połączenia z bazą danych.

Podstawowe zapytania

Metoda query() wykonuje zapytania do bazy danych i zwraca obiekt ResultSet reprezentujący wynik. Jeśli zapytanie nie powiedzie się, metoda zgłasza wyjątek. Wynik zapytania można przeglądać za pomocą pętli foreach lub użyć jednej z funkcji pomocniczych.

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

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

Aby bezpiecznie wstawiać wartości do zapytań SQL, należy używać zapytań parametryzowanych. Nette Database sprawia, że jest to bardzo proste: wystarczy dodać przecinek i wartość do zapytania SQL.

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

W przypadku wielu parametrów można albo przeplatać zapytanie SQL z parametrami:

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

Lub najpierw napisać całe zapytanie SQL, a następnie dołączyć wszystkie parametry:

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

Ochrona przed SQL Injection

Dlaczego korzystanie z zapytań parametryzowanych jest ważne? Ponieważ chronią one przed atakami SQL injection, w których atakujący mogą wstrzykiwać złośliwe polecenia SQL w celu manipulowania danymi bazy danych lub uzyskiwania do nich dostępu.

Nigdy nie wstawiaj zmiennych bezpośrednio do zapytania SQL! Zawsze używaj sparametryzowanych zapytań, aby chronić się przed wstrzyknięciem SQL.

// ❌ NIEBEZPIECZNY KOD - podatny na wstrzyknięcie kodu SQL
$database->query("SELECT * FROM users WHERE name = '$name'");

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

Należy zapoznać się z potencjalnymi zagrożeniami bezpieczeństwa.

Techniki zapytań

Warunki WHERE

Możesz zapisać warunki WHERE jako tablicę asocjacyjną, gdzie klucze są nazwami kolumn, a wartości są danymi do porównania. Nette Database automatycznie wybiera najbardziej odpowiedni operator SQL na podstawie typu wartości.

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

Można również jawnie określić operator w kluczu:

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

Specjalne przypadki, takie jak wartości null lub tablice, są obsługiwane automatycznie:

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // używa operatora =
	'category_id' => [1, 2, 3], // używa IN
	'description' => null,      // uses IS NULL
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

W przypadku warunków ujemnych należy użyć operatora NOT:

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // używa operatora <>
	'category_id NOT' => [1, 2, 3], // używa NOT IN
	'description NOT' => null,      // używa IS NOT NULL
	'id' => [],                     // skipped
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Domyślnie warunki są łączone za pomocą operatora AND. Możesz zmienić to zachowanie za pomocą symbolu zastępczego ?or.

Reguły ORDER BY

Klauzula ORDER BY może być zdefiniowana jako tablica, w której klucze reprezentują kolumny, a wartości są wartościami logicznymi wskazującymi kolejność rosnącą:

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

Wstawianie danych (INSERT)

Aby wstawić rekordy, należy użyć instrukcji SQL INSERT.

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

Metoda getInsertId() zwraca ID ostatniego wstawionego wiersza. W przypadku niektórych baz danych (np. PostgreSQL) należy określić nazwę sekwencji za pomocą $database->getInsertId($sequenceId).

Jako parametry można również przekazywać wartości specjalne, takie jak pliki, obiekty DateTime lub typy wyliczeniowe.

Wstawianie wielu rekordów jednocześnie:

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

Wykonanie wsadowego INSERT jest znacznie szybsze, ponieważ wykonywane jest tylko jedno zapytanie do bazy danych zamiast wielu pojedynczych zapytań.

Uwaga dotycząca bezpieczeństwa: Nigdy nie używaj niezwalidowanych danych jako $values. Zapoznaj się z możliwymi zagrożeniami.

Aktualizacja danych (UPDATE)

Aby zaktualizować rekordy, należy użyć instrukcji SQL UPDATE.

// Aktualizacja pojedynczego rekordu
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

Liczbę wierszy, których to dotyczy, można sprawdzić za pomocą $result->getRowCount().

Można użyć operatorów += i -= w UPDATE:

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

Aby wstawić lub zaktualizować rekord, jeśli już istnieje, użyj techniki 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

Należy zauważyć, że Nette Database rozpoznaje kontekst polecenia SQL, w którym używany jest parametr z tablicą i odpowiednio generuje kod SQL. Na przykład skonstruował (id, name, year) VALUES (123, 'Jim', 1978) z pierwszej tablicy, podczas gdy drugą przekonwertował na name = 'Jim', year = 1978. Zostało to omówione bardziej szczegółowo w sekcji Wskazówki dotyczące konstruowania kodu SQL.

Usuwanie danych (DELETE)

Aby usunąć rekordy, należy użyć instrukcji SQL DELETE. Przykład z liczbą usuniętych wierszy:

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

Wskazówki dotyczące budowy SQL

Symbole zastępcze SQL pozwalają kontrolować sposób, w jaki wartości parametrów są włączane do wyrażeń SQL:

Wskazówka	Opis	Automatycznie używane dla
`?name`	Używane dla nazw tabel lub kolumn	–
`?values`	Generuje `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Generuje przypisania `key = value, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Łączy warunki w tablicy z `AND`	`WHERE ?`, `HAVING ?`
`?or`	Łączy warunki w tablicy z `OR`	–
`?order`	Generuje klauzulę `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Do dynamicznego wstawiania nazw tabel lub kolumn należy użyć symbolu zastępczego ?name. Nette Database zapewnia prawidłowe uciekanie zgodnie z konwencjami bazy danych (np. zamykanie w backticks dla MySQL).

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

Ostrzeżenie: Używaj tylko symbolu zastępczego ?name dla zweryfikowanych nazw tabel i kolumn. W przeciwnym razie istnieje ryzyko wystąpienia luk w zabezpieczeniach.

Inne podpowiedzi zwykle nie są konieczne do określenia, ponieważ Nette używa inteligentnego automatycznego wykrywania podczas konstruowania zapytań SQL (patrz trzecia kolumna tabeli). Można ich jednak użyć w sytuacjach, gdy chcemy połączyć warunki za pomocą OR zamiast 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'

Wartości specjalne

Oprócz standardowych typów skalarnych (np. string, int, bool), można również przekazywać wartości specjalne jako parametry:

Pliki: Użyj fopen('file.png', 'r') aby wstawić binarną zawartość pliku.
Date and Time: obiekty DateTime są automatycznie konwertowane do formatu daty bazy danych.
Enum Values: Instancje enum są konwertowane na odpowiadające im wartości.
SQL Literals: Tworzone za pomocą Connection::literal('NOW()'), są wstawiane bezpośrednio do zapytania.

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

W przypadku baz danych, które nie obsługują natywnie typu datetime (np. SQLite i Oracle), wartości DateTime są konwertowane zgodnie z opcją konfiguracji formatDateTime (domyślnie: U dla uniksowego znacznika czasu).

Literały SQL

W niektórych przypadkach może być konieczne wstawienie nieprzetworzonego kodu SQL jako wartości bez traktowania go jako ciągu znaków lub ucieczki. W tym celu należy użyć obiektów klasy Nette\Database\SqlLiteral, które można utworzyć za pomocą metody Connection::literal().

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

Alternatywnie:

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

Literały SQL mogą również zawierać parametry:

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

Pozwala to na elastyczne kombinacje:

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

Pobieranie danych

Skróty dla zapytań SELECT

Aby uprościć pobieranie danych, klasa Connection udostępnia kilka skrótów, które łączą wywołanie query() z kolejnym wywołaniem fetch*(). Metody te akceptują te same parametry co query(), tj. zapytanie SQL i opcjonalne parametry. Szczegółowy opis metod fetch*() można znaleźć poniżej.

`fetch($sql, ...$params): ?Row`	Wykonuje zapytanie i pobiera pierwszy wiersz jako obiekt `Row`.
`fetchAll($sql, ...$params): array`	Wykonuje zapytanie i pobiera wszystkie wiersze jako tablicę obiektów `Row`.
`fetchPairs($sql, ...$params): array`	Wykonuje zapytanie i pobiera tablicę asocjacyjną, w której pierwsza kolumna jest kluczem, a druga wartością.
`fetchField($sql, ...$params): mixed`	Wykonuje zapytanie i pobiera wartość pierwszej komórki w pierwszym wierszu.
`fetchList($sql, ...$params): ?array`	Wykonuje zapytanie i pobiera pierwszy wiersz jako tablicę indeksowaną.

Przykład:

// fetchField() - zwraca wartość pierwszej komórki
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – Iteracja po wierszach

Po wykonaniu zapytania zwracany jest obiekt ResultSet, który umożliwia iterację po wynikach na różne sposoby. Najprostszym i najbardziej wydajnym pamięciowo sposobem pobierania wierszy jest iteracja w pętli foreach. Metoda ta przetwarza wiersze pojedynczo i pozwala uniknąć przechowywania wszystkich danych w pamięci jednocześnie.

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

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

Pętla ResultSet może być iterowana tylko raz. Jeśli konieczne jest wielokrotne iterowanie, należy najpierw załadować dane do tablicy, na przykład za pomocą metody fetchAll().

fetch(): ?Row

Wykonuje zapytanie i pobiera pojedynczy wiersz jako obiekt Row. Jeśli nie ma więcej wierszy, zwraca null. Ta metoda przesuwa wewnętrzny wskaźnik do następnego wiersza.

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // pobiera pierwszy wiersz
if ($row) {
	echo $row->name;
}

fetchAll(): array

Pobiera wszystkie pozostałe wiersze z ResultSet jako tablicę obiektów Row.

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // pobiera wszystkie wiersze
foreach ($rows as $row) {
	echo $row->name;
}

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

Pobiera wyniki jako tablicę asocjacyjną. Pierwszy argument określa kolumnę, która ma być użyta jako klucz, a drugi określa kolumnę, która ma być użyta jako wartość:

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

Jeśli podano tylko pierwszy parametr, wartością będzie cały wiersz (jako obiekt Row ):

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

Jeśli null zostanie przekazany jako klucz, tablica będzie indeksowana numerycznie, zaczynając od zera:

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

fetchPairs (Closure $callback): array

Alternatywnie można podać wywołanie zwrotne, które określa pary klucz-wartość lub wartości dla każdego wiersza.

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

// Wywołanie zwrotne może również zwrócić tablicę z parą klucz i wartość:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...].

fetchField(): mixed

Pobiera wartość pierwszej komórki w bieżącym wierszu. Jeśli nie ma więcej wierszy, zwraca null. Ta metoda przesuwa wewnętrzny wskaźnik do następnego wiersza.

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // pobiera nazwę z pierwszego wiersza

fetchList(): ?array

Pobiera wiersz jako indeksowaną tablicę. Jeśli nie ma więcej wierszy, zwraca null. Ta metoda przesuwa wewnętrzny wskaźnik do następnego wiersza.

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

getRowCount(): ?int

Zwraca liczbę wierszy, których dotyczyło ostatnie zapytanie UPDATE lub DELETE. W przypadku zapytań SELECT zwraca liczbę pobranych wierszy, ale nie zawsze jest ona znana – w takich przypadkach zwraca null.

getColumnCount(): ?int

Zwraca liczbę kolumn w pliku ResultSet.

Informacje o zapytaniu

Aby uzyskać szczegółowe informacje na temat ostatnio wykonanego zapytania, należy użyć opcji

echo $database->getLastQueryString(); // wyprowadza zapytanie SQL

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // wyprowadza zapytanie SQL
echo $result->getTime();           // wyświetla czas wykonania w sekundach

Aby wyświetlić wynik jako tabelę HTML, użyj:

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

Można również pobrać informacje o typach kolumn z ResultSet:

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

foreach ($types as $column => $type) {
	echo "$column is of type $type->type"; // np. "id jest typu int
}

Rejestrowanie zapytań

Można zaimplementować niestandardowe rejestrowanie zapytań. Zdarzenie onQuery to tablica wywołań zwrotnych, które są wywoływane po każdym wykonaniu zapytania:

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