Perl-DBIC/SELECT 基本のバックアップ(No.8)

SELECT - DBIx::Class::ResultSet

http://search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/ResultSet.pm#search

SEARCH

 $rs = $c->model('DB::User')->search({address => 'tokyo'},{order_by => ['name', 'id']})
 $rs = $c->model('DB::User')->search([{address => 'tokyo'},{address => 'osaka'}]) # "tokyo" OR "osaka"
 $rs = $c->model('DB::User')->search({age => {'>=' => 20})
 $rs = $c->model('DB::User')->search({hobby => undef) # SQL:"hobby is null"

IN

 $c->model('DB::User')->search({user_id => {-in => \@user_ids}})

OR

 $rs->search([{user.address => '東京'},{user.address => '大阪'}], {join => 'user'});

GROUP BY (AS)

 $rs->search({},{
     select   => ['name',{sum=>'point'}],
     as       => ['name','point'],
     group_by => ['name'],
 });

 $point = $rs->next->get_column('point');

JOIN （2つのテーブルと）

 SELECT order.* FROM order JOIN order_detail ON order.id = order_detail.order_id 
 JOIN goods order_detail.goods_id = goods.id 
 WHERE goods.name = 'book'
 
 $rs = $c->model('DB::Order')->search({
  "goods.name" => 'book',
 },{ 
   join => {order_detail => 'goods'}
 });

PREFETCH

 $rs = $rs->search(undef,{prefetch => 'user', join => 'user'});

ジョインするテーブルをjoin=>'TABLE名'で明示する事。