【Perl】DBIx::Simple で副問い合わせや JOIN を使う

by
このエントリーをはてなブックマークに追加

DBIx::Simple + SQL::Abstract を使うと簡単な SQL だと記述が簡潔にすむのだが、副問い合わせや JOIN を使い出すと途端に複雑になる。こういう場合は素直に素の SQL を書いた方がいいのだが、無理矢理にでも書くときはどうなるかやってみる。

WHERE 句にリテラル SQL を書く

文字列ではなく、文字列リファレンスを使う。例えば誕生日の「日」より「月」の方が数値として大きい（五月四日とか一〇月三日とか）人のリストを得たいとする。

二月生まれの人の情報を得る
SELECT id, name
FROM directory
WHERE MONTH(birthday) > DAY(birthday)

これを DBIx::Simple で表すとこうなる。

二月生まれの人の情報を得る（DBIx-Simple 版）
my $ds = DBIx::Simple->new($dbh);
my $rs = $sa->select('directory',
    ['id', 'name'],
    { 'MONTH(birthday)' => { '>' => \'DAY(birthday)' } },
    # { 'MONTH(birthday)' => \'> DAY(birthday)' },
);

リテラル SQL にプレースホルダを使う

次に、年月日が文字列で与えられたとき、誕生日がその日以降である人探す。SQL で書くならこんな感じ1。

平成生まれの人を探す
SELECT id, name
FROM directory
WHERE birthday >= STR_TO_DATE('1989/1/8', '%Y/%m/%d')

上記のやり方でそのまま書くとこうなる。

平成生まれの人を探す（DBIx-Simple 版）
my $rs = $ds->select('directory',
    ['id', 'name'],
    { birthday => { '>=' => \"STR_TO_DATE('1989/1/8', '%Y/%m/%d')" },
);

更に、誕生日の部分をプレースホルダにして任意の日付を指定できるようにしよう。

平成生まれの人を探す（DBIx-Simple 版）
my $ymd = '1989/1/8';
my $rs = $ds->select('directory',
    ['id', 'name'],
    { birthday => { '>=' => \["STR_TO_DATE(?, '%Y/%m/%d')" => $ymd] },
);

配列リファレンスのリファレンス（\[ ... ]） という摩訶不思議なものが出てきた。これを利用すると副問い合わせも可能になる。

副問い合わせを使う

別の住所テーブルを使って、東京都在住の人を探そう。SQL ならこんな感じ。

特定の都道府県に住む人の情報を得る
SELECT id, name
FROM directory
WHERE id  = (
    SELECT id
    FROM address
    WHERE prefecture = '東京都'
)
特定の都道府県に住む人の情報を得る（DBIx-Simple 版）
my $rs = $ds->select('directory',
    ['id', 'name'],
    { id => \[<<SQL => '東京都'] },
SELECT id
FROM address
WHERE prefecture = ?
SQL
);

これを、任意の数の都道府県に拡大したいときはどうしよう？ ここで肝になるのは件の“配列リファレンスのリファレンス”2。この中身は実は SQL::Abstract の返値に等しい。

任意の都道府県に住む人の情報を得る その１
1
2
3
4
5
6
7
8
9
10
my @prefectures = qw! 東京都 神奈川県 千葉県 !;
my $sa = SQL::Abstract->new;
my ($sub_stmt, @sub_binds) = $sa->select('address',
    ['id'],
    { prefecture => { -in => \@prefectures } },
);
my $rs = $ds->select('directory',
    ['id', 'name'],
    { id => \[$sub_stmt => @sub_binds] },
);

ここまで来ると記述量も増えてくるし意味あるの？って気にもなる。まあ記述できることに意味があるってことで。

JOIN を使う

副問い合わせは（記述量は増えるものの）SQL を一切使わない綺麗な構文で書けるのに対して、JOIN になるとかなり無理矢理な書き方になる。

任意の都道府県に住む人の情報を得る その２
SELECT d.id, d.name
FROM directory d
JOIN address a ON d.id = a.id
WHERE a.prefecture IN ('東京都', '神奈川県', '千葉県')

これを DBIx::Simple で書くと……

DBIx-Simple 版 その１
my @prefectures = qw! 東京都 神奈川県 千葉県 !;
my $rs = $ds->select(<<TABLES,
    directory d
    JOIN address a ON d.id = a.id
TABLES
    ['d.id', 'd.name'],
    { 'a.prefecture' => { -in => \@prefectures } },
);

テーブルを指定するところに無理矢理 JOIN 句を押し込むことになる。これなら素直に SQL 文書いた方がいいかもね。

DBIx-Simple 版 その２
my @prefectures = qw! 東京都 神奈川県 千葉県 !;
my $rs = $ds->query(<<SQL, @prefectures);
    SELECT d.id, d.name
    FROM directory d
    JOIN address a ON d.id = a.id
    WHERE prefecture IN (
        @{[ join ', ', ('?') x @prefectures ]}
    )
SQL
  1. 以下全て、MySQL での文法に従う。 ↩
  2. 一番最後の例に挙げたように SQL 文中に Perl 文を置いてプレースホルダを作る手もある。 ↩

コメントを残す