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

sql dbic perl

p175〜 相関副問い合わせ。

SELECT 
    p.ProductName,
    s1.Quantity,
    s1.saledate
FROM
    Sales AS s1
        JOIN
    Products AS p
    ON p.ProductID = s1.ProductID
WHERE
    s1.Quantity > (
        SELECT AVG(s2.Quantity)
        FROM
            Sales AS s2
        WHERE s2.ProductID = s1.ProductID
    )
;

my $avg = $schema->resultset('Sales')->get_column('Quantity')->func('AVG');
$schema->resultset('Sales')->search(
    {
        'me.Quantity' => {'>' => $avg},
    },
    {
        select => [qw/ Products.productname me.saledate /],
        as => [qw/ name saledate/],
        join => [qw/ Products /],
        order_by => \'Products.productid ASC, me.saledate DESC',
    }
);