Podejście SQL

Nette Database oferuje dwa sposoby pracy: zapytania SQL możesz pisać sam (podejście SQL) albo pozwolić je generować automatycznie (patrz Explorer). Podejście SQL daje Ci pełną kontrolę nad zapytaniami i jednocześnie zapewnia, że będą składane bezpiecznie.

Szczegóły dotyczące połączenia i konfiguracji bazy danych znajdziesz w rozdziale Połączenie i konfiguracja.

Podstawowe zapytania

Do odpytywania bazy danych służy metoda query(). Zwraca obiekt ResultSet, który reprezentuje wynik zapytania. Jeśli zapytanie się nie powiedzie, metoda rzuca wyjątek. Wynik zapytania możesz przejść pętlą foreach albo użyć jednej z metod pomocniczych.

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

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

Do bezpiecznego wstawiania wartości do zapytań SQL służą zapytania parametryzowane. Nette Database czyni to niezwykle prostym: wystarczy po zapytaniu SQL dodać przecinek i wartość:

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

Przy większej liczbie parametrów masz dwie możliwości. Możesz albo przeplatać zapytanie SQL parametrami:

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

Albo najpierw napisać całe zapytanie SQL, a potem dołączyć wszystkie parametry:

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

Ochrona przed SQL injection

Dlaczego ważne jest używanie zapytań parametryzowanych? Bo chronią Cię przed atakiem zwanym SQL injection, w którym atakujący mógłby wstrzyknąć własne polecenia SQL i tym samym uzyskać dostęp do danych w bazie albo je uszkodzić.

Nigdy nie wstawiaj zmiennych bezpośrednio do zapytania SQL! Zawsze używaj zapytań parametryzowanych, które chronią Cię przed SQL injection.

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

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

Zapoznaj się z potencjalnymi zagrożeniami bezpieczeństwa.

Techniki zapytań

Warunki WHERE

Warunki WHERE możesz zapisać jako tablicę asocjacyjną, gdzie kluczami są nazwy kolumn, a wartościami dane do porównania. Nette Database automatycznie wybiera najodpowiedniejszy operator SQL na podstawie typu wartości.

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

Operator porównania możesz też podać jawnie w kluczu:

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

Nette automatycznie obsługuje przypadki szczególne, jak wartości null czy tablice.

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

Dla warunków negatywnych użyj operatora NOT:

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

Domyślnie warunki łączone są operatorem AND. Można to zmienić za pomocą zastępnika ?or.

Reguły ORDER BY

Klauzulę ORDER BY można zapisać za pomocą tablicy. W kluczach podaj kolumny, a wartością logiczną wskaż kolejność rosnącą (true) albo malejącą (false):

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

Do wstawiania rekordów służy polecenie SQL INSERT.

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

Metoda getInsertId() zwraca ID ostatnio wstawionego wiersza. Dla niektórych baz danych (np. PostgreSQL) trzeba podać jako parametr nazwę sekwencji, z której ma zostać wygenerowane ID, za pomocą $database->getInsertId($sequenceId).

Jako parametry możesz przekazać także Wartości specjalne, jak pliki, obiekty DateTime czy typy enum.

Wstawianie wielu rekordów naraz:

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

INSERT wielu rekordów jest znacznie szybszy, bo wykonywane jest tylko jedno zapytanie do bazy zamiast wielu pojedynczych.

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

Aktualizacja danych (UPDATE)

Do aktualizacji rekordów służy polecenie SQL UPDATE.

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

Liczbę dotkniętych wierszy zwraca $result->getRowCount().

Przy UPDATE możemy użyć operatorów += i -=:

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

Przykład wstawienia albo aktualizacji rekordu, jeśli już istnieje. Użyjemy 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

Zauważ, że Nette Database rozpoznaje kontekst, w którym w poleceniu SQL użyty jest parametr tablicowy, i odpowiednio składa kod SQL. Z pierwszej tablicy złożyło więc (id, name, year) VALUES (123, 'Jim', 1978), podczas gdy drugą przekształciło w postać name = 'Jim', year = 1978. Omawiamy to szczegółowo w sekcji Wskazówki składania SQL.

Usuwanie danych (DELETE)

Do usuwania rekordów służy polecenie SQL DELETE. Przykład uzyskania liczby usuniętych wierszy:

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

Wskazówki składania SQL

Wskazówka to specjalny zastępnik w zapytaniu SQL, który określa, jak wartość parametru ma zostać przekształcona w wyrażenie SQL:

Wskazówka	Opis	Automatycznie używana dla
`?name`	Służy do wstawiania nazw tabel albo kolumn	–
`?values`	Generuje `(klucz, ...) VALUES (wartość, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	Generuje przypisania `klucz = wartość, ...`	`SET ?`, `KEY UPDATE ?`
`?and`	Łączy warunki w tablicy operatorem `AND`	`WHERE ?`, `HAVING ?`
`?or`	Łączy warunki w tablicy operatorem `OR`	–
`?order`	Generuje klauzulę `ORDER BY`	`ORDER BY ?`, `GROUP BY ?`

Zastępnik ?name służy do dynamicznego wstawiania do zapytania nazw tabel i kolumn. Nette Database dba o poprawne cytowanie identyfikatorów zgodnie z konwencjami bazy danych (np. otoczenie backtickami w 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)

Uwaga: zastępnika ?name używaj wyłącznie dla zwalidowanych nazw tabel i kolumn. W przeciwnym razie ryzykujesz luki bezpieczeństwa.

Pozostałych wskazówek zwykle nie trzeba podawać, bo Nette przy składaniu zapytania SQL używa sprytnej autodetekcji (patrz trzecia kolumna tabeli). Możesz jednak ich użyć na przykład w sytuacji, gdy chcesz połączyć warunki operatorem 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 zwykłych typów skalarnych (string, int, bool) możesz jako parametry przekazać także wartości specjalne:

pliki: fopen('image.gif', 'r') wstawia binarną zawartość pliku
data i czas: obiekty DateTimeInterface konwertowane są na format bazy danych
typy enum: instancje enum konwertowane są na swoją wartość
literały SQL: tworzone przez Connection::literal('NOW()') wstawiane są do zapytania bezpośrednio

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

Dla baz danych, które nie mają natywnego wsparcia dla typu danych datetime (jak SQLite i Oracle), obiekty DateTime i DateTimeImmutable konwertowane są na wartość określoną w konfiguracji bazy danych pozycją formatDateTime (wartość domyślna to U, czyli uniksowy timestamp).

Literały SQL

W niektórych przypadkach potrzebujesz przekazać jako wartość surowy kod SQL, który nie ma być traktowany jak ciąg i escapowany. Służą do tego obiekty klasy Nette\Database\SqlLiteral. Tworzy je metoda 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ą zawierać parametry:

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

Pozwala to na ciekawe 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

Dla uproszczenia pobierania danych Connection oferuje kilka skrótów łączących wywołanie query() z późniejszym wywołaniem fetch*(). Metody te przyjmują te same parametry co query(), czyli zapytanie SQL i opcjonalne parametry. Pełny opis metod fetch*() znajdziesz niżej.

`fetch($sql, ...$params): ?Row`	Wykonuje zapytanie i zwraca pierwszy wiersz jako obiekt `Row` albo `null`.
`fetchAll($sql, ...$params): array`	Wykonuje zapytanie i zwraca wszystkie wiersze jako tablicę obiektów `Row`.
`fetchPairs($sql, ...$params): array`	Wykonuje zapytanie i zwraca tablicę asocjacyjną (pary klucz ⇒ wartość).
`fetchField($sql, ...$params): mixed`	Wykonuje zapytanie i zwraca wartość pierwszej kolumny z pierwszego wiersza.
`fetchList($sql, ...$params): ?array`	Wykonuje zapytanie i zwraca pierwszy wiersz jako tablicę indeksowaną albo `null`.

Przykład:

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

`foreach` – iterowanie po wierszach

Po wykonaniu zapytania zwracany jest obiekt ResultSet, który pozwala przejść wyniki na kilka sposobów. Najprostszym sposobem wykonania zapytania i pobrania wierszy jest iterowanie w pętli foreach. Ta metoda jest najoszczędniejsza pamięciowo, bo pobiera dane wiersz po wierszu i nie ładuje całego zbioru wyników naraz do pamięci.

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

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

Po ResultSet można iterować tylko raz. Jeśli potrzebujesz iterować wielokrotnie, musisz najpierw wczytać dane do tablicy, na przykład metodą fetchAll().

fetch(): ?Row

Zwraca wiersz jako obiekt Row. Jeśli nie ma już kolejnych wierszy, zwraca null. Przesuwa wewnętrzny wskaźnik na kolejny wiersz.

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

fetchAll(): array

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

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

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

Zwraca zbiór wyników jako tablicę asocjacyjną. Pierwszy argument określa kolumnę używaną jako klucze, a drugi kolumnę używaną jako wartości:

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

Jeśli podany jest tylko pierwszy parametr ($key), jako wartość użyty zostanie cały wiersz (obiekt Row):

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

W przypadku zduplikowanych kluczy używana jest wartość z ostatniego wiersza. Użycie null jako klucza daje tablicę indeksowaną liczbowo (od zera), co zapobiega kolizjom kluczy:

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

fetchPairs (Closure $callback): array

Alternatywnie możesz podać callback, który przetworzy każdy wiersz. Callback może zwrócić pojedynczą wartość albo parę klucz-wartość.

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

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

fetchField(): mixed

Zwraca wartość pierwszej kolumny z bieżącego wiersza. Jeśli nie ma już kolejnych wierszy, zwraca null. Przesuwa wewnętrzny wskaźnik na kolejny wiersz.

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

fetchList(): ?array

Zwraca wiersz jako tablicę indeksowaną. Jeśli nie ma już kolejnych wierszy, zwraca null. Przesuwa wewnętrzny wskaźnik na kolejny wiersz.

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

getRowCount(): ?int

Zwraca liczbę dotkniętych wierszy z ostatniego zapytania UPDATE albo DELETE. Dla zapytań SELECT zwraca liczbę wierszy w zbiorze wyników. Nie zawsze jednak musi być ona znana, wtedy metoda zwraca null.

getColumnCount(): ?int

Zwraca liczbę kolumn w ResultSet.

Informacje o zapytaniu

Na potrzeby debugowania możemy uzyskać informacje o ostatnio wykonanym zapytaniu:

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

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

Żeby wyświetlić wynik jako tabelę HTML, możesz użyć:

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

ResultSet udostępnia informacje o typach kolumn:

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

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

Logowanie zapytań

Możemy zaimplementować własne logowanie zapytań. Zdarzenie onQuery to tablica callbacków wywoływanych po każdym wykonanym zapytaniu:

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