📝 ドリル 02 — タグ配列を扱う (PostgreSQL `TEXT[]`)

問題

articles テーブルから タグに "php" を含む記事の title を id 順で出力する。

PHP入門
Laravel入門

タグの格納形式は driver で異なる:

driver	カラム型	中身
PostgreSQL	TEXT[] (配列型)	ARRAY['php','tutorial','beginner']
MySQL	JSON (配列型は無いので JSON で代替)	'["php","tutorial","beginner"]'
SQLite	TEXT (JSON 文字列)	'["php","tutorial","beginner"]'

→ WHERE 句も driver で変わる。

driver	WHERE 句
PostgreSQL	'php' = ANY(tags)
MySQL	JSON_CONTAINS(tags, '"php"')
SQLite	EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'php')

期待される出力

PHP入門
Laravel入門

採点

php scripts/grade.php topics/11-db/ch12-postgres-specific/drill/02-text-array/

デフォルトで SQLite 経路 (json_each) が走って PASS する。

ヒント

• getenv('DOJO_DB_DRIVER') ?: 'sqlite' で分岐
• SQLite の json_each(tags) は JSON 配列を行に展開するテーブル値関数
• PostgreSQL の ANY(配列) は 配列内のどれかと一致 という意味
• 結果は ORDER BY id で並べる
• fetchAll(PDO::FETCH_COLUMN) で title 列だけ取り出すと echo が楽

なぜ配列型を持つ DB は便利か

タグや「複数選択値」を「中間テーブル + JOIN」せずに 1 カラム で表せる。

• 中間テーブル不要 → クエリがシンプル
• GIN インデックスを張れる → WHERE 'php' = ANY(tags) が高速
• 配列演算子 (@>, &&, ||) で集合演算ができる

ただし「タグごとに属性 (created_by など) を持たせたい」なら中間テーブルが必要なので 使い分け が肝心。

PHP入門
Laravel入門

<?php

// TODO:
// 1) $driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
// 2) scripts/shared/db-connect.php の dojo_db_connect() で PDO を取得
// 3) タグに "php" を含む記事の title を id 順で出力する
//    driver 別に WHERE 句を変える:
//      pgsql  : 'php' = ANY(tags)
//      mysql  : JSON_CONTAINS(tags, '"php"')
//      sqlite : EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'php')
// 4) 期待出力:
//      PHP入門
//      Laravel入門

<?php

require __DIR__ . '/../../../../../scripts/shared/db-connect.php';

$driver = getenv('DOJO_DB_DRIVER') ?: 'sqlite';
$pdo = dojo_db_connect();

// driver 別に「タグに 'php' を含む」を表す WHERE 句を組み立てる
$where = match ($driver) {
    'pgsql'  => "'php' = ANY(tags)",
    'mysql'  => "JSON_CONTAINS(tags, '\"php\"')",
    default  => "EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'php')",  // sqlite
};

$sql = "SELECT title FROM articles WHERE {$where} ORDER BY id";
$titles = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);

foreach ($titles as $title) {
    echo $title . "\n";
}