CakePHP1.3.6：テーブルのジョイン(join)

php cakephp

ORM(Object-Relational Map、もしくは、Object-Relational Mapper) モジュールでは、エンティティー間のリレーションが定義できることができるのが、一般的だ。話が脇にそれるが、先日、会社で教育をやったらエンティティー（Entity）という言葉に違和感を覚える人が多い（私もそうだったなぁ）。PHPのコーディングに特化して考えれば、ORMでいうエンティティーはテーブルと考えればいい。

CakePHPのモデルもこの機能をもっていて、モデル間で関連を定義することで、cakeが勝手にjoinの結果を返してくれる。

今回は、これまでの顧客テーブル（customers）に追加して、新しく売上(sales)テーブルを追加して、以下のような売上一覧を作成してみる。salesテーブルのDDLは以下。

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL COMMENT '顧客ID',
  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '商品名',
  `amount` int(11) NOT NULL COMMENT '金額',
  `purchase_date` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '購入年月日',
  `memo` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'メモ',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'タイムスタンプ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='売上' AUTO_INCREMENT=1 ;

上の画面のデータは、salesテーブルにcustomerテーブルをLEFT JOINすれば得られる。

関係性を定義する場合にも、CakePHPの規約がある。

  • 外部キーとなるフィールド名は、アンダースコア記法で「モデル名_モデル名のキー」とする。

salesテーブルの「顧客ID」が外部キーになっていて、相手は、モデルcustomerのキーidである。この場合、customer_idがフィールド名となる。

モデルの作成

/app/modelsにsale.phpを以下のように定義する。

<?php 
class Sale extends AppModel {
	public $name = 'Sale';
	
	public $belongsTo = 'Customer'; 
	
	public $validate = array(
				'customer_id'=>array(
							'rule' => 'notEmpty',
							'message' =>'入力してください'),
				'item_name'=>array(
							'rule' => 'notEmpty',
							'message' =>'入力してください'),
				'amount'=>array(
							'rule' => 'numeric',
							'required' => true,
							'message' =>'数字を入力してください'),
				'purchase_date'=>array(
							'rule' => 'date',
							'required' => true,
							'message' =>'yyyy-mm-ddの形式で日付を入力してください')
	);
	
}
?>

レフトジョインするための定義は、

	public $belongsTo = 'Customer';

だけで済む。これは、外部キーの命名を規約に沿って行ったからで、そうでない場合には、以下のように細かく定義することもできる。

	public $belongsTo = array(
		"Customer"	=> array(
			'className' => 'Customer',
			'conditions' => '',
			'order' => '',
			'dependent' => false,
			'foreignKey' => 'customer_id'
		)
	);

組み込みのdateダリデータを使ってみたが、yyyy-mm-ddの形式でカレンダーの不正をチェックできる。たとえば、4月31日など入れるとエラーになって戻ってくる。便利。

データの登録

テスト用のデータを１つ登録する。
この際、scaffoldでデータを登録したのだが、外部キーが「空っぽ」のselectボックスになってしまう問題がある模様。未入力でも登録できてしまうので、適当に登録後、SQLでcustomer_idを更新した。

ビューの作成

/app/viewsにsalesディレクトリを作成し、index.ctpを作成する。

<h2>売上の一覧</h2>
<br>
商品名で絞り込みをします。
<?php 
// 絞り込み用フォーム
echo $form->create(null,array('type'=>'post','action'=>'.'));
echo $form->text('Sale.name', array('size' => 10));
echo $form->end('送信');
?>

<?php 
// ページネーション
echo $paginator->numbers(
	array(
		'before'=>$paginator->hasPrev() ? $paginator->first('<<').' ' : '',
		'after'=>$paginator->hasNext() ? ' '.$paginator->last('>>') : '',
		'modulus'=>4,
		'separator'=>' '
	)
);
?>

<table>
<tr>
	<th><?php echo $paginator->sort('ID','Sale.id') ?></th>
	<th><?php echo $paginator->sort('商品名','item_name') ?></th>
	<th><?php echo $paginator->sort('顧客名','Customer.name') ?></th>
	<th><?php echo $paginator->sort('売上金額','amount') ?></th>
	<th><?php echo $paginator->sort('売上日','purchase_date') ?></th>
	<th><?php echo $paginator->sort('更新日','Sale.timestamp') ?></th>
</tr>
<?php 
	foreach ($data as $arr){
		echo '<tr>';
		echo '<td>'.$html->link($arr['Sale']['id'],
  				array('controller'=>'sales', 'action'=>'show',$arr['Sale']['id'])).'</td>';
		echo "<td>{$arr['Sale']['item_name']}</td>";
		echo "<td>{$arr['Customer']['name']}</td>";
		echo "<td>{$arr['Sale']['amount']}</td>";
		echo "<td>{$arr['Sale']['purchase_date']}</td>";
		echo "<td>{$arr['Sale']['timestamp']}</td>";
		echo '</tr>';
	}
?>
</table>
<?php
echo $html->link('登録',
  array('controller'=>'sales', 'action'=>'add'));
?>

コントローラー

/app/controllersに、以下のようにsales_controller.phpを作成する。

<?php
require_once '../vendors/MyConverter.class.php';

class SalesController extends AppController{
	public $name = 'Sales';
	public $layout = 'myznala';
	public $uses = array('Sale','Customer');
	
	/*
	 * Paginatorの定義
	 */
	public $paginate = array(
		'page'=>1,
		'conditions'=>array(),
		'fields'=>array(),
		'order'=>array('Sale.timestamp'=>'desc'),
		'limit'=>5,
		'recursive'=>0
	);
	
	/**
	 * 
	 * 初期画面（一覧表示）
	 */
	function index(){
		$this->set('title_for_layout', "売上の一覧");
		$req=null;
		if(!empty($this->data)){
			//サニタイズ
			$req = MyConverter::getRequestParams($this->data["Sale"]);
			//絞り込みの場合には、コンディションを書き換える。
			$this->paginate['conditions'] = array('Sale.item_name like ?' => array("%{$req["name"]}%"));
		}
		$data = $this->paginate();
		
		$this->set('data',$data);
	}
	
	
}
?>

このとき、CakePHPが発行するSQLは以下のようになり、ジョインされていることがわかる。

SELECT `Sale`.`id`, `Sale`.`customer_id`, `Sale`.`item_name`, `Sale`.`amount`, `Sale`.`purchase_date`, `Sale`.`memo`, `Sale`.`timestamp`, `Customer`.`id`, `Customer`.`name`, `Customer`.`zip`, `Customer`.`address`, `Customer`.`tel`, `Customer`.`mobile`, `Customer`.`mail`, `Customer`.`memo`, `Customer`.`timestamp` FROM `sales` AS `Sale` LEFT JOIN `customers` AS `Customer` ON (`Sale`.`customer_id` = `Customer`.`id`) WHERE 1 = 1 ORDER BY `Sale`.`timestamp` desc LIMIT 5

取得されるデータをprint_rすると、以下のようになっている。

Array ( [0] => 
Array ( [Sale] => 
  Array ( 
    [id] => 1 
    [customer_id] => 1 
    [item_name] => おまんじゅう 
    [amount] => 101 
    [purchase_date] => 2010-12-01 
    [memo] => 
    [timestamp] => 2010-12-10 12:29:48 ) 
   
       [Customer] => 
  Array ( 
    [id] => 1 
    [name] => 山田太郎 
    [zip] => 000-0000 
    [address] => 東京都新宿区xxx 1-1-1 
    [tel] => 03-0000-0000 
    [mobile] => 090-0000-0000 
    [mail] => taro.yamada@localhost.localdomain 
    [memo] => 山田商事社長です。 
   [timestamp] => 2010-12-06 21:13:18 ) 
) )