SELECT 基本where句を設定する比較$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 論理演算子$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 http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search 検索を実行するmy $rs = $schema->resultset('User')->search({ age => 20}); プライマリキーで検索するmy $user = $schema->resultset('User')->find(5); 見つからないときはundefが返ってくる。 検索結果を取り出す順次取り出すwhile (my $user = $rs->next) { print $user->name; } 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; 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 NULLを0と見なしてORDER BYする方法(多分)DBICではCASE式が使えないので、COALESCE関数を使う。例えばpointカラムを降順でソートするには以下のようにする。 $rs->search(undef, { order_by => [ { '-desc' => \'COALESCE(point, 0)' } ], }); GROUP BY$rs->search({},{ select => ['name', {SUM => 'point'}], as => ['name', 'point'], group_by => ['name'], }); $point = $rs->next->get_column('point'); LIMIT / OFFSET$rs->search({},{ order_by => 'id' })->slice(0,20); # slice($offset,$limit) 特定のカラムのみSELECT(AS演算子)$rs->search({},{ select => [ 'name', 'age' ], as => [ 'name', '年齢'], }); print $r->name, $r->get_column('年齢'); |
|