[Dbix-class] group_by woes

[Sarah Berry]

Ah, thanks, that's even better. Much more readable that way, which means
less chance for programmer error!

On 7/30/06, Jess Robinson <castaway at desert-island.demon.co.uk> wrote:
>
>
> See also the new DBIx::Class::Manual::FAQ, which explains another way to
> do it.
>
> Jess
>
>
> On Fri, 28 Jul 2006, Sarah Berry wrote:
>
> > 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/
> >>
> >
> >
>
> _______________________________________________
> 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/20060803/13ae28a5/attachment-0001.htm