DBIx::Simple + SQL::Abstract を使うと簡単な SQL だと記述が簡潔にすむのだが、副問い合わせや JOIN を使い出すと途端に複雑になる。こういう場合は素直に素の SQL を書いた方がいいのだが、無理矢理にでも書くときはどうなるかやってみる。
WHERE 句にリテラル SQL を書く
文字列ではなく、文字列リファレンスを使う。例えば誕生日の「日」より「月」の方が数値として大きい（五月四日とか一〇月三日とか）人のリストを得たいとする。
SELECT id, name FROM directory WHERE MONTH(birthday) > DAY(birthday)
これを 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')
上記のやり方でそのまま書くとこうなる。
my $rs = $ds->select('directory',
['id', 'name'],
{ birthday => { '>=' => \"STR_TO_DATE('1989/1/8', '%Y/%m/%d')" },
);
更に、誕生日の部分をプレースホルダにして任意の日付を指定できるようにしよう。
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 = '東京都'
)
my $rs = $ds->select('directory',
['id', 'name'],
{ id => \[<<SQL => '東京都'] },
SELECT id
FROM address
WHERE prefecture = ?
SQL
);
これを、任意の数の都道府県に拡大したいときはどうしよう？ ここで肝になるのは件の“配列リファレンスのリファレンス”2。この中身は実は SQL::Abstract の返値に等しい。
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 で書くと……
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 文書いた方がいいかもね。
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