[Dbix-class] Issue converting select/insert from DBI to DBIx::Class

[Rob Kinyon]

On Tue, Dec 22, 2009 at 09:28, Steve Bertrand <steve at ibctech.ca> wrote:
> Hi all,
>
> I've been working on converting much of my DBI code to DBIx::Class, and
> am a tad bit stuck.
>
> This particular issue is one that I've worked on for quite a few hours,
> while testing out different combinations I've found in the great number
> of docs. I'm at the point now where I could use an experienced set of eyes.
>
> My original code:
>
>    my $insert_sth = $dbh->prepare("
>        INSERT INTO aggregate_daily
>                (   UserName, AcctDate, ConnNum,
>                    ConnTotDuration, ConnMaxDuration, ConnMinDuration,
>                    InputOctets, OutputOctets, NASIPAddress
>                )
>
>         SELECT UserName, '$day', COUNT(*), SUM(AcctSessionTime),
>         MAX(AcctSessionTime), MIN(AcctSessionTime),
>         SUM(AcctInputOctets),
>         SUM(AcctOutputOctets), NASIPAddress
>         FROM radacct
>         WHERE AcctStopTime like '$day%'
>         GROUP BY UserName,NASIPAddress
>
>    ") or die $DBI::errstr;
>
> ...and the new code (albeit unfinished, as I am just trying to get the
> basics of a select down first). Although it runs without error, I have
> no indication that I'm doing things properly. When I dump the $rs with
> Data::Dumper, I do not see the expected data.
>
> Can someone let me know if I'm on the right track?
>
> my $rs = $schema->resultset( 'Radacct' )->search( undef, {
>            select => [
>                        'username',
>                        { count => 'radacctid' },
>                        { sum   => 'acctsessiontime' },
>                        { max   => 'acctsessiontime' },
>                        { min   => 'acctsessiontime' },
>                        { sum   => 'acctinputoctets' },                {                        {
> sum   => 'acctoutputoctets' },
>                        'nasipaddress',
>            ],
>            group_by => [ qw/ username nasipaddress / ],
>            as       => [ 'daily' ],
>        });
>
> Steve

The first problem is that you're ignore AcctDate in the select and
AcctStopTime in the where clauses. Second is the 'as' element is
unneeded.

Does that help?


-- 
Thanks,
Rob Kinyon