Perlゼミ

1. Perl
›
2. モジュール
›
3. データベース
›
4. DBIx::Custom
›
5. here

DBIx::Customの「select」メソッドで行を「選択」する

DBIx::Customで、行を選択するにはselectメソッドを使用します。

my $result = $dbi->select(table => 'book');

tableにテーブル名を指定します。以下のSQLが発行されます。

select * from book;

戻り値はDBIx::Custom::Resultオブジェクトで、このオブジェクトを使って行をフェッチすることができます。

while (my $row = $result->fetch_hash) {
  print $row->{author} . "\n";
  print $row->{title} . "\n";
}

while文を使って、すべての行を1行づつ取り出しています。

selectメソッドのオプション

selectメソッドではexecuteメソッドのすべてのオプションを使用することができます。加えて以下のオプションを実装しています。

列名 column

列名を指定するにはcolumnオプションを使用します。

my $result = $dbi->select(
  table => 'book',
  column => [
    'author',
    'title'
  ]
);

あるいは、selectメソッドの第一引数として列名を指定することもできます。

my $result = $dbi->select(['author', 'title'], table => 'book');

次のようなSQLが発行されます。

select author, title from book;

列名の指定 column

columnオプションを使用するとさまざまな方法で列名を指定することができます。columnオプションを省略した場合はすべての列を表す「*」が列名として設定されます。

(1)文字列

文字列による指定です。

column => 'author'

(2)配列のリファレンス

配列のリファレンスで複数の列を指定できます。

column => ['author', 'title']

次のような列名が生成されます。

author, title

(3) ハッシュリファレンス

ハッシュのリファレンスを使ってテーブル名と列名のセットを記述できます。

column => {book => [qw/title author]}

次のような列名が生成されます。

book.author as "book.author",
book.title as "book.title"

エイリアスの部分はデータベースに応じて正しくエスケープされます。内部的にはcolumnメソッドを実行しています。

(4)配列のリファレンスとハッシュのリファレンスの組み合わせ

複数のハッシュリファレンスを配列のリファレンスの中に指定することもできます。

column => [
  {book => [qw/title author]},
  {company => [qw/id name/]}
]

次のような列名が生成されます。

book.author as "book.author",
book.title as "book.title",
company.id as "company.id",
company.name as "company.name"

次のように文字列とハッシュのリファレンスを配列のリファレンスの要素として混在させることもできます。

column => [
  'title',
  'author',
  {company => [qw/id name/]}
]

IDによる条件の指定 id

IDを使って条件を指定するにはidオプションを使用します。primary_keyが設定されている必要があります。

id => 4
id => [4, 5]

たとえば次のようなselectが実行されたとします。

|
$dbi->select(
  primary_key => ['id1', 'id2'],
  id => [4, 5],
  table => 'book'
);

これは次のselectと同じです。

$dbi->select(
  where => {id1 => 4, id2 => 5},
  table => 'book'
);

Join句 join

join句を指定するにはjoinオプションを指定します。

join => 'left outer join company on book.company_id = company_id'
join => [
  'left outer join company on book.company_id = company_id',
  'left outer join location on company.location_id = location.id'
]

DBIx::Customのjoin句は少し特別な実装がされていて注意が必要です。selectメソッドはcolumn句とwhere句に指定されているテーブル名を検索して必要なjoinを探索します。必要でないjoin句については、SQLの中には含まれません。

つまりcolumnオプションあるいはwhereオプションでテーブル名が見つからなかった場合はjoin句が連結されないので注意してください。

たとえば次のようなselectを実行したとします。

$dbi->select(
  table => 'book',
  column => ['company.location_id as location_id'],
  where => {'company.name' => 'Orange'},
  join => [
    'left outer join company on book.company_id = company.id',
    'left outer join location on company.location_id = location.id'
  ]
);

上記ではcolumnオプションでcompanyというテーブル名が登場しています。一方locationというテーブル名はcolumnオプションにもwhereオプションにも登場していません。

ですのでjoin句の中でcompanyテーブルだけを連結する必要があり、locationテーブルは必要がありません。

それで次のようなSQLが生成されます。

select company.location_id as location_id
from book
  left outer join company on book.company_id = company.id
where company.name = ?;

selectメソッドはできるだけ自動的にテーブル名を探そうとしますが、複雑なjoin句の場合はできないこともあります。そのような場合は次のように文字列の代わりにハッシュのリファレンスを指定して、必要なテーブル名を明示します。

$dbi->select(
  table => 'book',
  column => ['company.location_id as location_id'],
  where => {'company.name' => 'Orange'},
  join => {
    clause => 'left outer join location on company.location_id = location.id',
    table => ['company', 'location']
  }
);

一般的に言えば、join句の中には二つのテーブル名「テーブル名.列名」という形で含まれている必要があります。ですからusing句などを利用する場合はテーブル名が「テーブル名.列名」という形になっていませんので、明示的に指定する必要があります。

またテーブル名の順番は重要です。連結元のテーブルは左側に、連結先のテーブルは右側にある必要があります。

# 正しい例 連結元のbook(book.company_id)は左側に、連結先のcompany(company.id)は右側にある
left outer join company on book.company_id = company.id

# 間違った例 連結元のbook(book.company_id)は右側に、連結先のcompany(company.id)は左側にある
left outer join company on company.id = book.company_id

通常のSQL文にとってはどちらも意味が同じですが、DBIx::Customにとっては解析結果が異なります。

少し難しい解説でしたが、joinがそのまま記述できることと、必要なjoinだけを選んでくれるという利点はとても大きなものです。またjoin句を自然に記述すれば、解析が失敗するということはまずないと思います。

パラメーターの指定 param

where句よりも前に出てくる名前付きプレースホルダーのために値を指定するにはparamオプションを使用します。

param => {'table2.key3' => 5}

たとえばjoin句の中で名前付きプレースホルダーを利用したい場合に利用できるでしょう。

join  => ['inner join (select * from table2 where table2.key3 = :table2.key3)' . 
          ' as table2 on table1.key1 = table2.key1']

プレフィックス prefix

selectの直後に文字列を追加したい場合はprefixを使用します。

prefix => 'SQL_CALC_FOUND_ROWS'

たとえば以下のようなSQLを発行することができます。

select SQL_CALC_FOUND_ROWS * from book;

テーブル名 table

テーブル名を指定するにはtableオプションを使用します。

table => 'book'

Where句 where

Where句を記述するにはwhereオプションを使用します。

where => {author => 'Ken', 'title' => 'Perl'}
where => [
  ['and', ':author{=}', ':title{like}'],
  {author => 'Ken', title => '%Perl%'}
]

whereオプション

whereオプションですが、次のようにさまざまな形式で指定することが可能です。

ハッシュリファレンスによる指定

ハッシュリファレンスでWhere句を指定した場合は、等価(=)による比較をandで結んだ条件になります。

where => {author => 'Ken', 'title' => 'Perl'}

上記の指定は次のようなWhere句になります。

where author = :author and title = :title

「Ken」と「Perl」は名前付きプレースホルダー(:author, :title)に埋め込まれます。

値として配列のリファレンスを指定するとinを使った記述が可能です。

where => {author => ['Ken', 'Taro'], 'title' => 'Perl'}

上記は次のようなWhere区になります。

where author in (:author, :author) and title = :title

対応する値は名前付きプレースホルダーに埋め込まれます。

DBIx::Custom::Whereオブジェクトによる指定

DBIx::Custom::Whereオブジェクトを指定することができます。

where => $where_obj

DBIx::Custom::Whereオブジェクトについては動的なWhere句の生成を見てください。

配列のリファレンスによる指定

配列のリファレンスによる指定を行うと、DBIx::Custom::Whereオブジェクトを生成することなしに、同様の指定を行うことができます。

where => [
  ['and', ':author{=}', ':title{like}'],
  {author => 'Ken', title => '%Perl%'}
];

上記は次の記述と同じ意味を持ちます。

where => $dbi->where(
  clause => ['and', ':author{=}', ':title{like}'],
  param => {author => 'Ken', title => '%Perl%'}
);

文字列による指定

文字列で指定した場合は、単純なWhere句が生成されます。

where => 'title is null'

上記の指定は次のようなWhere句になります。

where title is null

行の件数を取得する - count

行の件数を取得するにはcountメソッドを使用します。

my $count = $dbi->count(table => 'book', where => {title => 'Perl'});

指定できるオプションはselectメソッドと同じものです。columnには自動的に「count(*)」が設定されます。

現在はcountメソッドよりもselectメソッドを使う方法がおすすめです。

my $count = $dbi->select(
  'count(*)',
  table => 'book',
  where => {title => 'Perl'}
)->value;