[Dbix-class] group_by woes

[Jason Galea]

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