[Dbix-class] group_by woes

[Sarah Berry]

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