Dostęp SQL

Nette Database oferuje dwie ścieżki: możesz pisać zapytania SQL samodzielnie (dostęp SQL) lub pozwolić na ich automatyczne generowanie (zobacz Explorer). Dostęp SQL daje Ci pełną kontrolę nad zapytaniami, jednocześnie zapewniając ich bezpieczne tworzenie.

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

Podstawowe zapytania

Do wykonywania zapytań do bazy danych służy metoda query(). Zwraca ona obiekt ResultSet, który reprezentuje wynik zapytania. W przypadku niepowodzenia metoda rzuci wyjątek. Wynik zapytania możemy 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;
}

Do bezpiecznego wstawiania wartości do zapytań SQL używamy zapytań sparametryzowanych. Nette Database czyni je maksymalnie prostymi – wystarczy dodać przecinek i wartość po zapytaniu SQL:

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

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

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

Albo napisać najpierw 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 ważne jest używanie zapytań sparametryzowanych? Ponieważ chronią Cię przed atakiem zwanym SQL injection, w którym atakujący mógłby podrzucić własne polecenia SQL i tym samym uzyskać lub uszkodzić dane w bazie danych.

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

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

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

Zapoznaj się z możliwymi ryzykami bezpieczeństwa.

Techniki zapytań

Warunki WHERE

Warunki WHERE możesz zapisać jako tablicę asocjacyjną, gdzie klucze to nazwy kolumn, a wartości to dane do porównania. Nette Database automatycznie wybierze najodpowiedniejszy operator SQL w zależności od typu wartości.

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

W kluczu możesz również jawnie określić operator do porównania:

$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 specjalne przypadki, takie jak wartości null lub 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' => [],                     // zostanie pominięte
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

Do łączenia warunków używa się operatora AND. Można to zmienić za pomocą symbolu zastępczego ?or.

Reguły ORDER BY

Sortowanie ORDER BY można zapisać za pomocą tablicy. W kluczach podajemy kolumny, a wartością będzie boolean określający, czy sortować rosnąco:

$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 używa się polecenia 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. W niektórych bazach danych (np. PostgreSQL) konieczne jest podanie jako parametru nazwy sekwencji, z której ma być generowane ID za pomocą $database->getInsertId($sequenceId).

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

Wstawienie wielu rekordów naraz:

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

Wielokrotny INSERT jest znacznie szybszy, ponieważ wykonuje się jedno zapytanie do bazy danych, zamiast wielu pojedynczych.

Ostrzeżenie dotyczące bezpieczeństwa: Nigdy nie używaj jako $values niezweryfikowanych danych. Zapoznaj się z możliwymi ryzykami.

Aktualizacja danych (UPDATE)

Do aktualizacji rekordów używa się polecenia SQL UPDATE.

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

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

Dla UPDATE możemy wykorzystać operatory += i -=:

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

Przykład wstawienia lub 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, w jakim kontekście polecenia SQL wstawiamy parametr z tablicą i odpowiednio tworzy z niego kod SQL. Tak więc z pierwszej tablicy utworzył (id, name, year) VALUES (123, 'Jim', 1978), podczas gdy drugą przekształcił do postaci name = 'Jim', year = 1978. Szczegółowiej omówimy to w części Wskazówki dotyczące tworzenia SQL.

Usuwanie danych (DELETE)

Do usuwania rekordów używa się polecenia SQL DELETE. Przykład z uzyskaniem liczby usuniętych wierszy:

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

Wskazówki dotyczące tworzenia SQL

Wskazówka (hint) to specjalny symbol zastępczy w zapytaniu SQL, który mówi, jak wartość parametru ma zostać przepisana na wyrażenie SQL:

Wskazówka	Opis	Używa się automatycznie
`?name`	używa do wstawienia nazwy tabeli lub kolumny	–
`?values`	generuje `(key, ...) VALUES (value, ...)`	`INSERT ... ?`, `REPLACE ... ?`
`?set`	generuje przypisanie `key = value, ...`	`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 ?`

Do dynamicznego wstawiania nazw tabel i kolumn do zapytania służy symbol zastępczy ?name. Nette Database zadba o poprawne przetworzenie identyfikatorów zgodnie z konwencjami danej bazy danych (np. zamknięcie w odwrotnych apostrofach 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)

Ostrzeżenie: symbol ?name używaj tylko dla nazw tabel i kolumn z zweryfikowanych danych wejściowych, w przeciwnym razie narażasz się na ryzyko bezpieczeństwa.

Pozostałych wskazówek zwykle nie trzeba podawać, ponieważ Nette używa inteligentnej autodetekcji podczas składania zapytania SQL (zobacz trzecią kolumnę tabeli). Ale możesz jej użyć na przykład w sytuacji, gdy chcesz 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 zwykłych typów skalarnych (string, int, bool) możesz przekazywać jako parametry również wartości specjalne:

pliki: fopen('image.gif', 'r') wstawia binarną zawartość pliku
data i czas: obiekty DateTime są konwertowane na format bazodanowy
typy wyliczeniowe: instancje enum są konwertowane na ich wartość
literały SQL: utworzone 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 bazach danych, które nie mają natywnego wsparcia dla typu danych datetime (jak SQLite i Oracle), DateTime jest konwertowany na wartość określoną w konfiguracji bazy danych za pomocą opcji formatDateTime (domyślna wartość to U – unix timestamp).

Literały SQL

W niektórych przypadkach musisz podać jako wartość bezpośrednio kod SQL, który nie powinien być traktowany jako ciąg znaków i escapowany. Do tego służą 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())

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

Dzięki czemu możemy tworzyć 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

Aby uprościć pobieranie danych, Connection oferuje kilka skrótów, które łączą wywołanie query() z następującym fetch*(). Metody te przyjmują te same parametry co query(), czyli zapytanie SQL i opcjonalne parametry. Pełny opis metod fetch*() znajdziesz poniżej.

`fetch($sql, ...$params): ?Row`	Wykonuje zapytanie i zwraca pierwszy wiersz jako obiekt `Row`
`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ą, gdzie pierwsza kolumna reprezentuje klucz, a druga wartość
`fetchField($sql, ...$params): mixed`	Wykonuje zapytanie i zwraca wartość pierwszej komórki z pierwszego wiersza
`fetchList($sql, ...$params): ?array`	Wykonuje zapytanie i zwraca 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 przeglądanie wyników na kilka sposobów. Najłatwiejszym sposobem wykonania zapytania i pobrania wierszy jest iteracja w pętli foreach. Ten sposób jest najbardziej oszczędny pod względem pamięci, ponieważ zwraca dane stopniowo i nie przechowuje ich wszystkich w pamięci naraz.

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

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

ResultSet można iterować tylko raz. Jeśli potrzebujesz iterować wielokrotnie, musisz najpierw załadować dane do tablicy, na przykład za pomocą metody fetchAll().

fetch(): ?Row

Zwraca wiersz jako obiekt Row. Jeśli nie ma już więcej wierszy, zwraca null. Przesuwa wewnętrzny wskaźnik na następny 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 wyniki jako tablicę asocjacyjną. Pierwszy argument określa nazwę kolumny, która zostanie użyta jako klucz w tablicy, drugi argument określa nazwę kolumny, która zostanie 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 podamy tylko pierwszy parametr, wartością będzie cały wiersz, czyli obiekt Row:

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

W przypadku zduplikowanych kluczy użyta zostanie wartość z ostatniego wiersza. Przy użyciu null jako klucza, tablica będzie indeksowana numerycznie od zera (wtedy nie dochodzi do kolizji):

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

fetchPairs(Closure $callback): array

Alternatywnie możesz podać jako parametr callback, który dla każdego wiersza zwróci albo samą 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 również zwracać tablicę z parą klucz & wartość:
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

Zwraca wartość pierwszej komórki z bieżącego wiersza. Jeśli nie ma już więcej wierszy, zwraca null. Przesuwa wewnętrzny wskaźnik na następny wiersz.

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

fetchList(): ?array

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

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

getRowCount(): ?int

Zwraca liczbę zmienionych wierszy przez ostatnie zapytanie UPDATE lub DELETE. Dla SELECT jest to liczba zwróconych wierszy, ale ta może nie być znana – w takim przypadku metoda zwróci null.

getColumnCount(): ?int

Zwraca liczbę kolumn w ResultSet.

Informacje o zapytaniach

Do celów debugowania możemy uzyskać informacje o ostatnim 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

Do wyświetlenia wyniku jako tabeli HTML można użyć:

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

ResultSet oferuje informacje o typach kolumn:

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

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

Logowanie zapytań

Możemy zaimplementować własne logowanie zapytań. Zdarzenie onQuery jest tablicą callbacków, które są wywoływane po każdym wykonanym zapytaniu:

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

	if ($result->getRowCount() > 1000) {
		$logger->warning('Duży zestaw wyników: ' . $result->getRowCount() . ' wierszy');
	}
};

Nette Documentation Preview