perl DBIほにゃららら

最近SQL::Abstractがはやっていて、いろいろ調べてみた

user_id in (1, 2, 3)
もとsrc

my @ids = qw/111 222 333/;
my $ids = join(q{,}, @ids);
my $data = $dbh->selectall_hashref(<<"SQL", id);
select id, column1, column2 from table1 where id in ($ids)
SQL

それをいい感じに

my @ids = qw/111 222 333/;
my $sql = SQL::Abstract->new();
my ($stmt, @bind) = $sql->select(
    'table1',
    [qw/ id column1 column2 /],
    +{ id => +{ 'in' => \@ids } },
);
my $data = $dbh->selectall_hashref($stmt, 'id', +{}, @bind);

---

大なり、小なりとかとか

my $sql = SQL::Abstract->new;
my ($stmt, @bind) = $sql->insert(
    'user_data',
    '*',
    +{ user_id => +{ '>=', 100000} }
);

---

同じカラムに対するandとか

    ( $stmt, @bind ) = $sql->select(
        'table',
        [qw/field1 field2/],
        +{
            reg_date => +{ '<=' => 12345, '>' => 54321 },
        }
    );

    is $stmt, q{SELECT field1, field2 FROM table WHERE ( ( reg_date <= ? AND reg_date > ? ) )},
        "stmt";
    is_deeply \@bind, [qw/12345 54321/], "bind";

---

Limitとか

use SQL::Abstract::Limit;
    my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );
    ( $stmt, @bind ) = $sql->select(
        'table', [qw/field1 field2/],
        +{ id    => '1' },
        +{ -desc => 'field3' },
        10, 2
    );  
    is $stmt =>
        'SELECT field1, field2 FROM table WHERE ( id = ? ) ORDER BY field3 DESC LIMIT 10 OFFSET 2',
        'stmt';
    is_deeply \@bind, [qw/1/], 'bind';