― Web Technology and Life ―

PerlのTeng(SQL::Maker)でマニアックなクエリを組み立てる方法

2015-03-08
Tengでマニアックなクエリを組み立てたいときは、SQL::Makerのドキュメントを読みます。当然、生SQLで書いた方がいいこともたくさんありますが、複雑な検索フォームなどには有効な手法です。久々に書くブログ記事として、ググっても見つけづらい個人的に忘れがちな三つの用途をメモります。

TengからからSQL::Makerを使うと便利な三つの例

  • 複雑なOR検索
  • InsertOnDuplicate
  • JOIN

それぞれ個別に解説しますが、まずその前にそもそも論。

そもそもTengからSQL::Makerを使うには

たまに、Teng使っているのに新しくuse SQL::Maker;するっていう人がいますが、TengのクエリビルダーのバックエンドはSQL::Makerなので以下のようにしてSQL::Makerのオブジェクトをとれます。

$sql_maker = $teng->sql_builder();

意外とコレが一番重要ですね。

では、個別の事例の解説をしていきます。

複雑なOR検索

Tengを使って、WHERE句を組み立てると、基本的にAND検索になるのですが、条件を複数指定したOR検索含みのWHEREを作ることに対応していません。ですので、SQL::Makerを使います。

作りたいSQL文

例えば、ユーザー検索フォームみたいなのがあったとして

  • 趣味がゴルフか
  • 趣味が食べ歩きか
  • 趣味がプログラミングか
  • で年齢は20以上

といったものを検索するSQL文は

SELECT * FROM member 
WHERE
     ( favarite_1 = ? OR favarite_2 = ? OR favarite_3 = ? ) 
 AND age > 20

という風になります。趣味のところが、ウェブのフォームでチェックボックスになっていて他にもいろいろあると、いろいろ動的にプログラミングのデータ構造でごにょごにょしたいので、生SQLは不向きですよね。

Indexが効かないからこういうのは実装しないという話があったりしますが、まぁ1万件もいかないようなデータをいろいろと検索したいというニーズは企画職とかそういう人にはあるので、適当にインデックスつけてこういう実装しておくのはいいと思います。

TengでのSQL::Makerを使った例

#SQL::Maker->new_conditionでWhere句を動的に組み立てるためのオブジェクトがとれる
my $cond  = $teng->sql_builder->new_condition();

for my $var (qw/favarite_1 favarite_2 favarite_3 favarite_4 favarite_5 favarite_6/){ 
    if ($p{$var}) {
        #$condはOR検索にしたいときは「|」で連結可能
        $cond = $builder | $cond->new_condition->add($var => 1);
    }
}

if ($p{$age}) {
    #$condはAND検索にしたいときは「&」で連結可能
    $cond = $cond & $builder->new_condition->add(age => $age);
}

$teng->search('member',$cond); #$condをそのままwhereに指定できる

ポイントはコード例にもコメントいれましたが、以下の三つです。

  • SQL::Maker->new_conditionでWhere句を動的に組み立てるためのオブジェクトがとれる(詳細は、SQL::Maker::Condition参照)
  • $condはOR検索にしたいときは「|」で連結可能で、AND検索にしたいときは「&」で連結可能
  • $condをそのままTengのsearchメソッドのwhereに指定できる

なかなかスマートに書けると思います。

補足:同一カラムの OR検索

別カラムじゃなくて、同一カラムをOR検索したいときは、SQL::Makerを呼び出さなくても指定ができます。

$db->search(user => {id => [{'>' => 3},{'<' => 7}]});

なぜか、デフォルトはNULLになってしまったが基本10以下NULLはゼロ扱い的な感じでとりたいときがあります。(そもそも論はなしで、苦笑)

$db->search(user => {\"NULL",{'<' => 7}]});

InsertOrUpdateをしたい

「INSERT ... ON DUPLICATE KEY UPDATE」ってやつを動的にSQL組み立ててたたきたいときがあります。

作りたいSQL文

いい例が思いつかないのですが、

#uniqe keyがnameだったとして
INSERT INTO member (name,age,favarite_1,favarite_2) VALUES ("hirobanex",20,1,0) ON DUPLICATE KEY UPDATE age = 20,favarite_1 = 1,favarite_2 = 0;

INSERT INTO member (name,favarite_1,favarite_2) VALUES ("hirobanex",1,0) ON DUPLICATE KEY UPDATE favarite_1 = 1,favarite_2 = 0;

みたいな感じで、ケースバイケースで、カラム数が変わるときなんか生でSQL書くとちょっと具合が悪かったりする気がします。

TengでのSQL::Makerを使った例

$teng->sql_builder->load_plugin('InsertOnDuplicate');$tengをnewした直後にしておけばどこでも使える

my ($sql,@binds) = $teng->sql_builder->insert_on_duplicate('member', $var, $var);

$teng->->execute($sql, \@binds);

こんな感じですね。以下のようにもできますが、めんどいので、上のケースでOKです。

#独自のQueryBuilderを用意する
package MyApp::QueryBuilder;
use strict;
use warnings;
use utf8;
use parent qw/SQL::Maker/;

__PACKAGE__->load_plugin('InsertMulti');
__PACKAGE__->load_plugin('InsertOnDuplicate');

#Tengをnewするとき
Use MyApp::DB;
Use MyApp::DB::QueryBuilder;

MyApp::DB ->new(
    sql_builder_class => 'MyApp::DB::QueryBuilder',
    %{$self->get('conf')->{'datasource'}}
);

当初、load_pluginはnewしたあとに呼ぶということを考えもしなくて、下の独自クラスを用意することをしていましたが、Hachioji.pmで話したらマコピーさんとワイトンさんが「オブジェクト」から呼べるよ!って教えてくれました。感謝ですね!!

JOINしたい

Tengのsearchメソッドは以下のように第3引数にオプションがわたせて、SQL::MakerのドキュメントとつけあわせるとJOINがうてるように見えるのですが、少なくともMySQLだとSQL::MakerのSQL生成の都合上、発行したSQLがエラーになります。

ダメな方法

#teng->search('member',$where,{
    joins => [
        [member => {
            type      => 'inner',
            table     => 'item',
            condition => "member.id= item.member_id"
        }],
    ],
    order_by => +{ "member.age" => 'desc' },
    columns  => +[
        ["member.id","id"],
        ["member.age","age"],
        ["item.name","item_name"]
});

songmu製プラグインを利用する方法

Teng::Plugin::SearchJoinedというのがありまして、以下のように利用できます。

package MyDB;
use parent qw/Teng/;
__PACKAGE__->load_plugin('SearchJoined');

package main;
my $db = MyDB->new(...);
my $itr = $db->search_joined(user_item => [
    user => {'user_item.user_id' => 'user.id'},
    item => {'user_item.item_id' => 'item.id'},
], {
    'user.id' => 2,
}, {
    order_by => 'user_item.item_id',
});

while (my ($user_item, $user, $item) = $itr->next) {
    ...
}

これを使うと複数のテーブルそれぞれのrowオブジェクトがとれて、updateとかrowオブジェクトの便利メソッドと連携するときに結構ハッピーです。

愚直な方法

ただ、複数のrowオブジェクトいらないってときがあります。searchでなげたやつをまるっと、テンプレートの変数に突っ込んでしまいたいくらいあっさりしたものを作りたいときです。

それには、一旦、SQL::Makerを呼び出してそれを使いまわすようなメソッド拡張させておくと便利です。

package EyeCaratSupport::DB::EccubeDB;
use parent 'Teng';

__PACKAGE__->load_plugin('Count');
__PACKAGE__->load_plugin('FindOrCreate');

sub search_myjoin {

    my $table = $self->schema->get_table($table_name) or Carp::croak("'$table_name' is unknown table");

    my $columns = $opt->{'+columns'}
        ? [@{$table->{columns}}, @{$opt->{'+columns'}}]
        : ($opt->{columns} || $table->{columns})
    ;

    my ($sql, @binds) = $self->sql_builder->select(
        undef,#こ変更しただけ
        $columns,
        $where,
        $opt,
    );

    my $itr = $self->search_by_sql($sql,\@binds);

    return wantarray ? $itr->all : $itr;
}

これをおうようすれば、Pagerプラグインをloadして呼び出すsearch_with_pagerもjoinしたかたちにカスタマイズできるのでオレオレメソッドをつけていく皮切りにやってみるのもの良いと思います。

ドキュメントにパッチを送ればかった

なんとなく忘れそうだからオレオレメモようにブログ記事にしましたが、よく考えればドキュメントにPull Reqestをおくればよかったですねー。。。

なお、この記事はいつも通りHahchioji.pmのゆるふわLTをしてフィードバックをもらった後に記事化しております。ちょっと調べてリアルイベントでシェアしてその後記事にして、さらにたくさんの人にシェア or FBもらう、っていうのはいい流れだから、Hachioji.pmを筆頭にリアルイベントオススメですね!

Perl update_at : 2015-03-08T18:43:34
hirobanex.netの更新情報の取得
 RSSリーダーで購読する   
blog comments powered by Disqus