FuelPHPでテーブル結合するには
FuelPHPが、ウェブサービスのスタートアップに良さそうということで、以下の参考書を使用して、プロジェクトメンバーと勉強しました。
で、開発に入ってから、多:多のテーブルをJOINして取得するところがあったので、調べてみました。
たとえば、学生とクラブを例に。
- 生徒マスタ(students)
student_id | student_name |
---|---|
1 | 佐藤太郎 |
2 | 山田花子 |
- クラブマスタ(club)
club_id | club_name |
---|---|
1 | サッカー部 |
2 | バスケットボール部 |
3 | 園芸部 |
- 学生とクラブの関連テーブル(relations)
club_id | student_id |
---|---|
1 | 1 |
2 | 1 |
SQLで書くとこんな感じです。
select student_name, club_name from students left join relations on students.student_id = relations.student_id left join clubs on relations.club_id = clubs.club_id
ちなみに、取得されるデータは、こうなります。
student_name | club_name |
---|---|
佐藤太郎 | サッカー部 |
佐藤太郎 | バスケットボール部 |
山田花子 | null |
DB::queryでjoinすると、上記のSQLの結果のように、3行取得できますが、これでは、佐藤くんが、分身しているみたいです。
本当は、佐藤くんは、サッカー部とバスケ部を、掛け持ちしているので、佐藤オブジェクトの中に、サッカー部オブジェクトとバスケ部オブジェクトが保持されている形で取得したいです。
そこで、ORMを利用します。
まず、テーブルを作成します。
学生マスタのマイグレーションファイルを作成します。
ちなみに、oil generate migrationの使い方がイマイチわからないので、生成されたファイルを編集してます。
$ oil generate migration create_students student_id:text student_name:string[50] <?php namespace Fuel\Migrations; class Create_students { public function up() { \DBUtil::create_table('students', array( //'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true, 'unsigned' => true), 'student_id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true, 'unsigned' => true), //'student_id' => array('type' => 'text'), 'student_name' => array('constraint' => 50, 'type' => 'varchar'), // ), array('id')); ), array('student_id')); } public function down() { \DBUtil::drop_table('students'); } }
クラブマスタのマイグレーションファイルを作成します。
$ oil generate migration create_clubs club_id:text club_name:string[50] <?php namespace Fuel\Migrations; class Create_clubs { public function up() { \DBUtil::create_table('clubs', array( //'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true, 'unsigned' => true), 'club_id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true, 'unsigned' => true), //'club_id' => array('type' => 'text'), 'club_name' => array('constraint' => 50, 'type' => 'varchar'), //), array('id')); ), array('club_id')); } public function down() { \DBUtil::drop_table('clubs'); } }
学生とクラブの関連テーブルのマイグレーションファイルを作成します。
外部キーも貼ってます。
$ oil generate migration create_relations club_id:text student_id:text <?php namespace Fuel\Migrations; class Create_relations { public function up() { \DBUtil::create_table('relations', array( 'club_id' => array('constraint' => 11, 'type' => 'int', 'unsigned' => true), 'student_id' => array('constraint' => 11, 'type' => 'int', 'unsigned' => true), ), array('club_id', 'student_id'), false, 'InnoDB', 'utf8_unicode_ci', array( array( 'key' => 'club_id', 'reference' => array( 'table' => 'clubs', 'column' => 'club_id' ) ), array( 'key' => 'student_id', 'reference' => array( 'table' => 'students', 'column' => 'student_id' ) ), ) ); } public function down() { \DBUtil::drop_table('relations'); } }
テーブルを作成します。
fuelphp oil refine migrate
config.phpで、ormを有効にしておきます。
/**************************************************************************/ /* Always Load */ /**************************************************************************/ 'always_load' => array( /** * These packages are loaded on Fuel's startup. * You can specify them in the following manner: * * array('auth'); // This will assume the packages are in PKGPATH * * // Use this format to specify the path to the package explicitly * array( * array('auth' => PKGPATH.'auth/') * ); */ 'packages' => array( 'orm', // 'log', ), /** * These modules are always loaded on Fuel's startup. You can specify them * in the following manner: * * array('module_name'); * * A path must be set in module_paths for this to work. */ // 'modules' => array(), /** * Classes to autoload & initialize even when not used */ // 'classes' => array(), /** * Configs to autoload * * Examples: if you want to load 'session' config into a group 'session' you only have to * add 'session'. If you want to add it to another group (example: 'auth') you have to * add it like 'session' => 'auth'. * If you don't want the config in a group use null as groupname. */ // 'config' => array(), /** * Language files to autoload * * Examples: if you want to load 'validation' lang into a group 'validation' you only have to * add 'validation'. If you want to add it to another group (example: 'forms') you have to * add it like 'validation' => 'forms'. * If you don't want the lang in a group use null as groupname. */ // 'language' => array(), ),
モデルを作成します。
モデルクラスは、それぞれ、\Orm\Modelを継承します。
ユーザーモデルには、has_manyを試したパターンのget_students_has_many()メソッドと、many_manyを試したパターンのget_students_many_to_many()メソッドがあります。
<?php /** * Created by IntelliJ IDEA. * User: ryu * Date: 2013/06/20 * Time: 22:54 * To change this template use File | Settings | File Templates. */ class Model_Student extends \Orm\Model { protected static $_properties = array( 'student_id', 'student_name', ); protected static $_primary_key = array('student_id'); protected static $_table_name = 'students'; protected static $_has_many = array( 'relations' => array( 'key_from' => 'student_id', 'model_to' => 'Model_Relation', 'key_to' => 'student_id', 'cascade_save' => true, 'cascade_delete' => false, )); protected static $_many_many = array( 'clubs' => array( 'key_from' => 'student_id', 'key_through_from' => 'student_id', // テーブル間のカラム1は、posts.idと一致する必要があります 'table_through' => 'relations', // アルファベット順に接頭辞なしの複数のmodel双方に 'key_through_to' => 'club_id', // テーブル間のカラム2は、users.idと一致する必要があります 'model_to' => 'Model_Club', 'key_to' => 'club_id', 'cascade_save' => true, 'cascade_delete' => false, ) ); public static function get_students_has_many() { $data = static::find('all', array( 'related' => array( 'relations' => array( 'related' => array('clubs'), ), ), ) ); return $data; } public static function get_students_many_to_many() { $data = static::find('all', array( 'related' => array( 'clubs', ), ) ); return $data; } }
<?php /** * Created by IntelliJ IDEA. * User: ryu * Date: 2013/06/20 * Time: 22:30 * To change this template use File | Settings | File Templates. */ class Model_Relation extends \Orm\Model { protected static $_properties = array( 'club_id', 'student_id', ); protected static $_primary_key = array('club_id', 'student_id'); protected static $_table_name = 'relations'; protected static $_has_one = array( 'clubs' => array( 'key_from' => 'club_id', 'model_to' => 'Model_Club', 'key_to' => 'club_id', 'cascade_save' => true, 'cascade_delete' => false, )); protected static $_belongs_to = array( 'students' => array( 'key_from' => 'student_id', 'model_to' => 'Model_Student', 'key_to' => 'student_id', 'cascade_save' => true, 'cascade_delete' => false, ) ); }
<?php /** * Created by IntelliJ IDEA. * User: ryu * Date: 2013/06/20 * Time: 22:47 * To change this template use File | Settings | File Templates. */ class Model_Club extends \Orm\Model { protected static $_properties = array( 'club_id', 'club_name', ); protected static $_primary_key = array('club_id'); protected static $_table_name = 'clubs'; protected static $_belongs_to = array( 'relations' => array( 'key_from' => 'club_id', 'model_to' => 'Model_Relation', 'key_to' => 'club_id', 'cascade_save' => true, 'cascade_delete' => false, ) ); }
PHPUnitから、実行します。
public function test_get_students_has_many() { echo __METHOD__ . PHP_EOL; $data = Model_Student::get_students_has_many(); print_r($data); echo PHP_EOL; } public function test_get_students_many_to_many() { echo __METHOD__ . PHP_EOL; $data = Model_Student::get_students_many_to_many(); print_r($data); echo PHP_EOL; }
実行結果は、こうなりました。
Test_Model_Student::test_get_students_has_many Array ( [1] => Model_Student Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [student_id] => 1 [student_name] => 佐藤太郎 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [student_id] => 1 [student_name] => 佐藤太郎 ) [_data_relations:protected] => Array ( [relations] => Array ( [[1][1]] => Model_Relation Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 1 [student_id] => 1 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 1 [student_id] => 1 ) [_data_relations:protected] => Array ( [clubs] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) [_original_relations:protected] => Array ( [clubs] => 1 ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) [[2][1]] => Model_Relation Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 2 [student_id] => 1 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 2 [student_id] => 1 ) [_data_relations:protected] => Array ( [clubs] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) [_original_relations:protected] => Array ( [clubs] => 2 ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) ) [_original_relations:protected] => Array ( [relations] => Array ( [0] => [1][1] [1] => [2][1] ) ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) [2] => Model_Student Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [student_id] => 2 [student_name] => 山田花子 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [student_id] => 2 [student_name] => 山田花子 ) [_data_relations:protected] => Array ( [relations] => Array ( ) ) [_original_relations:protected] => Array ( [relations] => Array ( ) ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) )
Test_Model_Student::test_get_students_many_to_many Array ( [1] => Model_Student Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [student_id] => 1 [student_name] => 佐藤太郎 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [student_id] => 1 [student_name] => 佐藤太郎 ) [_data_relations:protected] => Array ( [relations] => Array ( [[1][1]] => Model_Relation Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 1 [student_id] => 1 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 1 [student_id] => 1 ) [_data_relations:protected] => Array ( [clubs] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) [_original_relations:protected] => Array ( [clubs] => 1 ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) [[2][1]] => Model_Relation Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 2 [student_id] => 1 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 2 [student_id] => 1 ) [_data_relations:protected] => Array ( [clubs] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) [_original_relations:protected] => Array ( [clubs] => 2 ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) [clubs] => Array ( [1] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 1 [club_name] => サッカー部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) [2] => Model_Club Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [club_id] => 2 [club_name] => バスケットボール部 ) [_data_relations:protected] => Array ( ) [_original_relations:protected] => Array ( ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) ) ) [_original_relations:protected] => Array ( [relations] => Array ( [0] => [1][1] [1] => [2][1] ) [clubs] => Array ( [0] => 1 [1] => 2 ) ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) [2] => Model_Student Object ( [_is_new:protected] => [_frozen:protected] => [_data:protected] => Array ( [student_id] => 2 [student_name] => 山田花子 ) [_custom_data:protected] => Array ( ) [_original:protected] => Array ( [student_id] => 2 [student_name] => 山田花子 ) [_data_relations:protected] => Array ( [relations] => Array ( ) [clubs] => Array ( ) ) [_original_relations:protected] => Array ( [relations] => Array ( ) [clubs] => Array ( ) ) [_reset_relations:protected] => Array ( ) [_view:protected] => [_iterable:protected] => Array ( ) ) )
生徒がクラブをもっている形になりました。
でも、has_manyもmany_manyも、どっちも、結果は同じか??