I'm trying to do the following
i have a table with 3 columns.... col1, col2 & time
I want to create some methods that I can pass dbix resultsets into
that will generate new tables
the first byMin takes the time field and blanks out the seconds, generating a
new column called 'minutes'
the second method is designed to take the output from the first resultset and
group by the newly formed minutes column.
However it doesnt work - it complains that minutes does not exist.
So its obviously not attempting to generate a nested select for me.
Is there anyway to do this?
sub byMin {
my $rs = shift;
$rs->search (
{ } ,
{
select => [ 'col1', 'col2', 'strftime("%H%M:00", time) as minutes' ],
as => ['col1', 'col2', 'minutes'] } );
}
(This generates expected SQL like ...
"SELECT col1, col2, strftime("%H:%M:00", time) as minutes FROM mytable where
(strftime('%H', time) is not null"
sub byMinGrouped {
my $rs = byMin(shift);
$rs->search ( { 'strftime(\'%H\',time)' => {'!=', undef} } , {
select => [ 'minutes', 'count(*) as count)' ],
as => ['minutes', 'count'],
group_by => ['minutes'] } );
}
This generated SQL like ...
"SELECT minutes, count(*) as count FROM mytable where (strftime('%H', time) is
not null ) group by minutes"
This is NOT the SQL Im expecting... Im expecting the first result to become a
subquery for the second. This seems to be a merge of the two queries.
So I'm hoping to generate SQL such as
"SELECT minutes, count(*) as count FROM (SELECT col1, col2,
strftime("%H:%M:00", time) as minutes FROM mytable where (strftime('%H', time)
is not null) group by minutes"
-------------------
Yes, See DBIx::Class::Manual::FAQ, the section about using "AS" in the queries.
Next time please create a new email instead of replying to an old one, as
threaded email clients display your new question at the bottom of a long and
possibly ignored thread of another question. Thanks!
Jess
----------------
not quite sure what you mean, I am using 'as'. Could you explain what I might
be doing wrong.
Additionally, I am using version 7.005 at the moment. We have 8.010 installed
here at but it seems to be broken (I dont get any useful error messages).
------------------
Ive been doing some hacking and added a method (asSQL) to ResultSet.pm so that
I can now do:
$rs1 = $rs->search( ...)
$rs2 ->search( { } , { select => [], from => $rs2->asSQL } );
Which is solving my problem for now but I suspect is unessecary ..
Tom
--------------------------------------------------------
NOTICE: If received in error, please destroy and notify sender. Sender does not
intend to waive confidentiality or privilege. Use of this email is prohibited
when received in error.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
