の続きとなります。前回は、Containable ビヘイビアを使って、User からスタートして hasMany しているデータをたどって最大4階層先の HistoryNote レコードを取得しました。
ふと、思ったのです。
HistoryNote から belongsTo をたどって User のデータを取ることも Containable ビヘイビアならできるはず！と。
今回はその記録を投稿いたします。もちろん、できましたわ♪belongsTo をたどるだけでなく、たどった先のモデルが hasMany しているデータも取得できましたの♪
はじめに。ER図、アソシエーションの確認！
前回はER図の左上から始めて右下へとつなげていってデータを取得しました。
今回は、右下から始めて左上へとつなげていくイメージです♪
- User
- hasMany Account, Post, Profile
- Profile
- belongsTo User
- Account
- belongsTo User
- AccountSummary
- belongsTo Account
- Post
- belongsTo User
- hasMany PostAttachment, Tag
- PostAttachment
- belongsTo Post
- hasMany PostAttachmentHistory
- PostAttachmentHistory
- belongsTo PostAttachment
- hasMany HistoryNote
- HistoryNote
- belongsTo PostAttachmentHistory
- Tag
- belongsTo Post
前回の投稿からの続きを想定しております。テーブル構造やレコードデータは前回の投稿の最後に掲載しておりますのでご参考に♪
Containalbe ビヘイビアを試すコントローラーの作成！
前回の投稿の続きですから、各テーブルに対応したモデル、ビュー、コントローラーは作成済みの状態です。そこに、次のコントローラーを作成します。
Controller/DishesController.php
<?php
App::uses('AppController', 'Controller');
class DishesController extends AppController {
public $uses = array('HistoryNote');
/**
* index method
*
* @return void
*/
public function index() {
// コントローラーでビヘイビアを有効
$this->HistoryNote->Behaviors->load('Containable');
// HistoryNote から繋がるすべてのモデルデータを取得
$params = array(
'contain' => array(
'PostAttachmentHistory' => array(
'PostAttachment' => array(
'Post' => array(
'User' => array(
'Profile' => array(
),
'Account' => array( 'AccountSummary'
)
),
'Tag' => array(
)
)
)
)
)
);
$data = $this->HistoryNote->find('all', $params);
debug($data);
}
}
ポイントはもちろん $params の array の内容ですの。また、地味なポイントとしては、$uses のモデル指定だけではなく、Behaviors->load や find 時のモデル指定を間違えないようにすることですの♪
わたくし、最初間違えて Fatal エラーになってしまいましたわ、てへっ。
さて、これで完成ですけれども、エラー回避のためだけにアクションに対応するビューを作成しておきます。
mkdir -p View/Dishes/ touch View/Dishes/index.ctp
取得できたデータの確認！
さて、例えば http://192.168.56.112/cake2/dishes/ にアクセスしますと次のように狙い通りにデータが取得できましたわ♪
array( (int) 0 => array( 'HistoryNote' => array( 'id' => '1', 'post_attachment_history_id' => '1', 'note' => 'post1 history note1', 'created' => '2014-09-12 18:53:32', 'modified' => '2014-09-12 19:10:25' ), 'PostAttachmentHistory' => array( 'id' => '1', 'post_attachment_id' => '1', 'histroy' => 'post1history', 'created' => '2014-09-12 18:51:56', 'modified' => '2014-09-12 18:51:56', 'PostAttachment' => array( 'password' => '*****', 'id' => '1', 'post_id' => '1', 'name' => 'post1attachmentname', 'pinged' => 'post1pinged', 'created' => '2014-09-12 18:50:50', 'modified' => '2014-09-12 18:50:50', 'Post' => array( 'id' => '1', 'user_id' => '1', 'title' => 'post1', 'content' => 'post1 content', 'created' => '2014-09-12 18:46:48', 'modified' => '2014-09-12 18:46:48', 'User' => array( 'password' => '*****', 'id' => '1', 'username' => 'user1name', 'created' => '2014-09-12 18:34:10', 'modified' => '2014-09-12 18:34:10', 'Account' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'email' => 'user1@example.com', 'url' => 'http://example.com/user1/', 'status' => '1', 'created' => '2014-09-12 18:41:22', 'modified' => '2014-09-12 18:41:22', 'AccountSummary' => array( (int) 0 => array( 'id' => '1', 'account_id' => '1', 'first_name' => 'Tom', 'last_name' => 'Cat', 'color' => 'red', 'level' => '1', 'description' => 'user1 Description', 'created' => '2014-09-12 18:43:19', 'modified' => '2014-09-12 18:43:19' ) ) ) ), 'Profile' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'catch_phrase' => 'user1 Catch Phrase', 'self_introduction' => 'user1 Self Introduction', 'created' => '2014-09-12 18:38:26', 'modified' => '2014-09-12 18:38:26' ) ) ), 'Tag' => array( (int) 0 => array( 'id' => '1', 'post_id' => '1', 'name' => 'tag1', 'created' => '2014-09-12 18:47:02', 'modified' => '2014-09-12 18:47:02' ), (int) 1 => array( 'id' => '2', 'post_id' => '1', 'name' => 'tag2', 'created' => '2014-09-12 18:47:10', 'modified' => '2014-09-12 18:47:10' ), (int) 2 => array( 'id' => '3', 'post_id' => '1', 'name' => 'tag3', 'created' => '2014-09-12 18:47:16', 'modified' => '2014-09-12 18:47:16' ) ) ) ) ) ), (int) 1 => array( 'HistoryNote' => array( 'id' => '2', 'post_attachment_history_id' => '1', 'note' => 'post1 history note2', 'created' => '2014-09-12 19:10:19', 'modified' => '2014-09-12 19:10:19' ), 'PostAttachmentHistory' => array( 'id' => '1', 'post_attachment_id' => '1', 'histroy' => 'post1history', 'created' => '2014-09-12 18:51:56', 'modified' => '2014-09-12 18:51:56', 'PostAttachment' => array( 'password' => '*****', 'id' => '1', 'post_id' => '1', 'name' => 'post1attachmentname', 'pinged' => 'post1pinged', 'created' => '2014-09-12 18:50:50', 'modified' => '2014-09-12 18:50:50', 'Post' => array( 'id' => '1', 'user_id' => '1', 'title' => 'post1', 'content' => 'post1 content', 'created' => '2014-09-12 18:46:48', 'modified' => '2014-09-12 18:46:48', 'User' => array( 'password' => '*****', 'id' => '1', 'username' => 'user1name', 'created' => '2014-09-12 18:34:10', 'modified' => '2014-09-12 18:34:10', 'Account' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'email' => 'user1@example.com', 'url' => 'http://example.com/user1/', 'status' => '1', 'created' => '2014-09-12 18:41:22', 'modified' => '2014-09-12 18:41:22', 'AccountSummary' => array( (int) 0 => array( 'id' => '1', 'account_id' => '1', 'first_name' => 'Tom', 'last_name' => 'Cat', 'color' => 'red', 'level' => '1', 'description' => 'user1 Description', 'created' => '2014-09-12 18:43:19', 'modified' => '2014-09-12 18:43:19' ) ) ) ), 'Profile' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'catch_phrase' => 'user1 Catch Phrase', 'self_introduction' => 'user1 Self Introduction', 'created' => '2014-09-12 18:38:26', 'modified' => '2014-09-12 18:38:26' ) ) ), 'Tag' => array( (int) 0 => array( 'id' => '1', 'post_id' => '1', 'name' => 'tag1', 'created' => '2014-09-12 18:47:02', 'modified' => '2014-09-12 18:47:02' ), (int) 1 => array( 'id' => '2', 'post_id' => '1', 'name' => 'tag2', 'created' => '2014-09-12 18:47:10', 'modified' => '2014-09-12 18:47:10' ), (int) 2 => array( 'id' => '3', 'post_id' => '1', 'name' => 'tag3', 'created' => '2014-09-12 18:47:16', 'modified' => '2014-09-12 18:47:16' ) ) ) ) ) ), (int) 2 => array( 'HistoryNote' => array( 'id' => '3', 'post_attachment_history_id' => '1', 'note' => 'post1 history note3', 'created' => '2014-09-12 19:10:46', 'modified' => '2014-09-12 19:10:46' ), 'PostAttachmentHistory' => array( 'id' => '1', 'post_attachment_id' => '1', 'histroy' => 'post1history', 'created' => '2014-09-12 18:51:56', 'modified' => '2014-09-12 18:51:56', 'PostAttachment' => array( 'password' => '*****', 'id' => '1', 'post_id' => '1', 'name' => 'post1attachmentname', 'pinged' => 'post1pinged', 'created' => '2014-09-12 18:50:50', 'modified' => '2014-09-12 18:50:50', 'Post' => array( 'id' => '1', 'user_id' => '1', 'title' => 'post1', 'content' => 'post1 content', 'created' => '2014-09-12 18:46:48', 'modified' => '2014-09-12 18:46:48', 'User' => array( 'password' => '*****', 'id' => '1', 'username' => 'user1name', 'created' => '2014-09-12 18:34:10', 'modified' => '2014-09-12 18:34:10', 'Account' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'email' => 'user1@example.com', 'url' => 'http://example.com/user1/', 'status' => '1', 'created' => '2014-09-12 18:41:22', 'modified' => '2014-09-12 18:41:22', 'AccountSummary' => array( (int) 0 => array( 'id' => '1', 'account_id' => '1', 'first_name' => 'Tom', 'last_name' => 'Cat', 'color' => 'red', 'level' => '1', 'description' => 'user1 Description', 'created' => '2014-09-12 18:43:19', 'modified' => '2014-09-12 18:43:19' ) ) ) ), 'Profile' => array( (int) 0 => array( 'id' => '1', 'user_id' => '1', 'catch_phrase' => 'user1 Catch Phrase', 'self_introduction' => 'user1 Self Introduction', 'created' => '2014-09-12 18:38:26', 'modified' => '2014-09-12 18:38:26' ) ) ), 'Tag' => array( (int) 0 => array( 'id' => '1', 'post_id' => '1', 'name' => 'tag1', 'created' => '2014-09-12 18:47:02', 'modified' => '2014-09-12 18:47:02' ), (int) 1 => array( 'id' => '2', 'post_id' => '1', 'name' => 'tag2', 'created' => '2014-09-12 18:47:10', 'modified' => '2014-09-12 18:47:10' ), (int) 2 => array( 'id' => '3', 'post_id' => '1', 'name' => 'tag3', 'created' => '2014-09-12 18:47:16', 'modified' => '2014-09-12 18:47:16' ) ) ) ) ) ), (int) 3 => array( 'HistoryNote' => array( 'id' => '4', 'post_attachment_history_id' => '2', 'note' => 'post2 history note1', 'created' => '2014-09-12 19:11:05', 'modified' => '2014-09-12 19:11:05' ), 'PostAttachmentHistory' => array( 'id' => '2', 'post_attachment_id' => '2', 'histroy' => 'post2history', 'created' => '2014-09-12 19:07:25', 'modified' => '2014-09-12 19:08:11', 'PostAttachment' => array( 'password' => '*****', 'id' => '2', 'post_id' => '2', 'name' => 'post2attachmentname', 'pinged' => 'post2 attachment name pinged', 'created' => '2014-09-12 19:03:06', 'modified' => '2014-09-12 19:08:59', 'Post' => array( 'id' => '2', 'user_id' => '2', 'title' => 'post2', 'content' => 'post2 content', 'created' => '2014-09-12 18:56:19', 'modified' => '2014-09-12 18:56:19', 'User' => array( 'password' => '*****', 'id' => '2', 'username' => 'user2name', 'created' => '2014-09-12 18:37:03', 'modified' => '2014-09-12 18:37:03', 'Account' => array( (int) 0 => array( 'id' => '2', 'user_id' => '2', 'email' => 'user2@example.com', 'url' => 'http://example.com/user2/', 'status' => '2', 'created' => '2014-09-12 18:41:47', 'modified' => '2014-09-12 18:41:47', 'AccountSummary' => array( (int) 0 => array( 'id' => '2', 'account_id' => '2', 'first_name' => 'John', 'last_name' => 'Dog', 'color' => 'blue', 'level' => '2', 'description' => 'user2 Description', 'created' => '2014-09-12 18:43:57', 'modified' => '2014-09-12 18:43:57' ) ) ) ), 'Profile' => array( (int) 0 => array( 'id' => '2', 'user_id' => '2', 'catch_phrase' => 'user2 Catch Phrase', 'self_introduction' => 'user2 Self Introduction', 'created' => '2014-09-12 18:39:41', 'modified' => '2014-09-12 18:39:41' ) ) ), 'Tag' => array( (int) 0 => array( 'id' => '4', 'post_id' => '2', 'name' => 'tag4', 'created' => '2014-09-12 19:00:57', 'modified' => '2014-09-12 19:00:57' ) ) ) ) ) ) )
また、データを取得するために発行された SQL は次のようになります。1 つの SQL 文ではなく、1モデルに対して1つの SQL を発行しているようですわね。
SELECT `HistoryNote`.`id`, `HistoryNote`.`post_attachment_history_id`, `HistoryNote`.`note`, `HistoryNote`.`created`, `HistoryNote`.`modified`, `PostAttachmentHistory`.`id`, `PostAttachmentHistory`.`post_attachment_id`, `PostAttachmentHistory`.`histroy`, `PostAttachmentHistory`.`created`, `PostAttachmentHistory`.`modified` FROM `cake2db`.`history_notes` AS `HistoryNote` LEFT JOIN `cake2db`.`post_attachment_histories` AS `PostAttachmentHistory` ON (`HistoryNote`.`post_attachment_history_id` = `PostAttachmentHistory`.`id`) WHERE 1 = 1 SELECT `PostAttachment`.`id`, `PostAttachment`.`post_id`, `PostAttachment`.`name`, `PostAttachment`.`pinged`, `PostAttachment`.`password`, `PostAttachment`.`created`, `PostAttachment`.`modified` FROM `cake2db`.`post_attachments` AS `PostAttachment` WHERE `PostAttachment`.`id` = 1 SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`content`, `Post`.`created`, `Post`.`modified` FROM `cake2db`.`posts` AS `Post` WHERE `Post`.`id` = 1 SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`created`, `User`.`modified` FROM `cake2db`.`users` AS `User` WHERE `User`.`id` = 1 SELECT `Account`.`id`, `Account`.`user_id`, `Account`.`email`, `Account`.`url`, `Account`.`status`, `Account`.`created`, `Account`.`modified` FROM `cake2db`.`accounts` AS `Account` WHERE `Account`.`user_id` = (1) SELECT `AccountSummary`.`id`, `AccountSummary`.`account_id`, `AccountSummary`.`first_name`, `AccountSummary`.`last_name`, `AccountSummary`.`color`, `AccountSummary`.`level`, `AccountSummary`.`description`, `AccountSummary`.`created`, `AccountSummary`.`modified` FROM `cake2db`.`account_summaries` AS `AccountSummary` WHERE `AccountSummary`.`account_id` = (1) SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`catch_phrase`, `Profile`.`self_introduction`, `Profile`.`created`, `Profile`.`modified` FROM `cake2db`.`profiles` AS `Profile` WHERE `Profile`.`user_id` = (1) SELECT `Tag`.`id`, `Tag`.`post_id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified` FROM `cake2db`.`tags` AS `Tag` WHERE `Tag`.`post_id` = (1) SELECT `PostAttachment`.`id`, `PostAttachment`.`post_id`, `PostAttachment`.`name`, `PostAttachment`.`pinged`, `PostAttachment`.`password`, `PostAttachment`.`created`, `PostAttachment`.`modified` FROM `cake2db`.`post_attachments` AS `PostAttachment` WHERE `PostAttachment`.`id` = 1 SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`content`, `Post`.`created`, `Post`.`modified` FROM `cake2db`.`posts` AS `Post` WHERE `Post`.`id` = 1 SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`created`, `User`.`modified` FROM `cake2db`.`users` AS `User` WHERE `User`.`id` = 1 SELECT `Account`.`id`, `Account`.`user_id`, `Account`.`email`, `Account`.`url`, `Account`.`status`, `Account`.`created`, `Account`.`modified` FROM `cake2db`.`accounts` AS `Account` WHERE `Account`.`user_id` = (1) SELECT `AccountSummary`.`id`, `AccountSummary`.`account_id`, `AccountSummary`.`first_name`, `AccountSummary`.`last_name`, `AccountSummary`.`color`, `AccountSummary`.`level`, `AccountSummary`.`description`, `AccountSummary`.`created`, `AccountSummary`.`modified` FROM `cake2db`.`account_summaries` AS `AccountSummary` WHERE `AccountSummary`.`account_id` = (1) SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`catch_phrase`, `Profile`.`self_introduction`, `Profile`.`created`, `Profile`.`modified` FROM `cake2db`.`profiles` AS `Profile` WHERE `Profile`.`user_id` = (1) SELECT `Tag`.`id`, `Tag`.`post_id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified` FROM `cake2db`.`tags` AS `Tag` WHERE `Tag`.`post_id` = (1) SELECT `PostAttachment`.`id`, `PostAttachment`.`post_id`, `PostAttachment`.`name`, `PostAttachment`.`pinged`, `PostAttachment`.`password`, `PostAttachment`.`created`, `PostAttachment`.`modified` FROM `cake2db`.`post_attachments` AS `PostAttachment` WHERE `PostAttachment`.`id` = 1 SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`content`, `Post`.`created`, `Post`.`modified` FROM `cake2db`.`posts` AS `Post` WHERE `Post`.`id` = 1 SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`created`, `User`.`modified` FROM `cake2db`.`users` AS `User` WHERE `User`.`id` = 1 SELECT `Account`.`id`, `Account`.`user_id`, `Account`.`email`, `Account`.`url`, `Account`.`status`, `Account`.`created`, `Account`.`modified` FROM `cake2db`.`accounts` AS `Account` WHERE `Account`.`user_id` = (1) SELECT `AccountSummary`.`id`, `AccountSummary`.`account_id`, `AccountSummary`.`first_name`, `AccountSummary`.`last_name`, `AccountSummary`.`color`, `AccountSummary`.`level`, `AccountSummary`.`description`, `AccountSummary`.`created`, `AccountSummary`.`modified` FROM `cake2db`.`account_summaries` AS `AccountSummary` WHERE `AccountSummary`.`account_id` = (1) SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`catch_phrase`, `Profile`.`self_introduction`, `Profile`.`created`, `Profile`.`modified` FROM `cake2db`.`profiles` AS `Profile` WHERE `Profile`.`user_id` = (1) SELECT `Tag`.`id`, `Tag`.`post_id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified` FROM `cake2db`.`tags` AS `Tag` WHERE `Tag`.`post_id` = (1) SELECT `PostAttachment`.`id`, `PostAttachment`.`post_id`, `PostAttachment`.`name`, `PostAttachment`.`pinged`, `PostAttachment`.`password`, `PostAttachment`.`created`, `PostAttachment`.`modified` FROM `cake2db`.`post_attachments` AS `PostAttachment` WHERE `PostAttachment`.`id` = 2 SELECT `Post`.`id`, `Post`.`user_id`, `Post`.`title`, `Post`.`content`, `Post`.`created`, `Post`.`modified` FROM `cake2db`.`posts` AS `Post` WHERE `Post`.`id` = 2 SELECT `User`.`id`, `User`.`username`, `User`.`password`, `User`.`created`, `User`.`modified` FROM `cake2db`.`users` AS `User` WHERE `User`.`id` = 2 SELECT `Account`.`id`, `Account`.`user_id`, `Account`.`email`, `Account`.`url`, `Account`.`status`, `Account`.`created`, `Account`.`modified` FROM `cake2db`.`accounts` AS `Account` WHERE `Account`.`user_id` = (2) SELECT `AccountSummary`.`id`, `AccountSummary`.`account_id`, `AccountSummary`.`first_name`, `AccountSummary`.`last_name`, `AccountSummary`.`color`, `AccountSummary`.`level`, `AccountSummary`.`description`, `AccountSummary`.`created`, `AccountSummary`.`modified` FROM `cake2db`.`account_summaries` AS `AccountSummary` WHERE `AccountSummary`.`account_id` = (2) SELECT `Profile`.`id`, `Profile`.`user_id`, `Profile`.`catch_phrase`, `Profile`.`self_introduction`, `Profile`.`created`, `Profile`.`modified` FROM `cake2db`.`profiles` AS `Profile` WHERE `Profile`.`user_id` = (2) SELECT `Tag`.`id`, `Tag`.`post_id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified` FROM `cake2db`.`tags` AS `Tag` WHERE `Tag`.`post_id` = (2)
これ、アプリが複雑になってレコードも多くなってきますと、Model::query()を使って1発の SQL で取得するように改造しなければパフォーマンスの問題がー！となりそうですわね、、、どうなのかしら。
おわりに
今回の投稿は、確かめるまでもない内容だったかもしれません。だって、Containable ビヘイビアの仕様を考えれば、自明ですものね。。。
でも、慣れていなくて疑心暗鬼だったのですから、きちんと確認できて良かったですの。
以上です。