SQL アプローチ
**********

.[perex]
Nette Database は 2 つのやり方を用意しています。SQL のクエリを自分で書くか（SQL アプローチ）、自動的に生成させるか（[Explorer |explorer]をご覧ください）です。SQL アプローチはクエリを完全に思いどおりにしつつ、それが安全に組み立てられることを保証します。

.[note]
データベースへの接続と設定の詳しい話は [接続と設定 |guide#接続と設定]の章にあります。


基本のクエリ
======

データベースへの問い合わせには `query()` メソッドを使います。これはクエリの結果を表す [ResultSet |api:Nette\Database\ResultSet]オブジェクトを返します。クエリが失敗すると、このメソッドは[例外を投げます|exceptions]。クエリの結果は `foreach` のループで回せますし、[補助のメソッド |#データの取り出し]のどれかを使えます。

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

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

SQL のクエリに値を安全に入れるには、パラメータ化されたクエリを使います。Nette Database ではこれがきわめて簡単で、SQL のクエリのうしろにコンマと値を足すだけです。

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

パラメータが複数あるときは 2 通りの書き方があります。SQL のクエリとパラメータを交互に並べられます。

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

あるいは先に SQL のクエリ全体を書いて、そのあとにすべてのパラメータを並べられます。

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


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

なぜパラメータ化されたクエリを使うことが大事なのでしょうか。それは SQL インジェクションと呼ばれる攻撃から守ってくれるからです。この攻撃では、攻撃者が自分の SQL のコマンドを差し込んで、データベースのデータにアクセスしたり壊したりできてしまいます。

.[warning]
**変数を SQL のクエリに直接入れては決していけません。** いつもパラメータ化されたクエリを使ってください。それが SQL インジェクションから守ってくれます。

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

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

[起こりうるセキュリティリスク |security]も知っておいてください。


クエリの書き方
=======


WHERE の条件
-----------

`WHERE` の条件は連想配列で書けます。キーは列の名前、値は比較するデータです。Nette Database は値の型をもとに、いちばんふさわしい SQL の演算子を自動的に選びます。

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

キーの中で比較の演算子をはっきり指定することもできます。

```php
$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` の値や配列といった特別な場合も自動的に扱います。

```php
$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` 演算子を使います。

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

既定では条件は `AND` 演算子でつながれます。これは [?or のプレースホルダ |#SQL の組み立てのヒント]で変えられます。


ORDER BY の規則
--------------

`ORDER BY` の句は配列で書けます。キーに列を指定し、真偽値で昇順（`true`）か降順（`false`）かを示します。

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


データの挿入（INSERT）
---------------

レコードの挿入には SQL の `INSERT` コマンドを使います。

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

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

パラメータとしては[特別な値 |#特別な値]、たとえばファイル、DateTime オブジェクト、enum 型も渡せます。

複数のレコードを一度に挿入します。

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

複数レコードの INSERT はずっと速くなります。多くの個別のクエリの代わりに、データベースのクエリが 1 つだけ実行されるからです。

**セキュリティに関する注意:** 検証していないデータを `$values` として決して使わないでください。[起こりうるリスク |security#列を安全に扱う]を知っておいてください。


データの更新（UPDATE）
---------------

レコードの更新には SQL の `UPDATE` コマンドを使います。

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

影響を受けた行の数は `$result->getRowCount()` が返します。

`UPDATE` では `+=` と `-=` の演算子を使えます。

```php
$database->query('UPDATE users SET ? WHERE id = ?', [
	'login_count+=' => 1, // login_count を増やします
], 1);
```

レコードがすでにあれば更新し、なければ挿入する例です。`ON DUPLICATE KEY UPDATE` の手法を使います。

```php
$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` コマンドを使います。消された行の数を得る例です。

```php
$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 ならバッククォートで囲みます）。

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

**注意:** `?name` のプレースホルダは検証済みのテーブル名と列名にだけ使ってください。さもないと[セキュリティ上の弱点 |security#動的な識別子]を招きます。

ほかのヒントはふつう指定する必要はありません。Nette は SQL のクエリを組み立てるときに賢く自動判別するからです（表の 3 列めをご覧ください）。とはいえ、たとえば条件を `AND` ではなく `OR` でつなぎたい場面で使えます。

```php
$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')` はファイルの中身をバイナリとして入れます
- 日付と時刻: `DateTimeInterface` のオブジェクトはデータベースの書式に変換されます
- enum 型: `enum` のインスタンスはその値に変換されます
- SQL のリテラル: `Connection::literal('NOW()')` で作られ、そのままクエリに入ります

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

`datetime` のデータ型を本来は持たないデータベース（SQLite や Oracle など）では、`DateTime` と `DateTimeImmutable` のオブジェクトは、[データベースの設定|configuration]の `formatDateTime` の項目で指定された値に変換されます（既定値は `U`、つまり Unix タイムスタンプです）。


SQL のリテラル
---------

生の SQL のコードを値として渡し、文字列として扱われたりエスケープされたりしないようにしたい場合があります。そのために `Nette\Database\SqlLiteral` クラスのオブジェクトを使います。これは `Connection::literal()` メソッドで作ります。

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

あるいは次のようにも書けます。

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

SQL のリテラルはパラメータを含められます。

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

これで面白い組み合わせが作れます。

```php
$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()]にあります。

| `fetch($sql, ...$params): ?Row`       | クエリを実行し、最初の行を `Row` オブジェクトとして、なければ `null` を返します。
| `fetchAll($sql, ...$params): array`   | クエリを実行し、すべての行を `Row` オブジェクトの配列として返します。
| `fetchPairs($sql, ...$params): array` | クエリを実行し、連想配列（キー => 値の組）を返します。
| `fetchField($sql, ...$params): mixed` | クエリを実行し、最初の行の最初の列の値を返します。
| `fetchList($sql, ...$params): ?array` | クエリを実行し、最初の行を添字の配列として、なければ `null` を返します。

例です。

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


`foreach` - 行を順に回す
--------------------

クエリを実行すると [ResultSet|api:Nette\Database\ResultSet]オブジェクトが返され、結果をいくつかの方法で回せます。クエリを実行して行を取り出すいちばん簡単な方法は、`foreach` のループで回すことです。この方法はメモリをもっとも節約します。データを 1 行ずつ取り出し、結果全体を一度にメモリへ読み込まないからです。

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

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

.[note]
`ResultSet` は一度しか回せません。何度も回す必要があるなら、まず `fetchAll()` メソッドなどでデータを配列に読み込まなければなりません。


fetch(): ?Row .[method]
-----------------------

行を `Row` オブジェクトとして返します。行がもうなければ `null` を返します。内部のポインタを次の行へ進めます。

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


fetchAll(): array .[method]
---------------------------

`ResultSet` に残っているすべての行を `Row` オブジェクトの配列として返します。

```php
$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 .[method]
---------------------------------------------------------------------------------------

結果を連想配列として返します。第 1 引数はキーとして使う列、第 2 引数は値として使う列を指定します。

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

第 1 パラメータ（`$key`）だけを渡すと、行全体（`Row` オブジェクト）が値として使われます。

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

キーが重なった場合は最後の行の値が使われます。キーに `null` を使うと、ゼロから始まる添字の配列になり、キーの衝突が起きません。

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


fetchPairs(Closure $callback): array .[method]
----------------------------------------------

代わりに、行ごとに処理するコールバックを渡せます。コールバックはひとつの値か、キーと値の組を返せます。

```php
$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 .[method]
-----------------------------

今の行の最初の列の値を返します。行がもうなければ `null` を返します。内部のポインタを次の行へ進めます。

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


fetchList(): ?array .[method]
-----------------------------

行を添字の配列として返します。行がもうなければ `null` を返します。内部のポインタを次の行へ進めます。

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


getRowCount(): ?int .[method]
-----------------------------

直前の `UPDATE` または `DELETE` のクエリで影響を受けた行の数を返します。`SELECT` のクエリでは結果の行の数を返します。ただしこれは常に分かるとは限らず、その場合このメソッドは `null` を返します。


getColumnCount(): ?int .[method]
--------------------------------

`ResultSet` の列の数を返します。


クエリの情報
======

デバッグのために、最後に実行されたクエリの情報を取り出せます。

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

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

結果を HTML の表として表示するには次のようにします。

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

`ResultSet` は列の型の情報も提供します。

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

foreach ($types as $column => $type) {
	echo "$column is of type $type"; // たとえば 'id is of type int'
}
```


クエリのログ
------

クエリのログを独自に作れます。`onQuery` イベントは、実行されたクエリごとに呼ばれるコールバックの配列です。

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