* SELECT [#g8767459]
** where句を設定する [#u5289657]
$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
** 結果を取り出す [#p7adce4a]
*** 順次取り出す [#f11c67d6]
my $rs = $schema->resultset('User')->search({ age => 20});
while (my $user = $rs->next) {
}
*** 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;
http://search.cpan.org/perldoc?DBIx::Class::ResultSet
** プライマリキーで結果を取り出す [#he6a13f8]
見つからないときは、undefが返ってくる。
my $cd = $schema->resultset('CD')->find(5);
** 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
** GROUP BY [#g35ffed7]
$rs->search({},{
select => ['name', {SUM => 'point'}],
as => ['name', 'point'],
group_by => ['name'],
});
$point = $rs->next->get_column('point');
** 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]
$rs->search({},{ order_by => 'id' })->slice(0,20); # slice($offset,$limit)
** テーブル結合 [#w4bf664b]
*** 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' } ]
]
}
);
*** 自己結合 [#f2db902b]
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 [#r9685ba3]
$rs->search(undef,{prefetch => 'user', join => 'user'});
ジョインするテーブルをjoin=>'TABLE名'で明示する事。
** 特定のカラムのみSELECT（AS演算子）[#hf28370c]
$rs->search({},{
select => [ 'name', 'age' ],
as => [ 'name', '年齢'],
});
print $r->name, $r->get_column('年齢');
** DB関数 [#r24f677f]
*** sum() [#o5ab99c5]
# 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の関数を比較の対象に使う [#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()' } );
