Practice of Programming このページをアンテナに追加 RSSフィード

2011-12-05

[]SQLを組み立てるもの

最近、Tengを使っていますが、それほど複雑でもないSQLを組み立てるのに、searchメソッドは使いにくいと思う時が割と多い(group by出来ないとか、x = ? or y = ? とか書きにくいとか)。たぶん、方針として、シンプルなSQL以外は、search_by_sqlをっていうことなのだろうと思います。


ですが、SQL文字列として組み立てるのは、割とめんどくさいので、SQLを組み立てるモジュールを使って、search_by_sqlに渡しています。


O/Rマッパーで、SQLクエリを組み立てるものが組み込まれてますが、Tengで使われているのは、SQL::MakerDBIx::Classだと、SQL::Abstract。Tengのsearchメソッドでは、SQL::Makerのselect メソッドを使っていますので、出来ることは、selectメソッドに出来ることに限られます。SQL::Makerをフルで使う場合は、>query_buildersql_builderからnew_selectとか使えばSQL::Maker::Selectのオブジェクト取れますので、それを使ってごにょごにょできます。

SQL::Maker

SQL::Makerはコードが読みやすくてすっきりしています。ただ、andで書く場合や、一つのカラムについてのorを書く場合は良いんですが、x = ? OR y = ? とかが書くのが、ちょっとめんどくさいと思う(もっと簡単にかけますかね?)

  use SQL::Maker;
  my $q = SQL::Maker::Select->new(driver => 'mysql');
  my $c1 = SQL::Maker::Condition->new;
  $c1->add(hoge => 1);
  my $c2 = SQL::Maker::Condition->new;
  $c2->add(fuga => 2);
  my $c3 = SQL::Maker::Condition->new;
  $c3->add(xxx => 2);

  $q->add_from('hoge')->set_where(($c1 | $c2) & $c3)->add_select('id');
  my $sql = $q->as_sql;
  my @binds = $q->bind;

  print $sql, "\n";
  print join ", ",@binds;

結果。

SELECT id
FROM hoge
WHERE (((hoge = ?)) OR ((fuga = ?))) AND ((xxx = ?))
1, 2, 2

コードを見たときに条件の組み立てが分かりやすいのはいいとは思います。後、結果のSQLが改行されていて見やすい。


自分で、SQL::Maker::Selectオブジェクトを使ってSQLを作る場合に、order by, limit, offsetなどの処理を、selectメソッドと同じような引数で同じような処理をしたくても、その辺の処理は select メソッド内で書かれていて外から呼べないので、自前で書くか SQL::Makerからコピペする必要があります。

SQL::Abstract

SQL::Abstractだと、

  use SQL::Abstract;
  my $sql = new SQL::Abstract;
  my ($s, @binds) =$sql->select('hoge', ['id'], {-or => [{hoge => 1}, {fuga => 2}], xxx => 2});
  print $s, "\n";
  print join ", ", @binds;

結果。

SELECT id FROM hoge WHERE ( ( ( hoge = ? OR fuga = ? ) AND xxx = ? ) )
1, 2, 2

ただ、SQL::Abstractも、joinとかしようと思うと、割とめんどくさいなぁって気分になるというか、書き方を毎度忘れる。

limit使う場合は、SQL::Maker::Abstract::Limit とかも覚えられない。


ちなみに、SQL::Abstractについては、zigorouさんが、2年前に記事を書かれています。

http://perl-users.jp/articles/advent-calendar/2009/hacker/06.html

SQL::Abstract::More

で、SQL::Abstract::Moreというのを見つけました。

SQL::Abstract::Moreは、SQL::Abstractサブクラスですが、hash一つで、割と綺麗に書ける気がする。

use SQL::Abstract::More;

my $sql = new SQL::Abstract::More;
my ($s, @binds) = $sql->select
    (
     -columns => ['*'],
      -from    => 'test',
      -where   => {
                   'hoge' => {'!=' => undef},
                   'fuga' => {'not in' => [1,2,3,4]},
                   'bar' => [
			     222
			    ],
                   '-or' => [
                             {xxx => 1},
                             {yyy => 1}
                            ],
                  },
      '-group_by' => [
                      'group1',
                      'group2'
                     ],
      '-limit' => 3
     );
  print $s, "\n";
  print join ", ", @binds;

結果

SELECT * FROM test WHERE ( ( ( xxx = ? OR yyy = ? ) AND bar = ? AND fuga NOT IN ( ?, ?, ?, ? ) AND hoge IS NOT NULL ) ) GROUP BY group1, group2  LIMIT ? OFFSET ?
1,1,222,1,2,3,4,3,0

joinとかも、慣れたら覚えられるレベル。

use Data::Dumper;
print Dumper($s, \@binds);

use SQL::Abstract::More;
my $sql = new SQL::Abstract::More;
my ($s, @binds) = $sql->select
  (
   '-columns' => [
		  'x.id',
		  'y.id',
		  'count(*) as cnt',
		 ],
   '-where' => {
		'x.type' => '1',
		'date'   => '2011-12-05',
		'y.name' => 'name'
	       },
   '-from' => [
	       '-join',
	       'table1|x',              # from table1 as x
	       'x.id=y.table1_id', # on x.id = y.table1_id
	       'table2|y'              # inner join table2 as y
	      ],
   '-group_by' => [
		   'x.id',
		   'y.id'
		  ],
   '-limit' => 3
  );
  print $s, "\n";
  print join ", ", @binds;

こんな感じ。

SELECT x.id, y.id, count(*) as cnt FROM table1 AS x INNER JOIN table2 AS y ON ( x.id = y.table1_id ) WHERE ( ( date = ? AND x.type = ? AND y.name = ? ) ) GROUP BY x.id, y.id  LIMIT ? OFFSET ?
2011-12-05,1,name,3,0

fromのところを以下のようにすると LEFT JOIN。

   '-from' => [
	       '-join',
	       'table1|x',
	       '=>{x.id=y.table1_id}',
	       'tablr2|y'
	      ],

以下のようにjoinを書ける。

         '<=>' => '%s INNER JOIN %s ON %s',
          '=>' => '%s LEFT OUTER JOIN %s ON %s',
         '<='  => '%s RIGHT JOIN %s ON %s',
         '=='  => '%s NATURAL JOIN %s',

割と良さそうなので、Teng用に既存のメソッドと似た感じに使えるようにプラグインを書いてみた。まだ、テストはしていません。

Teng::Plugin::SearchBySQLAbstractMore

hirafoohirafoo 2011/12/05 11:10 s/zigrouさん/zigorouさん/

ktatktat 2011/12/05 12:15 毎度、さんくすです…。

nekokaknekokak 2011/12/05 16:45 昔SkinnyにはresultsetというメソッドがあったのですがTengでは取っ払ってるんですよねー。ただSQL::MakerのnewメソッドへのSugarはあってもいいかもとおもってます!ぱっちーず(ry

ktatktat 2011/12/05 19:14 あれ、sql_builder でSQL::Makerのオブジェクト返ってきません?(文章中、query_builderって、間違えて書いてますが)。

自分用のモジュールでは searchメソッドをSQL::Abstract::More使うようにオーバーライドしちゃっても問題ないんじゃないかなぁと思ってます。
SQL::Abstract::MoreとSQL::Makerを比べたときに、書きやすさは前者で、内部のコードの読みやすさとスピードは後者って感じなんですが、SQLの生成はボトルネックになるところではないので、使うだけで考えると前者の方が良いということに落ち着いちゃう感じで。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証