[Dbix-class] Nested Selects and Getting SQL from Resultset

[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 => ['col1', 'col2', 'minutes']  } );
}

sub byMinGrouped {
  my $rs = byMin(shift);
  $rs->search ( {} , { select => [ 'col1', 'minutes' ], as => ['col1', 'minutes'], group_by => ['col1', 'minutes']  } );
}


If not..

Is it possible to get the raw sql from a resultset so that one can do something like

sub byMinGrouped {
  my $rs = byMin(shift);
  $rs->search ( {} , {
    from => $rs->toSQL ,
    select => [ 'col1', 'minutes' ], as => ['col1', 'minutes'], group_by => ['col1', 'minutes']
  } );
}

Thanks,
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.