SQLを組み立てるもの - Practice of Programming

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

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

O/Rマッパーで、SQLのクエリを組み立てるものが組み込まれてますが、Tengで使われているのは、SQL::Maker、DBIx::Classだと、SQL::Abstract。Tengのsearchメソッドでは、SQL::Makerのselect メソッドを使っていますので、出来ることは、selectメソッドに出来ることに限られます。SQL::Makerをフルで使う場合は、~~>query_builder~~sql_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