Friction River Software - CakePHP5入門【CakePHP5基礎編⑦】データベース操作③

Ｂ美

今回が「データベース操作」の最後よ

Ａ子

長かった…

Ｃ菜

私は面白かったですよ～

あ、一つ質問なんですが、前回「結合けつごう」という言葉が出てきたのに、その説明が無かったです～

Ｂ美

関係データベースって、複数のテーブルを連携させて取り扱うことができるの

例えば、「顧客」表（マスターテーブル）があって、その主キーである「顧客コード」が「売上」表（トランザクションテーブル）に存在するとしましょう
このときテーブルは次の二つ

【顧客】

顧客コード	顧客名	顧客住所	顧客電話番号
1001	○○商事	東京都港区…	03-xxxx-xxxx
1002	△△商会	大阪府大阪市…	06-xxxx-xxxx
1003	株式会社××	福岡県北九州市…	093-xxx-xxxx

【売上】

伝票番号	売上日付	商品名	顧客コード
T123	2024/01/11	X	1001
T124	2024/01/11	Y	1003
T125	2024/01/12	Z	1001
T126	2024/01/13	X	1002
T127	2024/01/13	Y	1003
T128	2024/01/13	Z	1001

【注意点】
これって「1枚の伝票に1つの商品しか書けない」という制約下で作ってるんだけど、実際の伝票はそうじゃないからね
あと、商品についても「商品」表（マスターテーブル）を作るべきなんだけど、そうしてないから…

Ｃ菜

もしもテーブルを分けなかった場合、こうなるってことですよね～？

【売上一覧】

伝票番号	売上日付	商品名	顧客コード	顧客名	顧客住所	顧客電話番号
T123	2024/01/11	X	1001	○○商事	東京都港区…	03-xxxx-xxxx
T124	2024/01/11	Y	1003	株式会社××	福岡県北九州市…	093-xxx-xxxx
T125	2024/01/12	Z	1001	○○商事	東京都港区…	03-xxxx-xxxx
T126	2024/01/13	X	1002	△△商会	大阪府大阪市…	06-xxxx-xxxx
T127	2024/01/13	Y	1003	株式会社××	福岡県北九州市…	093-xxx-xxxx
T128	2024/01/13	Z	1001	○○商事	東京都港区…	03-xxxx-xxxx

「顧客名」「顧客住所」「顧客電話番号」の値がめっちゃ重複してます～

Ａ子

なるほどねぇ

「売上一覧」表のように表が一つの場合、もしも引っ越しして住所が変わったりしたら大変なことになるのかー
（「顧客」表があれば、変更箇所は一ヶ所だけだもんね）

Ｂ美

そういうこと

適切に表（テーブル）を分割することを「正規化」と呼びます
そして「正規化」による適切な「データベース設計」こそが、データベースエンジニアの腕の見せ所でもあるってわけ

Ｃ菜

それで「結合けつごう」というのは～？

Ｂ美

例えば「商品名Zを購入した顧客の名前を調べたい」と思った場合、二つの方法があるの

まずは「売上」表から商品名が「Z」であるレコードを検索して、そのレコードの顧客コードが「1001」であることを知るわけ
次に、その値「1001」を使って「顧客」表を検索すれば、顧客名が「○○商事」であると分かるわよね？

Ａ子

二回に分けてSQLを実行するの？

Ｂ美

いいえ
「副問い合わせサブクエリー」という方法を使えば、一度で（一つのSQL文で）検索できるわよ

Ｃ菜

あ、ということは、もう一つの方法が「結合」を使うやり方なんですね～？

Ｂ美

その通り！

上でＣ菜が示した「売上一覧」表（めっちゃ重複データがあるやつ）があるわよね？
あれって、私が書いた「顧客」表と「売上」表の二つを「結合ジョイン」したものとも言えるわけ

その結合した表を商品名「Z」で検索すれば、一発で「○○商事」という顧客名が得られるでしょ？

Ａ子

確かに…

あ、さっきは（話の腰を折るのもなんだから）質問を控えてたんだけどさ
「マスターテーブル」とか「トランザクションテーブル」って何？

Ｂ美

「顧客」表や「商品」表のように基本的なデータを格納するのが「マスターテーブル」
「売上」表や「取引」表のように日々データが増加していくテーブルが「トランザクションテーブル」よ

【参照頻度：高　更新頻度：低】なのが「マスターテーブル」
【参照頻度：低　更新頻度：高】なのが「トランザクションテーブル」とも言えるかな

Ａ子

なるほどねぇ
なんとなく分かったわ

Ｃ菜

それで～
本題に戻りますけど、「CakePHP」ではテーブルを「結合」できないんですか～？

Ｂ美

もちろんできるわよ
ただ、その方法は二種類あるの

一つは、まさに「join」メソッドを使う方法
もう一つは、「contain」メソッドを使う方法
（「contain」メソッドを使用する場合は、テーブルのクラス内で「belongsTo」メソッドや「hasMany」メソッドを記述する必要あり）

Ａ子

「join」のほうが簡単っぽいね

Ｂ美

いいえ、逆よ

「join」を使う場合は、Controllerコントローラー内に面倒な記述をしなきゃいけないのに対して
「contain」のほうはめっちゃシンプルに書けるの
（面倒なことは、テーブルのクラス内に記述すれば良いじゃん…って考え方ね）

Ｃ菜

どちらにしても面倒そうですね～

Ｂ美

それがそうでもないのよねー
SQLの「CREATEクリエイト TABLEテーブル」文を実行する際、主しゅキー（PRIMARYプライマリー KEYキー）を設定するのは必須なんだけど、外部キー（FOREIGNフォーリン KEYキー）を設定することもできるの

外部キーを設定したテーブルに対して「bake model」コマンドを使ってModelモデルクラス（テーブルのクラス）を作ると、「結合」に必要な「belongsTo」メソッドや「hasMany」メソッドの記述を自動生成してくれるのよね
（そうすればControllerコントローラー内で、シンプルに「contain」メソッドを呼ぶだけで済むってわけ…超簡単！）

Ａ子

「主キー」って一つのレコードを特定するための項目
言い換えれば、絶対に重複しない値を格納してる項目…ってことよね？

ただ、「外部キー」ってのが分からん！
いったい何なのよ？

Ｂ美

「ある表の主キーが他の表の主キー以外の項目として存在するとき、他の表の中のその項目を外部キーと呼ぶ」
…と定義されているわね

Ａ子

うがぁー
さっぱり分からん！

Ｂ美

具体例で考えると簡単よ

ある表（「顧客」表）の主キー（「顧客コード」）が他の表（「売上」表）の主キー（「伝票番号」）以外の項目として存在するとき、他の表（「売上」表）の中のその項目（「顧客コード」）を外部キーと呼ぶ
…ってこと

要するに、「顧客」表と「売上」表を連携させるためには両方の表に「顧客コード」が必要じゃん？
そのとき「顧客コード」って「顧客」表では「主キー」、「売上」表では「外部キー」と呼ばれるってだけの話よ

Ｃ菜

二つの表を結びつけるための項目が「外部キー」ってことでしょうか～？

Ｂ美

正解！

ちなみに「結合」は、「主キー」項目と「外部キー」項目を「＝イコール」で結ぶことで行われるわ
（これはSQL文の話…「CakePHP」では先ほど話に出た「belongsTo」メソッドを使います）

Ａ子

そのあたりの実践はやらないの？

具体的なコードが無いと、いまいち分からん！

Ｂ美

うーん、面倒くさい！

とりあえず現時点においては省略します
（先々コラム的な話として、やるかもしれないけど…）

Ｃ菜

今回の本題は「CakePHP」におけるSQL文の直接実行でしたもんね～？

Ｂ美

そういうこと

さて、それじゃ今回の授業を始めます
まずは「MATE端末」を開いて次のように入力してね

cd html/testapp[Enter]
bin/cake bake controller SqlTest[Enter]

Ｃ菜

「testapp/src/Controller」の中に「SqlTestController.php」ができました～

Ｂ美

それじゃ、その「SqlTestController.php」を開いて「index」メソッド以外を削除してね
（要するに、下記の状態にします）

Ｂ美

そうしたら、「index」メソッドの中にこう記述してね

public function index()
{
　　$connection = ConnectionManager::get('default');
　　$sql = "SELECT id,remarks,counter FROM counters";
　　$results = $connection->execute($sql)->fetchAll('assoc');
　　$this->set(compact('results'));
}

あと重要なのが、上のほうにある「namespace …」の下に次の一文を記述すること！
（これって「index」メソッド内で使っている「ConnectionManager」の場所はここですよー…って意味ね）

use Cake\Datasource\ConnectionManager;

Ｃ菜

Viewビューファイルはコマンド（bake template）で作りますか～？

Ｂ美

いいえ

「testapp/templates」を開いて「SqlTest」ディレクトリを新規作成しましょう
もちろん、その中に「index.php」も作ってね

Ｂ美

んで、その「index.php」の中身がこうよ
（頑張って入力してね…コピペでも良いけど）

注意点だけど、「$value->id」という書き方はできないからね

Ｂ美

完成したら、ブラウザで確認してちょうだい

URLは「localhost/testapp/sqlTest」よ

Ｃ菜

Viewビューファイルの中で「$value->id」のように「->アロー」を使えないのはなぜですか～？

まさか検索結果がオブジェクトじゃないとか～？

Ｂ美

実は私もよく知らないのよね（苦笑）
おそらくだけど、検索結果は「連想配列」の「配列」なんだと思う

まぁ良いじゃない
気にしない、気にしない（笑）

Ａ子

プログラマって理系よね？

そんないいかげんなことで良いの？

Ｂ美

うーん、決して理系ばかりってことはないと思う
（文系プログラマも多いわよ）

てか、プログラムなんて動きゃ良いのよ、動きゃ！

Ｃ菜

結構いいかげんです～

Ｂ美

そんなことはともかく（苦笑）

さっきの「index」メソッドを次のように書き換えてね
（赤と緑の色で示したところが変更した箇所ね）

public function index()
{
　　$connection = ConnectionManager::get('default');
　　$sql =<<<EOF
　　　　SELECT id,remarks,
　　　　　　CASE
　　　　　　　　WHEN counter = 0 THEN 'ゼロ'
　　　　　　　　WHEN counter >= 1 AND counter <= 10 THEN '少'
　　　　　　　　WHEN counter >= 11 AND counter <= 99 THEN '中'
　　　　　　　　WHEN counter >= 100 THEN '多'
　　　　　　END AS counter
　　　　FROM counters;
　　EOF;
　　$results = $connection->execute($sql)->fetchAll('assoc');
　　$this->set(compact('results'));
}

Ｃ菜

「counter」の列って最初は数字だったのに、文字に変わりましたよ～

これってデータベース自体が更新されちゃったんですかぁ～？

Ｂ美

違う違う
Viewビュー側には（「少」とか「多」のような）文字列が渡されたけど、データベースのデータ自体は更新されてないの

「counter」の値によって出力されるデータを変更することができたのは、SQLの「CASE」式を使ったからよ

Ａ子

「WHEN」のあとに条件式を書いて、それが真なら「THEN」以下を実行する…ってこと？

Ｂ美

そう、その通り
Ａ子もなかなかやるじゃん

まぁ、複雑なSQL文を書かないと「SQL文の直接実行」という行為を理解しづらいからね
（別に「CASE」式を覚えなくても良いわよ…単なるサンプルなので（笑））

ちなみに「AS ○○」というのは、その項目に「○○」という別名（エイリアス）を付けます…って意味よ
（Viewビューファイルを修正したくなかったから、同じ「counter」という名前にしただけ）

Ｃ菜

それよりもまたまた変な書き方が登場してます～

「$sql =<<<EOF」って何なんですか～？

Ｂ美

それはPHP言語の文法の一つなんだけど、「ヒアドキュメント」と言います
複数行の文字列を変数に代入したいってときに使うのよ

$変数名 =<<<文字列
　　・・・
　　（複数行）
　　・・・
文字列;

という形式ね

Ａ子

その「文字列」の名前って、さっきのコードでは「EOF」だったよね

必ずその名前にするの？

Ｂ美

んなわけあるかい！

何でも良い（「ABC」でもOK）んだけど、「EOF」「EOD」「EOT」なんかを使う人が多いみたい
（私は「EOF」ばっか使うけど…）

あと、これは本当にどうでもいい話なんだけど、それらの文字列って「End Of File」「End Of Document」「End Of Text」の頭文字を並べたものだからね
（てか、意味的に「EOF」はおかしいんだけど…（笑））

Ｃ菜

要するに、同じ「文字列」で挟まれた部分が変数に代入されるってわけですね～？

Ｂ美

そういうこと

あ、「ヒアドキュメント」を使うときに注意しなきゃいけないことが一つ
ダブルクォートで挟まれた文字列中に変数や配列の名前を書くと「変数展開」される…って話、憶えてる？

Ｃ菜

あー、ありましたね～

ちょっと面倒なやつです～

Ｂ美

「ヒアドキュメント」では、その「変数展開」が実行されます（苦笑）

Ａ子

えぇー、それって無効にはできないの？

Ｂ美

もちろんできるわよ
その方法は超簡単で、最初の「文字列」をシングルクォートで囲めば良い（'EOF'）だけなの

具体的には

$sql =<<<'EOF'
　　SELECT id,remarks,
　　　　CASE
　　　　　　WHEN counter = 0 THEN 'ゼロ'
　　　　　　WHEN counter >= 1 AND counter <= 10 THEN '少'
　　　　　　WHEN counter >= 11 AND counter <= 99 THEN '中'
　　　　　　WHEN counter >= 100 THEN '多'
　　　　END AS counter
　　FROM counters;
EOF;

…ってことね
（最後の「文字列」部分（EOF）については、シングルクォートを付けないように！）

Ｃ菜

でもSQL文を書くときは、プログラムで事前に処理した値を元にデータベースを検索するなんてことが、よくありそうですよ～

そういうときは「変数展開」されたほうが便利なんじゃないですか～？

Ｂ美

大正解！
こんな感じでSQL文の中に変数の値を埋め込むことは、よくやるかな

$value = 100;

$sql =<<<EOF
　　SELECT id,remarks,counter
　　FROM counters;
　　WHERE counter > {$value};
EOF;

Ａ子

あー、たしかにちょっと便利そうかも…（苦笑）

そういえば、さっきの「CASE」式ってさ
（SQLじゃなくて）Modelモデルのクラスを使って、実現できないの？

Ｂ美

当然できるんだけど、めっちゃ複雑になるわね

なので、例となるコードは省略
（というか、私が書きたくない（笑））

Ｃ菜

さっきの例（counter列の値として「少」や「多」を表示するやつ）だったら、Viewビューファイルの中で条件分岐のコードを書けば良いんじゃないでしょうか～？

Ｂ美

お、よく分かってるじゃん

私だったら、そうするかな
（まぁ、人それぞれお好きなように…って感じだけど）

プログラムって「正解は一つじゃない」からね
いえ「プログラマの数だけ正解がある」と言っても過言ではないわ

Ａ子

うーん、なるほどねぇ

さっき言ってた「プログラムなんて動きゃ良い」ってのは、そういうことか…

Ｂ美

さて、それじゃ今回はここまでよ

「CakePHP」の基礎的な話はいったん終わって、次回からは実用的な「Webアプリケーションシステム」を作っていきます

Ｃ菜

いよいよですね～

楽しみです～

Ａ子

あれ？
データベーステーブルに対する「レコードの追加」「レコードの更新」「レコードの削除」なんかをやってないような気が…

Ｂ美

そのあたりも次回以降、ぼちぼちやっていく予定よ

基礎的な話ばかりだと面白くないからね（苦笑）