- 追加された行はこの色です。
- 削除された行はこの色です。
** テーブル結合 [#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'
* 自己結合 [#c9b292e5]
$rs->search({
"goods.name" => 'book',
},{
join => {order_detail => 'goods'}
});
*** 自己結合 [#c1729252]
*** 自己結合1 [#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]
*** 自己結合2 [#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名'で明示する事。