CakePHPで3つのテーブルを連結させる。

さて、雑誌(magazines)と出版社(publishers)を Model::$belongsTo で連結させることができましたが、今度は都道府県(states)と出版社を連結させて、3つのテーブルを使います。

実用的な問題として、ひとつのテーブルで収まることはまず無い訳で、ログやら単純なマスターテーブル程度ならばいいのですが、【真面目に】テーブルを正規化していれば、3 つ以上のテーブルが連結するのは当たり前ですッ!!! … だといいなぁ、という希望的観測も含めて(苦笑)。

-- 雑誌テーブル
create table magazines (
 id int not null,
 name varchar(50),
 publisher_id int,
 price int
);
-- 出版社テーブル
create table publishers (
 id int not null,
 name varchar(50),
 state_id int
);
-- 都道府県テーブル
create table states (
 id int not null,
 name varchar(20)
);

# 説明が面倒なので、T100 を magazines に、T200 を publishers に変更しましたｗ

ER 図はこんな感じで、1対多の関係です。

これを出版社を中心にして(cakephp は left join で連結させるので)、

<h2>出版社一覧</h2>
<table>
	<tr>
		<td>id</td>
		<td>name</td>
		<td>Magazine.name</td>
		<td>State.name</td>
	</tr>
<?php foreach($Publishers as $item) : ?>
	<tr>
		<td><?php echo $item['Publisher']['id'] ?></td>
		<td><?php echo $item['Publisher']['name'] ?></td>
		<td><?php echo $item['Magazine']['name'] ?></td>
		<td><?php echo $item['State']['name'] ?></td>
	</tr>
<?php endforeach ; ?>
</table>

のように、都道府県名(State)を表示させようとすると、Publisher モデルクラスに次のように加筆します。

<!-- models/publisher.php -->
<?php
class Publisher extends AppModel
{
	var $name = 'Publisher ';
    var $belongsTo = array(
    	'Magazine' => array(
			'className' => 'Magazine',
			'conditions' => 'Magazine.publisher_id = Publisher.id',
			'order' => 'Publisher.id ASC',
			'foreignKey' => ''),
        'State' => array (				// ここから追加
	    	'className' => 'State',
			'conditions' => '',
			'order' => 'State.id ASC',
			'foreignKey' => 'state_id')
        );
}
?>

関係が

• Publisher->Magazine
• Publisher->State

なので、1段階しか探索をしないため結構簡単にできます。

実行すると素直にデータが取得できます。

SELECT 
 `Publisher`.`id`, 
 `Publisher`.`name`, 
 `Publisher`.`state_id`, 
 `Magazine`.`id`, 
 `Magazine`.`name`, 
 `Magazine`.`publisher_id`, 
 `Magazine`.`price`, 
 `State`.`id`, 
 `State`.`name` 
FROM `publishers` AS `Publisher` 
 LEFT JOIN `magazines` AS `Magazine` 
  ON (`Magazine`.`publisher_id` = `Publisher`.`id`) 
 LEFT JOIN `states` AS `State` 
  ON (`Publisher`.`state_id` = `State`.`id`) 
WHERE 1 = 1 
 ORDER BY `Publisher`.`id` ASC, `State`.`id` ASC 

発行されるクエリを見ると、left join でうまく繋がっています。
難点を云えば、

• left join でつながっているので、雑誌を持たない出版社も表示されている。この場合、inner join にどうすれば切り替えられるのかは不明です。
• 全てのカラムを取得しているので、カラム内のデータが多い場合(varchar(5000)など)は、データ転送に時間がかかる。できれば、index.ctp で使っているカラムだけのクエリを作って欲しいのですが、これもどうやるのか不明です。

そんな感じで、クエリ文を直接書いてしまえば良いのでは？と思ったりしますが、PHP は分かるけど、SQL は分からない(C# でも LINQ は分かるけど SQL が分からない、サブクエリが書けない、という方もいらっしゃるので)場合もあるので、これはこれで良いのかなと。

# 余談ですが、Model::$hasMany を使うとクエリ文がたくさん発行されてしまうという…これって、サブクエリを使えばいいんじゃないの？と思うのですが、パフォーマンス的にはどうなんでしょう。CakePHP 2.0 だと違うのかな？

次に雑誌(Magazine)を中心にして、連結をさせます。

• Magazine->Publiser
• Magazine->Publiers->State

という形で Magazine から State まで 2段階の探索が必要になるわけですが。

<!-- models/magazine.php -->
<?php
class Magazine extends AppModel
{
	var $name = 'Magazine';
	var $belongsTo = array(
    'Publisher' => array(
			'className' => 'Publisher',
			'conditions' => '',
			'order' => 'Magazine.id ASC',
			'foreignKey' => 'publisher_id' ),
    'State' => array(				// ここから追加
			'className' => 'State',
			'conditions' => 'Publisher.state_id = State.id',
			'order' => 'State.id ASC',
			'foreignKey' => '' ),
            );
}
?>

こんな風に conditions に連結の条件を指定してしまいます。
本当は、Model::$recursive を設定するのでしょうが…

recursive :: Model の属性 :: モデル :: CakePHPによる開発 :: マニュアル :: 1.2コレクション :: The Cookbook
http://book.cakephp.org/ja/view/439/recursive
recursiveの正しい理解CakePHP – CPA-LABテクニカル
http://www.cpa-lab.com/tech/081

なんか思ったように動いてくれません。なので、手っ取り早い方法としては、Model::$belongsTo に設定してしまうのがお手軽のなのかなぁと。

このようにモデルで設定しておくと、次のように $item[‘State’][‘name’] で都道府県名が表示できます。

<h2>雑誌一覧</h2>
<table>
	<tr>
		<td>id</td>
		<td>name</td>
		<td>Publisher.name</td>
		<td>State.name</td>
	</tr>
<?php foreach($Magazines as $item) : ?>
	<tr>
		<td><?php echo $item['Magazine']['id'] ?></td>
		<td><?php echo $item['Magazine']['name'] ?></td>
		<td><?php echo $item['Publisher']['name'] ?></td>
		<td><?php echo $item['State']['name'] ?></td>
	</tr>
<?php endforeach ; ?>
</table>

発行されるクエリは以下の通りです。

SELECT 
 `Magazine`.`id`, 
 `Magazine`.`name`, 
 `Magazine`.`publisher_id`, 
 `Magazine`.`price`, 
 `Publisher`.`id`, 
 `Publisher`.`name`, 
 `Publisher`.`state_id`, 
 `State`.`id`, 
 `State`.`name` 
FROM `magazines` AS `Magazine` 
 LEFT JOIN `publishers` AS `Publisher` 
  ON (`Magazine`.`publisher_id` = `Publisher`.`id`) 
 LEFT JOIN `states` AS `State` 
  ON (`Publisher`.`state_id` = `State`.`id`) 
WHERE 1 = 1 
 ORDER BY `Magazine`.`id` ASC, `State`.`id` ASC 

表示が若干違うのは、left join をしているからですね。