DBIC勉強しつつ、SQL書き方ドリル読んだメモ

DBは SQL書き方ドリル のサンプルです。
そこから DBIx::Class::Schema::Loader をつかって schema dump して relation とか書きました。
毎日ちょっとずつやっていこうかと思っております。

Util

dumpするやつ。

sub p {
    my ($rs) = @_;
    my @rows = $rs->isa('DBIx::Class::ResultSet') ? $rs->all : @_;
    my $colums_ref_encode = sub {
        my $hsh = {$_[0]->get_columns};
        $dev->encode('utf8', $hsh);
        $hsh;
    };
    my $ret = [ map{ $colums_ref_encode->($_) } @rows ];
    use Data::Dumper;
    print Dumper $ret, "\n";
}

もっと良い方法が知りたい。

集合関数

SELECT AVG(Price) FROM Products;

$schema->resultset('Products')->get_column('Price')->func('avg');

WHERE句

SELECT * FROM Employees WHERE Height >= 180;

sub taller_than {
    my ($height) = @_;
    return $schema->resultset('Employees')->search({
        Height => { '>=' => $height },
    });
}

p(taller_than(180));

self join

SELECT
    p1.ProductName
  , p2.ProductName
  , (p1.Price + p2.Price)
FROM Products as p1
     JOIN
     Products as p2
        ON p1.ProductID < p2.ProductID
        AND p1.CategoryID <> p2.CategoryID
WHERE (p1.Price + p2.Price) > 2500
;

sub more_expensive_than {
    my ($num) = @_;
    $schema->resultset('Products')->search(
        {
            "(p1.Price + p2.Price)" => {'>' => $num},
        },
        {
            from => [
                { p1 => 'Products'},
                [
                    { p2 => 'Products', -join_type => 'inner' },
                    {
                        'p1.ProductID' => \'< p2.categoryid',
                        'p1.CategoryID' => \'<> p2.CategoryID',
                    }
                ],
            ],
            'select' => [
                'p1.ProductName',
                'p2.ProductName',
                \"(p1.Price + p2.Price)",
            ],
            'as' => [
                'p1_name',
                'p2_name',
                'sum',
            ]
        }
    );
}

p(more_expensive_than(2500));

発行されるSQL

SELECT
    p1.ProductName, p2.ProductName,(p1.Price + p2.Price)
FROM
    Products p1
    INNER JOIN
    Products p2
        ON ( p1.CategoryID <> p2.CategoryID
         AND p1.ProductID < p2.categoryid )
WHERE
    ( (p1.Price + p2.Price) > 2500 )
;