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

SELECT

http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search

SEARCH

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

DBの関数を比較（の対象）に使う

 $rs->search({"date_part('year', age(now(), birth_date))" => { '<=' => 18 } }); # 年齢が18歳以下
                                                                                # date_part()とage()はPostgreSQLの関数

http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook#Using_SQL_functions_on_the_left_hand_side_of_a_comparison

DBの関数を比較（の値）に使う

不等号「>」も含めて、スカラリファレンスにする。

 $rs->search( { start_day => \' > now()' } );

FIND

プライマリキーで検索する。見つからないときは、undefが返ってくる。

 my $cd = $schema->resultset('CD')->find(5);

LIKE

 $rs = $rs->search_like({title => '%blue%'});

IN

 $rs->search({user_id => {-in => \@user_ids}})

OR

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

特定のカラムのみSELECT

 $rs->search({},{
    select => [
      'name',
      'age',
    ],
    as => [
       'name',
       '年齢'
    ],
 });
 
 print $r->name;
 print $r->get_column('年齢');

GROUP BY (AS)

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

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

JOIN （3つのテーブル）

 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->search({
  "goods.name" => 'book',
 },{ 
   join => {order_detail => 'goods'}
 });

自己結合

 SELECT XXX FROM category cate1 JOIN category cate2 ON cate1.parent_category_id = cate2.category_id

 $rs = $rs->search(
     {},
     {
         from => [
             { cate1 => 'category' },
             [ { cate2 => 'category' }, { 'cate1.parent_category_id' => 'cate2.category_id' } ]
         ]
     }
 );

PREFETCH

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

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

COUNT()

 $rs = $rs->search();
 print $rs->count;

↓SELECT COUNT(DISTINCT name) FROM user

 $rs = $rs->seach({},{
   select => [ { count => { distinct => 'name' } } ],
    as    => ['count'],
 });
 print $rs->next->get_column('count');

LIMIT / OFFSET

 my $rs = $schema->resultset('CD')->search({ title => 'TEST' },{ order_by => 'id' })->slice(0,20);

slice($offset,$limit)を使う。