Direct SQL

Z Nette Database można pracować na dwa sposoby: pisząc zapytania SQL bezpośrednio(Direct Access) lub pozwalając na automatyczne generowanie SQL(Explorer Access). Direct Access pozwala na bezpieczne tworzenie zapytań przy zachowaniu pełnej kontroli nad ich strukturą.

Informacje na temat tworzenia połączenia i jego konfiguracji można znaleźć na osobnej stronie.

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