SQLアクセス

Nette Databaseは2つの方法を提供します：SQLクエリを自分で記述する（SQLアクセス）、または自動的に生成させる（Explorerを参照）。SQLアクセスはクエリを完全に制御でき、同時に安全な構築を保証します。

データベース接続と設定の詳細については、接続と設定の章を参照してください。

基本的なクエリ

データベースにクエリを実行するには、query()メソッドを使用します。これは、クエリの結果を表すResultSetオブジェクトを返します。失敗した場合、メソッドは例外をスローします。クエリの結果はforeachループを使用して反復処理するか、ヘルパー関数のいずれかを使用できます。

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

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

SQLクエリに値を安全に挿入するには、パラメータ化されたクエリを使用します。Nette Databaseはこれを最大限に簡単にします – SQLクエリの後にカンマと値を追加するだけです：

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

複数のパラメータがある場合、2つの記述方法があります。SQLクエリにパラメータを「散りばめる」ことができます：

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

または、まず完全なSQLクエリを記述し、次にすべてのパラメータを追加します：

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

SQLインジェクションからの保護

パラメータ化されたクエリを使用することが重要なのはなぜでしょうか？なぜなら、SQLインジェクションと呼ばれる攻撃から保護してくれるからです。この攻撃では、攻撃者が独自のSQLコマンドを挿入し、それによってデータベース内のデータを取得または破損させる可能性があります。

変数をSQLクエリに直接挿入しないでください！ SQLインジェクションから保護するために、常にパラメータ化されたクエリを使用してください。

// ❌ 危険なコード - SQLインジェクションに対して脆弱
$database->query("SELECT * FROM users WHERE name = '$name'");

// ✅ 安全なパラメータ化されたクエリ
$database->query('SELECT * FROM users WHERE name = ?', $name);

潜在的なセキュリティリスクについて理解してください。

クエリ技術

WHERE条件

WHERE条件は連想配列として記述でき、キーはカラム名、値は比較データです。Nette Databaseは、値の型に基づいて最適なSQL演算子を自動的に選択します。

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

キーで比較演算子を明示的に指定することもできます：

$database->query('SELECT * FROM users WHERE', [
	'age >' => 25,          // 演算子 > を使用
	'name LIKE' => '%John%', // 演算子 LIKE を使用
	'email NOT LIKE' => '%example.com%', // 演算子 NOT LIKE を使用
]);
// WHERE `age` > 25 AND `name` LIKE '%John%' AND `email` NOT LIKE '%example.com%'

Netteは、null値や配列などの特殊なケースを自動的に処理します。

$database->query('SELECT * FROM products WHERE', [
	'name' => 'Laptop',         // 演算子 = を使用
	'category_id' => [1, 2, 3], // IN を使用
	'description' => null,      // IS NULL を使用
]);
// WHERE `name` = 'Laptop' AND `category_id` IN (1, 2, 3) AND `description` IS NULL

否定条件には演算子 NOT を使用します：

$database->query('SELECT * FROM products WHERE', [
	'name NOT' => 'Laptop',         // 演算子 <> を使用
	'category_id NOT' => [1, 2, 3], // NOT IN を使用
	'description NOT' => null,      // IS NOT NULL を使用
	'id' => [],                     // 省略されます
]);
// WHERE `name` <> 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

条件を結合するには演算子 AND が使用されます。これはプレースホルダ ?orを使用して変更できます。

ORDER BYルール

ORDER BYソートは配列を使用して記述できます。キーにカラムを指定し、値は昇順でソートするかどうかを示すブール値になります：

$database->query('SELECT id FROM author ORDER BY', [
	'id' => true, // 昇順
	'name' => false, // 降順
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC

データの挿入 (INSERT)

レコードを挿入するには、SQLコマンド INSERT を使用します。

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

getInsertId()メソッドは、最後に挿入された行のIDを返します。一部のデータベース（例：PostgreSQL）では、$database->getInsertId($sequenceId)を使用してIDを生成するシーケンス名をパラメータとして指定する必要があります。

パラメータとして、ファイル、DateTimeオブジェクト、または列挙型などの特別な値を渡すこともできます。

複数のレコードを一度に挿入する：

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

複数INSERTは、多くの個別のクエリではなく単一のデータベースクエリが実行されるため、はるかに高速です。

セキュリティ警告: $valuesとして検証されていないデータを使用しないでください。潜在的なリスクについて理解してください。

データの更新 (UPDATE)

レコードを更新するには、SQLコマンド UPDATE を使用します。

// 1つのレコードの更新
$values = [
	'name' => 'John Smith',
];
$result = $database->query('UPDATE users SET ? WHERE id = ?', $values, 1);

影響を受けた行数は $result->getRowCount() で返されます。

UPDATEには演算子 += および -= を使用できます：

$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // login_count をインクリメント
], 1);

レコードが存在する場合は挿入、存在しない場合は更新する例。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

Nette Databaseが、SQLコマンドのどのコンテキストに配列パラメータを挿入するかを認識し、それに応じてSQLコードを構築することに注意してください。したがって、最初の配列から(id, name, year) VALUES (123, 'Jim', 1978)を構築し、2番目の配列をname = 'Jim', year = 1978の形式に変換しました。これについては、SQL構築のヒントセクションで詳しく説明します。

データの削除 (DELETE)

レコードを削除するには、SQLコマンド DELETE を使用します。削除された行数を取得する例：

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

SQL構築のヒント

ヒントは、SQLクエリ内の特別なプレースホルダーであり、パラメータ値をSQL式にどのように書き換えるかを示します：

ヒント	説明	自動的に使用される
`?name`	テーブル名またはカラム名の挿入に使用します	–
`?values`	`(key, ...) VALUES (value, ...)` を生成します	`INSERT ... ?`, `REPLACE ... ?`
`?set`	割り当て `key = value, ...` を生成します	`SET ?`, `KEY UPDATE ?`
`?and`	配列内の条件を `AND` 演算子で結合します	`WHERE ?`, `HAVING ?`
`?or`	配列内の条件を `OR` 演算子で結合します	–
`?order`	`ORDER BY` 句を生成します	`ORDER BY ?`, `GROUP BY ?`

テーブル名とカラム名をクエリに動的に挿入するには、プレースホルダ ?name を使用します。Nette Databaseは、特定のデータベースの規則に従って識別子を正しく処理します（たとえば、MySQLではバッククォートで囲む）。

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

警告: シンボル ?name は、検証された入力からのテーブル名とカラム名にのみ使用してください。そうしないと、セキュリティリスクにさらされます。

他のヒントは通常、NetteがSQLクエリを構築する際に賢い自動検出を使用するため（表の3番目の列を参照）、指定する必要はありません。ただし、たとえば AND の代わりに OR を使用して条件を結合したい場合などに使用できます：

$database->query('SELECT * FROM users WHERE ?or', [
	'name' => 'John',
	'email' => 'john@example.com',
]);
// SELECT * FROM users WHERE `name` = 'John' OR `email` = 'john@example.com'

特別な値

通常のスカラ型（string、int、bool）に加えて、パラメータとして特別な値を渡すこともできます：

ファイル：fopen('image.gif', 'r') はファイルのバイナリコンテンツを挿入します
日付と時刻：DateTimeオブジェクトはデータベース形式に変換されます
列挙型：enumインスタンスはその値に変換されます
SQLリテラル：Connection::literal('NOW()')を使用して作成されたものは、クエリに直接挿入されます

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

datetimeデータ型をネイティブにサポートしていないデータベース（SQLiteやOracleなど）の場合、DateTimeはデータベース設定のformatDateTime項目で指定された値（デフォルト値はU – Unixタイムスタンプ）に変換されます。

SQLリテラル

場合によっては、値として直接SQLコードを指定する必要がありますが、これは文字列として解釈されず、エスケープされるべきではありません。この目的のために、Nette\Database\SqlLiteralクラスのオブジェクトが使用されます。これらはConnection::literal()メソッドによって作成されます。

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

または代替案：

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

SQLリテラルにはパラメータを含めることができます：

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

これにより、興味深い組み合わせを作成できます：

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

データの取得

SELECTクエリのショートカット

データ取得を簡略化するために、Connectionはquery()の呼び出しとそれに続くfetch*()を組み合わせたいくつかのショートカットを提供します。これらのメソッドはquery()と同じパラメータ、つまりSQLクエリとオプションのパラメータを受け入れます。fetch*()メソッドの完全な説明は以下にあります。

`fetch($sql, ...$params): ?Row`	クエリを実行し、最初の行を`Row`オブジェクトとして返します
`fetchAll($sql, ...$params): array`	クエリを実行し、すべての行を`Row`オブジェクトの配列として返します
`fetchPairs($sql, ...$params): array`	クエリを実行し、最初のカラムがキー、2番目のカラムが値である連想配列を返します
`fetchField($sql, ...$params): mixed`	クエリを実行し、最初の行の最初のフィールドの値を返します
`fetchList($sql, ...$params): ?array`	クエリを実行し、最初の行をインデックス付き配列として返します

例：

// fetchField() - 最初のセルの値を返します
$count = $database->query('SELECT COUNT(*) FROM articles')
	->fetchField();

`foreach` – 行の反復処理

クエリを実行した後、ResultSetオブジェクトが返され、これにより結果をいくつかの方法で反復処理できます。クエリを実行して行を取得する最も簡単な方法は、foreachループで反復処理することです。この方法は、データを段階的に返し、一度にメモリに保存しないため、メモリ効率が最も高くなります。

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

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

ResultSetは一度しか反復処理できません。繰り返し反復処理する必要がある場合は、まずfetchAll()メソッドなどを使用してデータを配列に読み込む必要があります。

fetch(): ?Row

行をRowオブジェクトとして返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

$result = $database->query('SELECT * FROM users');
$row = $result->fetch(); // 最初の行を読み込みます
if ($row) {
	echo $row->name;
}

fetchAll(): array

ResultSetから残りのすべての行をRowオブジェクトの配列として返します。

$result = $database->query('SELECT * FROM users');
$rows = $result->fetchAll(); // すべての行を読み込みます
foreach ($rows as $row) {
	echo $row->name;
}

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

結果を連想配列として返します。最初の引数は配列のキーとして使用されるカラム名を指定し、2番目の引数は値として使用されるカラム名を指定します：

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

最初のパラメータのみを指定した場合、値は行全体、つまりRowオブジェクトになります：

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

キーが重複する場合、最後の行の値が使用されます。キーとしてnullを使用すると、配列はゼロから数値でインデックス付けされます（衝突は発生しません）：

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

fetchPairs(Closure $callback): array

あるいは、パラメータとしてコールバックを指定できます。これは、各行に対して値自体、またはキーと値のペアのいずれかを返します。

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

// コールバックはキーと値のペアを持つ配列を返すこともできます：
$names = $result->fetchPairs(fn($row) => [$row->name, $row->age]);
// ['John' => 46, 'Jane' => 21, ...]

fetchField(): mixed

現在の行の最初のフィールドの値を返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

$result = $database->query('SELECT name FROM users');
$name = $result->fetchField(); // 最初の行から名前を読み込みます

fetchList(): ?array

行をインデックス付き配列として返します。これ以上行がない場合はnullを返します。内部ポインタを次の行に進めます。

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

getRowCount(): ?int

最後のUPDATEまたはDELETEクエリによって影響を受けた行数を返します。SELECTの場合、これは返された行数ですが、これは不明な場合があり、その場合メソッドはnullを返します。

getColumnCount(): ?int

ResultSet内のカラム数を返します。

クエリ情報

デバッグ目的で、最後に実行されたクエリに関する情報を取得できます：

echo $database->getLastQueryString();   // SQLクエリを出力します

$result = $database->query('SELECT * FROM articles');
echo $result->getQueryString();    // SQLクエリを出力します
echo $result->getTime();           // 実行時間を秒単位で出力します

結果をHTMLテーブルとして表示するには、次を使用できます：

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

ResultSetはカラムの型に関する情報を提供します：

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

foreach ($types as $column => $type) {
	echo "$column は型 $type->type です"; // 例：'id は型 int です'
}

クエリのロギング

独自のクエリロギングを実装できます。イベントonQueryは、実行された各クエリの後に呼び出されるコールバックの配列です：

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

Nette Documentation Preview