DBICのmany_to_manyでできること
ググっても意外と情報が出てこないmany_to_manyを試してみた。DBはMySQLで。
■ DB作成
DBはこんな感じで作る。
create table users ( id int not null auto_increment primary key, name varchar(255) not null unique ) engine=innodb; create table roles ( id int not null auto_increment primary key, name varchar(255) not null unique ) engine=innodb; create table user_roles ( id int not null auto_increment primary key, user int not null, role int not null, foreign key(user) references users(id) on delete cascade, foreign key(role) references roles(id) on delete cascade ) engine=innodb; insert into users (name) values ('taro'), ('jiro'); insert into roles (name) values ('admin'), ('manager'), ('user'); insert into user_roles (user, role) values (1, 1), (2, 2), (2, 3);
初期状態はこんな感じ。
mysql> select ur.*,u.name user_name,r.name role_name from user_roles ur join users u on u.id=ur.user join roles r on r.id=ur.role order by ur.id; +----+------+------+-----------+-----------+ | id | user | role | user_name | role_name | +----+------+------+-----------+-----------+ | 1 | 1 | 1 | taro | admin | | 2 | 2 | 2 | jiro | manager | | 3 | 2 | 3 | jiro | user | +----+------+------+-----------+-----------+ 3 rows in set (0.00 sec)
■ スキーマの設定
スキーマはDBIC::Schema::Loaderにお任せ。自動生成は
で紹介されてるZIGOROuさんのスクリプトで「差分を直に書く方法」でやってます。
many_to_manyの設定は自動ではやってくれないようなので、Users.pmとRoles.pmにそれぞれこんな感じで直書き。
# in Users.pm __PACKAGE__->many_to_many('roles' => 'user_roles', 'role'); # in Roles.pm __PACKAGE__->many_to_many('users' => 'user_roles', 'user');
引数は「関連ResultSetにひもづけるメソッド名」「対象のテーブル名」「最終的な目的テーブルにひもづく中間テーブルのカラム名」ってとこですかね。
■ テストスクリプト実行
んで、こんなスクリプトを走らせる。
#!/usr/bin/perl use strict; use warnings; use FindBin::libs; use MyApp::Schema; my $schema = MyApp::Schema->connect('dbi:mysql:many_to_many', 'username', 'password'); $schema->storage->debug(1); my $u = $schema->resultset('Users'); my $r = $schema->resultset('Roles'); sub printRoles { my $user = shift or die; printf '[%s] : ', $user->name; map { print $_->name, ' ' } $user->roles; print "\n\n"; } { my $taro = $u->search( { name => 'taro' } )->first; printRoles($taro); $taro->add_to_roles($r->search( { name => 'user' } )); printRoles($taro); $taro->remove_from_roles($r->search( { name => 'admin' } )); printRoles($taro); } print "----\n"; { my $jiro = $u->search( { name => 'jiro' } )->first; printRoles($jiro); $jiro->set_roles( $r->search( { name => [qw/admin user/] } ) ); printRoles($jiro); }
これを実行すると、DBICのSQLも交えてコンソールの出力はこんな感じに。
SELECT me.id, me.name FROM users me WHERE ( name = ? ): 'taro' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : admin SELECT me.id, me.name FROM roles me WHERE ( name = ? ): 'user' INSERT INTO user_roles (role, user) VALUES (?, ?): '3', '1' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : admin user SELECT me.id, me.name FROM roles me WHERE ( name = ? ): 'admin' DELETE FROM user_roles WHERE ( ( ( role = ? ) AND ( user = ? ) ) ): '1', '1' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : user ---- SELECT me.id, me.name FROM users me WHERE ( name = ? ): 'jiro' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '2' [jiro] : manager user SELECT me.id, me.name FROM roles me WHERE ( ( ( name = ? ) OR ( name = ? ) ) ): 'admin', 'user' DELETE FROM user_roles WHERE ( user = ? ): '2' INSERT INTO user_roles (role, user) VALUES (?, ?): '1', '2' INSERT INTO user_roles (role, user) VALUES (?, ?): '3', '2' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '2' [jiro] : admin user
やってることは上から以下の通り。
- taro の roles を表示
- taro に user の role を追加
- taro の admin の role を削除
- jiro の roles を表示
- jiro の roles を admin, user に設定
$user->rolesで、users → user_roles → rolesとテーブルをたどってresultsetが取得できてます。
add_to_roles, remove_from_rolesでそれぞれrolesの追加と削除。
set_rolesは既存のレコードを全て一度deleteした後で改めて指定のデータをinsertしていますね。既存のデータを見て差分のSQLを発行しているわけではない、というのは気に留めておいた方がいいかも。
実行後のDBの様子はこうです。
+----+------+------+-----------+-----------+ | id | user | role | user_name | role_name | +----+------+------+-----------+-----------+ | 4 | 1 | 3 | taro | user | | 5 | 2 | 1 | jiro | admin | | 6 | 2 | 3 | jiro | user | +----+------+------+-----------+-----------+
■ もう一度、続けざまに実行してみる
上記のスクリプトをもう一度走らせると、今度はこうなります。
SELECT me.id, me.name FROM users me WHERE ( name = ? ): 'taro' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : user SELECT me.id, me.name FROM roles me WHERE ( name = ? ): 'user' INSERT INTO user_roles (role, user) VALUES (?, ?): '3', '1' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : user user SELECT me.id, me.name FROM roles me WHERE ( name = ? ): 'admin' DELETE FROM user_roles WHERE ( ( ( role = ? ) AND ( user = ? ) ) ): '1', '1' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '1' [taro] : user user ---- SELECT me.id, me.name FROM users me WHERE ( name = ? ): 'jiro' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '2' [jiro] : admin user SELECT me.id, me.name FROM roles me WHERE ( ( ( name = ? ) OR ( name = ? ) ) ): 'admin', 'user' DELETE FROM user_roles WHERE ( user = ? ): '2' INSERT INTO user_roles (role, user) VALUES (?, ?): '1', '2' INSERT INTO user_roles (role, user) VALUES (?, ?): '3', '2' SELECT role.id, role.name FROM user_roles me JOIN roles role ON ( role.id = me.role ) WHERE ( me.user = ? ): '2' [jiro] : admin user
add_to_rolesがもう一度実行されて、taroのrolesが「user user」になってしまいました。
remove_from_rolesは対象のレコードはもうないけど、delete文を発行しているようです。
set_rolesは全てdeleteしてからinsertし直すので、結果は変わっていませんね。
どれも共通して、現状に関わらず指定のSQLを問答無用で発行します（一応mysqlのクエリログも確認したけど、確かに発行されてました）。
そして、この時点でのDBの様子はこう。
+----+------+------+-----------+-----------+ | id | user | role | user_name | role_name | +----+------+------+-----------+-----------+ | 4 | 1 | 3 | taro | user | | 7 | 1 | 3 | taro | user | | 8 | 2 | 1 | jiro | admin | | 9 | 2 | 3 | jiro | user | +----+------+------+-----------+-----------+
