[Dbix-class] group_by woes

[Dan Horne]

Try here:

 

http://search.cpan.org/~mstrout/DBIx-Class-0.07000/

 

Dan

 

  _____  

From: dbix-class-bounces at lists.rawmode.org
[mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Sarah Berry
Sent: Wednesday, 2 August 2006 2:50 a.m.
To: dbix-class at lists.rawmode.org
Subject: Re: [Dbix-class] group_by woes

 

I don't see a FAQ anywhere in the docmap -- am I in the right place?
http://www.annocpan.org/~MSTROUT/DBIx-Class-0.07000/lib/DBIx/Class/Manual/Do
cMap.pod 

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/20060802/915dc9ce/attachment.htm