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

SELECT

where句を設定する

 $rs->search({ age => 20 })                        # WHERE age = 20                   =
 $rs->search({ age => {'>' => 20} })               # WHERE age > 20                   >
 $rs->search({ age => undef })                     # WHERE age is NULL                NULL
 $rs->search([{ age => 20 },{ age => 30 }])        # WHERE age = 20 OR age = 30       OR
 $rs->search({ age => 20, name => 'taro })         # WHERE age = 20 AND name = 'taro' AND
 $rs->search({ 'age' => { '>' => 10, '<' => 20 }}) # WHERE age > 10 AND age < 20      AND  同じカラムを使う場合
 $rs->search_like({ name => '%taro%' })            # WHERE name LIKE '%taro%'         LIKE
 $rs->search({id => {-in => \@ids}})               # WHERE id in (1,2,3..)            IN

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

結果を取り出す

順次取り出す

 my $rs = $schema->resultset('User')->search({ age => 20});
 while (my $user = $rs->next) {
 }

1行だけ取り出す

 my $user = $schema->resultset('User')->single({ age => 20});

最初の行を取り出す

 my $user = $schema->resultset('User')->search({ age => 20})->first;

全行取り出す

 my @users = $schema->resultset('User')->search({ age => 20})->all;

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

プライマリキーで結果を取り出す

見つからないときは、undefが返ってくる。

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

ORDER BY

 $rs->search({},{ 
  order_by => [ 'name', { -desc => 'id' } ] # ORDER BY name ASC, id DESC
  });                                       # 'name' は { -asc => 'name' } の省略形

http://search.cpan.org/perldoc?SQL::Abstract#ORDER_BY_CLAUSES

GROUP BY

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

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

 $rs->search({},{ order_by => 'id' })->slice(0,20); # slice($offset,$limit)

テーブル結合

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' } ]
         ]
     }
 );

自己結合

 SELECT 
   goods.*, 
   cate1.category_name AS category_name, cate2.category_name AS parent_category_name 
 FROM 
   goods 
 JOIN 
   category cate1 ON goods.category_id = cate1.category_id 
 JOIN 
   category AS cate2 ON cate1.parent_category_id = cate2.category_id
 WHERE 
   goods_id = 123

 my $rs = $self->schema->resultset('Goods')->search(
     { goods_id => 123 },
     {
         '+select' => [ 'cate1.category_name', 'cate2.category_name' ],
         '+as'     => [ 'category_name',       'parent_category_name' ],
         from      => [
             { me => 'goods' },
             [ { cate1 => 'category' }, { 'me.category_id'    => 'cate1.category_id' } ],
             [ { cate2 => 'category' }, { 'cate2.category_id' => 'cate1.parent_category_id' } ],
         ]
     }
 );
 
 $row = $rs->first;
 print $row->get_column('category_name');

$row->get_column('category_name')でなく$row->category_nameとしたい場合は、テーブル定義クラスに sub category_name{ shift->get_column('category_name) } というアクセサを追加しておけばよい。

PREFETCH

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

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

特定のカラムのみSELECT（AS演算子）

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

DB関数

sum()

 # SELECT sum(point) AS total_point FROM t1;
 $rs = $rs->search(
     {},
     {
         select => [ { sum => 'point' } ],
         as     => ['total_point'],
     }
 );
 my $total_point = $rs->first ? $rs->first->get_column('total_point') : 0;

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()' } );