つよく、やさしく、泥臭く生きていくブログ

日常とポエムと、ときどき技術

DBのリレーション

人と人の関係も難しいし、DBのリレーションも分からない。
ので基本を確認することにした。
だいたい社内のそういうのって既に良い感じに使えるように作られてて、それを使うかコピペして微修正するだけなので、細かいことはよく分かってなかった。

特に見たいのはリレーションのところで、SQLでいうとjoinの動作?なところ。

  • belongs_to
  • might_have

のあたり

この記事はPerlDBIx::Classなんかを使う感じのやつです。

準備

  • とりあえずMySQLで適当なテーブルを作ります。
  • 人の関係はよく分からないのでプリキュアで例示します。
mysql> use PRECURE;
mysql> SELECT * FROM Series;
+-----------+-----------+
| series_id | name      |
+-----------+-----------+
|         1 | hutariha  |
|         2 | max heart |
|         9 | smile     |
|        15 | hug       |
+-----------+-----------+

mysql> SELECT * FROM Precure;
+------------+-----------+----------------+
| precure_id | series_id | name           |
+------------+-----------+----------------+
|          1 |         1 | black          |
|          2 |         1 | white          |
|          3 |         2 | shiny luminous |
|         55 |        15 | amour          |
|        102 |        13 | mofurun        |
+------------+-----------+----------------+

  • 穴抜けがあるのはあえて。 全部は面倒くさい
  • このテーブルでは問題があるけどとりあえずスルー。

    • ブラックとホワイトはMHにも含まれてるはずだがそれが表現できない
    • レギュラープリキュアに含まれないキュアモフルンのプリキュアIDを100番台に設定しているが、10数年後にはレギュラープリキュアが100人を超えるので困る。
  • とりあえず使えるか確認

# DB接続
use My::Schema;

my $schema =  My::Schema->connect(
    "DBI:mysql:$database",
    $user,
    $password
) or die "cannot connect to MySWL: $DBI::errstr";

# 一人目のプリキュアの名前を取得してみる
my $precure_rs =  $schema->resultset('Precure');
my $precure = $precure_rs->search({
    precure_id => 1,
})->first;

print $precure->name;    # black

基本

  • プリキュア名からシリーズタイトルを取得する
    • 地道にやるとこうなる
# 名前でPrecureテーブルを検索
my $precure = $precure_rs->find({
    name => 'black',
});

# 検索結果の情報からシリーズIDでSeriesテーブルを検索
my $series = $series_rs->find({
    series_id => $precure->series_id,
});

say $series->name;    # hutariha
  • シリーズタイトルからそのシリーズのプリキュアを取得する
    • 地道にやるとこうなる
my $series = $series_rs->find({
    name => 'hutariha',
});

my $precure = $precure_rs->search({
    series_id => $series->series_id,
});

while (my $rs = $precure->next) {
    print $rs->name, "\n";
}

#----
# black
# white

さらにプリキュア名からそのプリキュアの所属するシリーズの全プリキュア名を取るってなると手間が多い

リレーション

  • リレーションを設定すると、前述のことがさらりとかけるようになる。
  • シリーズタイトルからそのシリーズのプリキュアを取得する
my $precure = $series_rs->find({
   name => 'hutariha'
})->precure;

while (my $rs = $precure->next) {
    print $rs->name, "\n";
}
# ----
# black
# white
my $precure = $precure_rs->find({
    name => 'white'
})->series->precure;

while (my $rs = $series->next) {
    print $rs->name, "\n";
}
# ----
# black
# white

リレーションの書き方

スキーマ情報を書いてるファイルにこんな風に追記する。

  • Series.pm
__PACKAGE__->has_many(  # SeriesはPrecure をたくさん持ってる
    # 'precure' はこのリレーションを使うときの名前なので何でもいい
    'precure' => 'DB3::DBIC::Schema::Result::Precure', 
    # 2つのテーブルを紐付ける条件。
    # foreign はここではPrecure。selfがSeries
    {'foreign.series_id' => 'self.series_id'}
);
  • Precure.pm
__PACKAGE__->belongs_to(    # Precureはシリーズに属する
    'series' => 'DB3::DBIC::Schema::Result::Series',
    { 'foreign.series_id' => 'self.series_id'}
);
  • 1対多の関係
    一つのシリーズに複数のプリキュアが居る。
    一人のプリキュアは一つのシリーズに属する。(今はMHや5GoGoは考えない)
    • has_many
      • シリーズ has_many プリキュア
      • これで返ってくるのはResultSetクラス??。many持ってるので当然ながら複数で返ってくる。
      • なので、->first->name したり、->nextでイテレートする。
    • belongs_to
      • プリキュア belongs_to シリーズ
      • これで返ってくるのは、Result::テーブル名 のクラス??。うーんつまりまあ一個だけ返ってくる。
      • なので、そのまま->name できる。
  • 1対1の関係?
    • might_have
      手元でやってるときはSQLを吐かせても、belongs_toとの違いがいまいち分からなかったのだけど、 プロダクションコードでSQLを吐くようにして見てみると、belongs_toのときはJOINで、might_haveのときはLEFT JOINされているのが確認できた。
      具体的に何をどうしたところでそのJOINが出てるのかまでは追えてないが、そんな感じ。
      名前的にも確かに、絶対両方あるよねだからJOINでいいよね、っていうのとまあ多分あるでしょでもないかもだからLEFT JOINにしとくねっていう感じ。

参考

use DBIx::Class; - 今日のCPANモジュール(跡地)

DBIx::Class::Relationship - テーブル間のリレーションシップ

第3回 DBIx::Classでデータベース操作(1):Perl Hackers Hub|gihyo.jp … 技術評論社

JOINによるテーブルの結合方法5種類を整理 - 具体例で学ぶ数学

JOINについて

そもそものSQLでJOINの動作を確認する。 シリーズIDで結合させる。

  • 内部結合ってやつ
mysql > SELECT * FROM Series se INNER JOIN Precure pr ON se.series_id=pr.series_id;
+-----------+-----------+------------+-----------+----------------+
| series_id | name      | precure_id | series_id | name           |
+-----------+-----------+------------+-----------+----------------+
|         1 | hutariha  |          1 |         1 | black          |
|         1 | hutariha  |          2 |         1 | white          |
|         2 | max heart |          3 |         2 | shiny luminous |
|        15 | hug       |         55 |        15 | amour          |
+-----------+-----------+------------+-----------+----------------+

両テーブルにあるものしか出ない。( smile は出ないしモフルンも出ない )

  • 外部結合ってやつ
mysql > select * from Series se LEFT OUTER JOIN Precure pr ON se.series_id=pr.series_id;
Enter password:
+-----------+-----------+------------+-----------+----------------+
| series_id | name      | precure_id | series_id | name           |
+-----------+-----------+------------+-----------+----------------+
|         1 | hutariha  |          1 |         1 | black          |
|         1 | hutariha  |          2 |         1 | white          |
|         2 | max heart |          3 |         2 | shiny luminous |
|         9 | smile     |       NULL |      NULL | NULL           |
|        15 | hug       |         55 |        15 | amour          |
+-----------+-----------+------------+-----------+----------------+

この場合は、Seriesテーブルは全部出す。が、Precureテーブルに無いものはNULLになる。 RIGHTの場合は、Precureテーブルのを全部出して、Seriesテーブルに無いものはNULLになる。

mysql> select * from Series se RIGHT OUTER JOIN Precure pr ON se.series_id=pr.series_id;
Enter password:
+-----------+-----------+------------+-----------+----------------+
| series_id | name      | precure_id | series_id | name           |
+-----------+-----------+------------+-----------+----------------+
|         1 | hutariha  |          1 |         1 | black          |
|         1 | hutariha  |          2 |         1 | white          |
|         2 | max heart |          3 |         2 | shiny luminous |
|        15 | hug       |         55 |        15 | amour          |
|      NULL | NULL      |        102 |        13 | mofurun        |
+-----------+-----------+------------+-----------+----------------+

次に考えたい

  • プリキュアのテーブル構成はより正しくはこうなるはず
    一人のプリキュアが複数シリーズに参加している。
    (といってもMHと5GoGoだけなんだけど )
    なので、シリーズテーブルとプリキュアテーブルの他に、それらを関連付けるためのシリーズ-プリキュア-関係テーブルが必要。
    っていう場合にどう書けばよいか。
シリーズテーブル
+-----------+-----------+
| series_id | name      |
+-----------+-----------+
|         1 | hutariha  |
|         2 | max heart |
|         9 | smile     |
|        15 | hug       |
+-----------+-----------+

プリキュアテーブル
+------------+----------------+
| precure_id | name           |
+------------+----------------+
|          1 | black          |
|          2 | white          |
|          3 | shiny luminous |
|         55 | amour          |
|        102 | mofurun        |
+------------+----------------+

シリーズ-プリキュア-関係テーブル
+------------+-----------+
| series_id | precure_id |
+------------+-----------+
|          1 |         1 |
|          1 |         2 | 
|          2 |         1 |
|          2 |         2 | 
|          2 |         3 | 
|          3 |         4 | 
+------------+-----------+