On 7/30/06, Jess
Robinson <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
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/[email protected]/
>> >
>>
>> _______________________________________________
>> 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/[email protected]/
>>
>
>
_______________________________________________
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/[email protected]/
