* SELECT [#g8767459]
http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search
** SEARCH [#u5289657]
$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の関数を比較(の対象)に使う [#zeced0ab]
$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の関数を比較(の値)に使う [#z004db0c]
不等号「>」も含めて、スカラリファレンスにする。
$rs->search( { start_day => \' > now()' } );
** FIND [#he6a13f8]
プライマリキーで検索する。見つからないときは、undefが返ってくる。
my $cd = $schema->resultset('CD')->find(5);
** LIKE [#f4b07f2c]
$rs = $rs->search_like({title => '%blue%'});
** IN [#md6dc17d]
$rs->search({user_id => {-in => \@user_ids}})
** OR [#k5895aa6]
$rs->search([{user.address => '東京'},{user.address => '大阪'}], {join => 'user'});
** 特定のカラムのみSELECT [#hf28370c]
$rs->search({},{
select => [
'name',
'age',
],
as => [
'name',
'年齢'
],
});
print $r->name;
print $r->get_column('年齢');
** GROUP BY (AS)[#g35ffed7]
$rs->search({},{
select => ['name',{sum=>'point'}],
as => ['name','point'],
group_by => ['name'],
});
$point = $rs->next->get_column('point');
** JOIN (3つのテーブル) [#s1746a0e]
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'}
});
** 自己結合 [#c1729252]
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 [#r9685ba3]
$rs->search(undef,{prefetch => 'user', join => 'user'});
ジョインするテーブルをjoin=>'TABLE名'で明示する事。
** COUNT() [#e0e28000]
$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 [#y76174dc]
my $rs = $schema->resultset('CD')->search({ title => 'TEST' },{ order_by => 'id' })->slice(0,20);
slice($offset,$limit)を使う。