DBIx::Skinny
スキーマクラス
package MyDB;
use DBIx::Skinny setup =>
{ dsn => 'dbi:Pg:dbname=test_db;host=localhost', username => 'scott', password => '' };
package MyDB::Schema;
use DBIx::Skinny::Schema;
install_table users => schema {
pk 'id';
columns qw/id name birth_on group_id/;
};
install_table groups => schema {
pk 'id';
columns qw/id name/;
};
普通にSELECT
use MyDB;
use Perl6::Say;
my $db = MyDB->new;
my $row = $db->search('users', {name => 'taro'})->first;
say $row->id;
say $row->name;
SQLを手で書いてSELECT
use MyDB;
use Perl6::Say;
my $db = MyDB->new;
my $row = $db->search_by_sql(
"SELECT * FROM users WHERE name = ?",
['taro'],
'users' # 結果セットオブジェクトの基準テーブルを指定
)->first;
say $row->id;
say $row->name;
JOINしてSELECT
use MyDB;
use Perl6::Say;
my $db = MyDB->new;
my $rs = $db->resultset;
$rs->add_select('*');
$rs->add_select( 'users.id' => 'user_id' );
$rs->add_select( 'groups.id' => 'group_id' );
$rs->add_select( 'users.name' => 'user_name' );
$rs->add_select( 'groups.name' => 'group_name' );
$rs->add_join(
users => [
{
table => 'groups',
condition => 'users.id = groups.id',
}
]
);
$rs->add_where('users.name' => 'taro');
my $itr = $rs->retrieve('users'); # 結果セットオブジェクトの基準テーブルを指定
while (my $row = $itr->next) {
say $row->user_id;
say $row->user_name;
say $row->group_id;
say $row->group_name;
}
SQLとバインドパラメータを表示
say $rs->as_sql;
say Dumper $rs->bind;
