- 追加された行はこの色です。
- 削除された行はこの色です。
* SELECT - [[DBIx::Class::ResultSet>http://search.cpan.org/~ash/DBIx-Class-0.08006/lib/DBIx/Class/ResultSet.pm]][#ec0dd122]
* SELECT 基本 [#g8767459]
** SEARCH [#u5289657]
$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"
** where句を設定する [#u5289657]
*** 比較 [#x9a2add7]
$rs->search({ age => 20 }) # WHERE age = 20 =
$rs->search({ age => {'!=', 20} }) # WHERE age != 20 !=
$rs->search({ age => {'>' => 20} }) # WHERE age > 20 >
$rs->search({ age => undef }) # WHERE age is NULL NULL
*** 論理演算子 [#bc1f7be7]
$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({ age => [10, 20] }) # WHERE age BETWEEN 10 AND 20 BETWEEN
$rs->search_like({ name => '%taro%' }) # WHERE name LIKE '%taro%' LIKE
$rs->search({ id => {-in => \@ids}}) # WHERE id in (1,2,3..) IN
** IN [#md6dc17d]
$c->model('DB::User')->search({user_id => {-in => \@user_ids}})
** GROUP BY (AS)[#g35ffed7]
http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search
** 検索を実行する [#p7adce4a]
my $rs = $schema->resultset('User')->search({ age => 20});
*** プライマリキーで検索する [#he6a13f8]
my $user = $schema->resultset('User')->find(5);
見つからないときはundefが返ってくる。
** 検索結果を取り出す [#m298a625]
*** 順次取り出す [#f11c67d6]
while (my $user = $rs->next) { print $user->name; }
*** 1行だけ取り出す [#c2654ac7]
my $user = $schema->resultset('User')->single({ age => 20});
*** 最初の行を取り出す [#s12d6f89]
my $user = $schema->resultset('User')->search({ age => 20})->first;
*** 全行取り出す [#kb43997d]
my @users = $schema->resultset('User')->search({ age => 20})->all;
** ORDER BY [#h7a857b5]
$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
*** NULLを0と見なしてORDER BYする方法 [#ec445214]
(多分)DBICではCASE式が使えないので、COALESCE関数を使う。例えばpointカラムを降順でソートするには以下のようにする。
$rs->search(undef, {
order_by => [ { '-desc' => \'COALESCE(point, 0)' } ],
});
** GROUP BY [#g35ffed7]
$rs->search({},{
select => ['name',{sum=>'point'}],
as => ['name','point'],
select => ['name', {SUM => 'point'}],
as => ['name', 'point'],
group_by => ['name'],
});
$point = $rs->next->get_column('point');
** JOIN (2つのテーブルと) [#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 = $c->model('DB::Order')->search({
"goods.name" => 'book',
},{
join => {order_detail => 'goods'}
** LIMIT / OFFSET [#y76174dc]
$rs->search({},{ order_by => 'id' })->slice(0,20); # slice($offset,$limit)
** 特定のカラムのみSELECT(AS演算子)[#hf28370c]
$rs->search({},{
select => [ 'name', 'age' ],
as => [ 'name', '年齢'],
});
print $r->name, $r->get_column('年齢');
** PREFETCH [#r9685ba3]
$rs = $rs->search(undef,{prefetch => 'user', join => 'user'});
ジョインするテーブルをjoin=>'TABLE名'で明示する事。