DBIx::Custom

動的にwhere句を作成する

DBIx::Customで「動的にwhere句を作成」する方法を解説します。

動的なWhere句の生成

DBIx::Customでは動的にWhere句を生成することができます。たとえば、与えられたパラメータにtitleしか含まれていないければ、titleだけの条件が含まれたWhere句を生成して、パラメータにpriceしか含まれていないならば、priceの条件だけが含まれたWhere句を生成するということが簡単にできます。

動的なWhere句生成の基礎

DBIx::Custom::Whereオブジェクトの生成

まずDBIx::Custom::Whereオブジェクトを生成します。DBIx::Customのwhereメソッドを使うと、DBIx::Custom::Whereオブジェクトを生成することができます。 

my $where = $dbi->where;

Where句の設定

clause属性を使って、Where句を記述します。(:title, :priceは名前付きプレースホルダーです。executeメソッドの解説を参照してください。) 

$where->clause(['and', 'title like :title', 'price = :price']);

配列のリファレンスを指定する必要があり、最初の要素は演算子になります。演算子は「and」と「or」を利用することができます。二つ目の要素以降は、条件を指定しますが、名前付きプレースホルダーを一つだけ含むようにする必要があります。

また条件がひとつだけの場合は文字列を指定することもできます。 

$where->clause('title like :title');

この場合は次の記述と同じ意味になります。 

$where->clause(['and', 'title like :title']);

Where句の生成

Where句を生成するには、to_stringメソッドを利用するか、DBIx::Custom::Whereオブジェクトを文字列として評価します。 

# to_stringメソッド
my $where_clause = $where->to_string;

# 文字列として評価
my $where_clause = "$where";

次のようなWhere句を生成することができます。 

where title like :title and price = :price

動的なWhere句の生成

Where句を動的に簡単に生成することができます。priceの条件だけを含んだWhere句を生成するにはparamのキーにpriceを含んだハッシュリファレンスのデータを渡します。 

$where->clause(['and', 'title like :title', 'price = :price']);
$where->param({price => 1900});
my $where_clause = "$where";

次のようなWhere句が生成されます。 

where price = :price

titleの条件だけを含んだWhere句を生成するにはparamのキーにtitleを含んだハッシュリファレンスのデータを渡します。 

$where->clause(['and', 'title like :title', 'price = :price']);
$where->param({title => 'Perl'});
my $where_clause = "$where";

次のようなWhere句が生成されます。 

where title like :title

どちらも含んでいない場合はWhere句は生成されず、空文字列を取得することができます。 

$where->clause(['and', 'title like :title', 'price = :price']);
$where->param({});
my $where_clause = "$where"; # 空文字列

テーブル名を先頭に付加した列名を利用する

テーブル名を先頭に付加した列名を利用することもできます。他のテーブルの列と区別する必要がある場合にも対応しています。 

$where->clause(['and', 'book.title like :book.title', 'book.price = :book.price']);

Where句の生成の応用

同名の列を二つ以上含む場合

たとえば比較の条件を書きたい場合は、同じ列名を含む条件を記述する必要があります。このような場合にも対応しています。パラメータには、値を配列のリファレンスで指定する必要があります。 

$where->clause(['and', 'price > :price', 'price < :price']);
$where->param({price => [1000, 2000]});

次のようなWhere句が生成されます。 

where price > :price and price < :price

最初の条件だけを動的に生成したい場合は、次のようにします。配列のリファレンスの二つ目の要素に、存在しないことを示すDBIx::Custom::NotExistsオブジェクトをわたします。これはnot_existsメソッドで取得することができます。 

$where->clause(['and', 'price > :price', 'price < :price']);
$where->param({price => [1000, $dbi->not_exists]});

次のようなwhere句が生成されます。 

where price > :price

二つ目の条件だけを動的に生成したい場合は、次のようにします。配列のリファレンスの一つ目の要素に、存在しないことを示すDBIx::Custom::NotExistsオブジェクトをわたします。 

$where->clause(['and', 'price > :price', 'price < :price']);
$where->param({price => [$dbi->not_exists, 2000]});

次のようなwhere句が生成されます。 

where price < :price

複雑な条件

条件を入れ子にしたような複雑な条件も記述することができます。 

$where->clause(
  [
    'and',
    'price = :price',
    ['or', 'title = :title', 'title = :title', 'title = :title']
  ]
);
$where->param({price => '1000', title => ['Perl', 'Ruby', 'Python']});
my $where_clause = "$where";

次のようなWhere句が生成されます。 

where price = :price and (title = :title or title = :title or title = :title)

入れ子構造が複雑ななった場合でも正しく動的にWhere句を生成することができます。たとえば、次のようなパラメータが渡された場合にも正しくWhere句を生成します。 

$where->param({price => 1900, title => ['Perl', 'Ruby']});

配列のリファレンスには、二つしか値が含まれていませんので、次のようなWhere句が生成されます。 

where price = :price and (title = :title or title = :title)

名前付きプレースホルダーを含まない条件

名前付きプレースホルダーを含まない条件を記述することもできます。 

$where->clause(['and', "title = 'Perl'"]);

パラメーターの値にかかわらず、この条件は必ずwhere区に含まれることになります。

or条件による検索

or条件を使った検索を行うには次のようにします。DBIx::CustomではWhere条件が複雑になったときに、複雑なWhereを扱うためのDBIx::Custom::Whereオブジェクトが用意されています。whereメソッドを使用すると、適切に設定されたDBIx::Custom::Whereオブジェクトを新しく生成することができます。 

# where (title = 'Perl' or title = 'Ruby') and price > 1500
my $where = $dbi->where;
my $titles = [qw/Perl Ruby/];
my $clause = ['and',
  ['or', ('title = :title') x @$titles],
  'price > :price'
];
my $param = {title => $titles, price => 1500};
$where->clause($clause);
$where->param($param);

以下はSQLiteを使ったサンプルになります。モデルも上手に使っているので、重複の少ないコードになっています。 

use strict;
use warnings;

use DBIx::Custom;

# Connect
my $dbi = DBIx::Custom->connect(dsn => "dbi:SQLite:dbname=:memory:");

# Create table
$dbi->execute("create table book(title, price)");
my $model = $dbi->create_model(table => 'book');
$model->insert({title => 'Perl', price => 1900});
$model->insert({title => 'Ruby', price => 2000});
$model->insert({title => 'Python', price => 2100});

# Where, (title = 'Perl' or title = 'Ruby') and price > 1500
my $where = $dbi->where;
my $titles = [qw/Perl Ruby/];
my $clause = ['and',
  ['or', ('title = :title') x @$titles],
  'price > :price'
];
my $param = {title => $titles, price => 1500};
$where->clause($clause);
$where->param($param);

# Select
my $result = $model->select(where => $where);

# Fetch
my $rows = $result->all;

Where句で日付の範囲を指定する

データベースである日付からある日付までのデータを取り出すというのはよくある操作です。両方の日付が指定される場合もありますし、片方の日付だけが指定される場合もあります。このような場合に自分で条件分岐を書くのはめんどうです。DBIx::Custom::Whereオブジェクトを利用すると、少し工夫するだけで記述することができます。 

my $start_date = '2010-01-01';
my $end_date   = '2010-03-31';

# DBIx::Custom::Where オブジェクト
my $where = $dbi->where;

# Where句の記述
$where->clause(['and', ':date{>}', ':date{<}'}]);

# パラメーターの設定
my $param = {
  date => [
    $start_date ? $start_date : $dbi->not_exists,
    $end_date   ? $end_date : $dbi->not_exists
  ]
};
$where->param($param);

# selectの実行
$dbi->select(table => 'book', where => $where);

最初にwhereメソッドでDBIx::Custom::Whereオブジェクトを生成します。次にclauseで、Where句を記述します。これはDBIx::Customのタグを使って記述します。上記の例では 

['and', ':date{>}', ':date{<}'}]

は以下のように展開されます。 

where date > ? and date < ?

これは動的なもので、たとえば開始時刻が与えられない場合は次のように展開されます。 

where date < ?

次はパラメータの記述です。dateを二つ指定する必要があるので、配列のリファレンスで指定します。また存在する場合はその値を、存在しない場合は、not_existsメソッドを使って、DBIx::Custom::NotExistsオブジェクトを指定します。 

# パラメーターの設定
my $param = {
  date => [
    $start_date ? $start_date : $dbi->not_exists,
    $end_date   ? $end_date : $dbi->not_exists
  ]
};
$where->param($param);

最後にselect区のwhereに作成したDBIx::Customオブジェクトを渡します。 

# selectの実行
$dbi->select(table => 'book', where => $where);

関連情報

Perlクラブ