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 NOT' => [],                 // 飛ばされます
]);
// WHERE `name` != 'Laptop' AND `category_id` NOT IN (1, 2, 3) AND `description` IS NOT NULL

既定では条件は AND 演算子でつながれます。これは ?or のプレースホルダで変えられます。

ORDER BY の規則

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

$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 など）、ID を生成するシーケンスの名前をパラメータとして $database->getInsertId($sequenceId) のように指定する必要があります。

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

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

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

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

セキュリティに関する注意: 検証していないデータを $values として決して使わないでください。起こりうるリスクを知っておいてください。

データの更新（UPDATE）

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

// ひとつのレコードを更新します
$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 のプレースホルダは、テーブルや列の名前を動的にクエリに入れるのに使います。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') はファイルの中身をバイナリとして入れます
• 日付と時刻: DateTimeInterface のオブジェクトはデータベースの書式に変換されます
• enum 型: enum のインスタンスはその値に変換されます
• SQL のリテラル: Connection::literal('NOW()') で作られ、そのままクエリに入ります

$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 のオブジェクトは、データベースの設定の 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*() メソッドの詳しい説明は下にあります。

例です。

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

foreach – 行を順に回す

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

$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

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

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

第 1 パラメータ（$key）だけを渡すと、行全体（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(); // 最初の行から name を読み込みます

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 is of type $type"; // たとえば 'id is of type 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');
	}
};