[Dbix-class] group_by woes

Jess Robinson castaway at desert-island.demon.co.uk
Sun Jul 30 11:51:45 CEST 2006


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/
>> 
>
>



More information about the Dbix-class mailing list