[Dbix-class] DBIx::Class

[Howe, Tom (IT)]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20081027/a17957c2/attachment.htm