[Dbix-class] group_by woes

[Sarah Berry]

That did it! Thanks so much. This new query runs about a jillion times
faster than the old one, which had to execute once for each day in the date
range.

On 7/27/06, Jason Galea <lists at eightdegrees.com.au> wrote:
>
>
> not sure if this will work, but it seems like it should..
>
> I recently got an order_by working like this..
>
> order_by => ['"sum( quantity )" DESC'],
>
> so maybe..
>
> group_by => [
>    '"DAYOFMONTH(DATE_ADD(date, INTERVAL -12 HOUR))"',
>    'sensorid',
>    '"DATE_FORMAT(DATE_ADD(date, INTERVAL -12 HOUR),'%b')"'
> ]
>
>
> cheers,
>
> J
>
> Sarah Berry wrote:
> > I have a query where I need to group by several fields. I set it up in
> > SQL first to make sure that it would work on my db, and now I'm trying
> > to put it into the proper DBIx format. The problem is that DBIx
> > doesn't seem to like me using column aliases in my group_by clause.
> >
> > I found a message in the archives from this month (July 7, "help on
> > group_by and select as?") that verified that I can't use aliases in
> > group_by. How can I rephrase my query in a way that is tasteful to
> > DBIx? Should I just grit my teeth and use the straight SQL that
> > already works?
> >
> > The SQL query:
> > SELECT
> >       DATE_ADD(date, INTERVAL -12 HOUR) AS newdate,
> >       DAYOFMONTH(DATE_ADD(date, INTERVAL -12 HOUR)) AS dayofmonth,
> >       DATE_FORMAT(DATE_ADD(date, INTERVAL -12 HOUR),'%b') AS month,
> >       count(*)
> > FROM tblsensorlog
> > WHERE userid = 3003
> >       AND sensorid > 14 AND sensorid < 19
> >       AND date > '2006-06-01 11:59:59' AND date < '2006-07-01 12:00:00'
> > GROUP BY sensorid, month, dayofmonth
> > ORDER BY newdate, sensorid;
> >
> > The prose version: Find out how many times each sensor fired each
> > night, where "night" is defined as anything between noon of one day
> > and noon of the next day, over several nights, for one user.
> >
> > The DBIx attempt:
> >                 my $range = {
> >                         userid => "$userid" ,
> >                         date    => {    '>' => "$tempdate->{'sql'}
> 11:59:59",
> >                                         '<' =>
> > ($tempdate2+1)->{'sql'}." 12:00:00" },
> >                         sensorid        => { '>' => '14', '<' => '19' },
> >                 };
> >                 my $fields = {
> >                         select  => [    "DATE_ADD(date, INTERVAL -12
> HOUR)",
> >                                         "DATE_FORMAT(DATE_ADD(date,
> > INTERVAL -12 HOUR),'%b')",
> >                                         "DAYOFMONTH(DATE_ADD(date,
> > INTERVAL -12 HOUR))",
> >                                         'sensorid',
> >                                         { count => '*' }
> >                                    ],
> >                         as      => [ 'newdate', 'month', 'daymonth',
> > 'sensorid', 'qcount' ],
> >                         group_by        => [ 'daymonth', 'sensorid',
> > 'month', 'daymonth' ],
> >                         order_by        => [ 'newdate', 'sensorid' ]
> >                 };
> >                 my $rs =
> > $schema->resultset('Tblsensorlog')->search($range,$fields);
> >
> > The error message:
> > DBD::mysql::st execute failed: Unknown column 'newdate' in 'order
> > clause' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm
> > line 525.
> > DBD::mysql::st execute failed: Unknown column 'daymonth' in 'field
> > list' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm
> > line 525.
> >
> > Thanks for your help.
> >
> > - Sarah
> >
> > _______________________________________________
> > List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> > Wiki: http://dbix-class.shadowcatsystems.co.uk/
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> > Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
> >
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060728/e50c1406/attachment.htm