<HTML >
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16705" name=GENERATOR></HEAD>
<BODY >
<DIV>
<DIV>
<P> I'm trying to do the following</P>
<P><FONT size=2> i have a table with 3 columns.... col1, col2 &
time</FONT></P>
<P><FONT size=2> I want to create some methods that I can pass dbix
resultsets into </FONT></P>
<P><FONT size=2> that will generate new tables</FONT></P>
<P><FONT size=2> the first byMin takes the time field and blanks out the
seconds, generating a new column called 'minutes'</FONT></P>
<P><FONT size=2> the second method is designed to take the output from the
first resultset and group by the newly formed minutes column.</FONT></P>
<P><FONT size=2> However it doesnt work - it complains that minutes does
not exist.</FONT></P>
<P><FONT size=2> So its obviously not attempting to generate a nested
select for me.</FONT></P>
<P><FONT size=2> Is there anyway to do this?</FONT></P>
<P><FONT face=Arial size=2></FONT> </P>
<P><FONT size=2> sub byMin {</FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN> my $rs =
shift;</FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN>$rs->search (
</FONT></P>
<P><FONT size=2>{<SPAN class=994272410-27102008> </SPAN><SPAN
class=994272410-27102008> </SPAN>} , </FONT></P>
<P><FONT size=2>{ </FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN>select => [
'col1', 'col2', 'strftime("%H%M:00", </FONT><FONT size=2> time)<SPAN
class=994272410-27102008> as minutes</SPAN>' ], </FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN>as
=> ['col1', 'col2', 'minutes'] } ); </FONT></P>
<P>}</P>
<P><SPAN class=994272410-27102008><FONT size=2> (This generates expected
SQL like ...<FONT face=Arial> </FONT></FONT></SPAN></P>
<P><FONT face=Arial><FONT size=2><SPAN class=994272410-27102008>"</SPAN><SPAN
class=994272410-27102008>SELECT col1, col2, strftime("%H:%M:00", time) as
minutes FROM mytable where (strftime('%H', time) is not
null"</SPAN></FONT></FONT></P>
<P><FONT size=2></FONT> </P>
<P> <FONT size=2>sub byMinGrouped {</FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN> my $rs =
byMin(shift);</FONT></P>
<P><FONT size=2> <SPAN class=994272410-27102008> </SPAN>$rs->search
( {<SPAN class=994272410-27102008> 'strftime(\'%H\',time)' => {'!=',
undef} </SPAN>} , { </FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008>
</SPAN>select => [ '<SPAN class=994272410-27102008>minutes</SPAN>', '<SPAN
class=994272410-27102008>count(*) as count)</SPAN>' ], </FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008> </SPAN>as
=> ['<SPAN class=994272410-27102008>minutes</SPAN>', <SPAN
class=994272410-27102008> </SPAN>'<SPAN class=994272410-27102008>count'</SPAN>],
</FONT></P>
<P><FONT size=2><SPAN class=994272410-27102008>
</SPAN>group_by => ['minutes'] } );</FONT></P>
<P><FONT size=2>}</FONT></P>
<P><SPAN class=994272410-27102008><FONT size=2>This generated SQL
like ... </FONT></SPAN></P>
<P><FONT size=2><SPAN class=994272410-27102008>"</SPAN>SELECT <SPAN
class=994272410-27102008>minutes, </SPAN>count(*) as count FROM <SPAN
class=994272410-27102008>mytable where (strftime('%H', time) is not null ) group
by minutes"</SPAN></FONT></P>
<P><SPAN class=994272410-27102008><FONT size=2></FONT></SPAN> </P>
<P><SPAN class=994272410-27102008><FONT size=2>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.</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT size=2>So I'm hoping to generate SQL
such as </FONT></SPAN></P><SPAN class=994272410-27102008><FONT size=2>
<P><FONT size=2><SPAN class=994272410-27102008>"</SPAN>SELECT <SPAN
class=994272410-27102008>minutes, </SPAN>count(*) as count
FROM </FONT><FONT face=Arial><SPAN class=994272410-27102008>(</SPAN><SPAN
class=994272410-27102008>SELECT col1, col2, strftime("%H:%M:00", time) as
minutes FROM mytable where (strftime('%H', time) is not null)
</SPAN></FONT><FONT size=2><SPAN class=994272410-27102008> group by
minutes"</SPAN></FONT></P></FONT></SPAN>
<P><SPAN class=994272410-27102008><FONT
size=2>-------------------</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT size=2></FONT></SPAN> </P>
<P><FONT size=2>Yes, See DBIx::Class::Manual::FAQ, the section about using "AS"
in the queries.</FONT></P>
<P><FONT size=2>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!</FONT></P>
<P><FONT size=2>Jess</FONT></P>
<P><FONT size=2></FONT> </P>
<P><SPAN class=994272410-27102008><FONT
size=2>----------------</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT size=2>not quite sure what you mean, I
am using 'as'. Could you explain what I might be doing wrong.</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT size=2>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).</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial
size=2>------------------</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial size=2>Ive been doing some
hacking and added a method (asSQL) to ResultSet.pm so that I can
now do:</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial size=2>$rs1 = $rs->search(
...) </FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial size=2>$rs2
->search( { } , { select => [], from => $rs2->asSQL
} );</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial size=2>Which is solving my
problem for now but I suspect is unessecary .. </FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial
size=2></FONT></SPAN> </P>
<P><SPAN class=994272410-27102008><FONT face=Arial size=2>Tom</FONT></SPAN></P>
<P><SPAN class=994272410-27102008><FONT face=Arial
size=2></FONT></SPAN> </P>
<P><FONT size=2></FONT> </P></DIV></DIV>
<DIV>
<HR>
</DIV>
<P CLASS="BulletedList" STYLE="MARGIN: 0in 0in 0pt; TEXT-INDENT: 0in; mso-list: none; tab-stops: .5in"><SPAN STYLE="FONT-SIZE: 8pt; COLOR: gray; mso-bidi-font-family: Arial"><FONT FACE="Arial" COLOR="gray" SIZE="1">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.</FONT></SPAN></P>
<DIV>
</DIV></BODY></HTML>