<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><div>Is there a way to augment the buglist.cgi SQL query <b><i>other</i></b> than buglist_columns and buglist_column_joins? I want to do a multi-table join (e.g., bugs -> A -> B -> C.*), but I don't like the idea of doing the following because it seems <b><i>really</i></b> inefficient:</div><div><br></div></div><blockquote class="webkit-indent-blockquote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 40px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"># MyExtension/Extension.pm</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">...</span></font></div></blockquote><blockquote class="webkit-indent-blockquote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 40px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">sub <b><u>buglist_columns</u></b> {</span></font></div></div><div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> my ( $self, $args ) = @_;</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> my $columns = $args->{'columns'};</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> $columns->{'<font class="Apple-style-span" color="#0096fb"><b>c_val1</b></font>'} = {</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> 'name' => '<font class="Apple-style-span" color="#0096fb"><b>subquery_hack1</b></font>.<font class="Apple-style-span" color="#0096fb"><b>c_value1</b></font>',</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> };</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> $columns->{'<font class="Apple-style-span" color="#ff7e7b"><b>c_val2</b></font>'} = {</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> 'name' => '<font class="Apple-style-span" color="#ff7e7b"><b>subquery_hack2</b></font>.<font class="Apple-style-span" color="#ff7e7b"><b>c_value2</b></font>',</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> };</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">}</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">sub <b><u>buglist_column_joins</u></b> {</span></font></div></div><div></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> my ( $self, $args ) = @_;</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> my $joins = $args->{'column_joins'};</span></font></div></div><div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> $joins->{'<font class="Apple-style-span" color="#0096fb"><b>c_val1</b></font>'} = {</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> from => 'bug_id',</span></font></div></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> to => '</span></font><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px; ">bug_id</span><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">'.</span></font></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> table => <<<font class="Apple-style-span" color="#44901d"><b>END</b></font>;</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"><i><font class="Apple-style-span" color="#919191"> (SELECT bugs.bug_id, </font><font class="Apple-style-span" color="#0096fb"><b>c.value1 AS </b><b>c_value1</b></font></i></span></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> FROM bugs</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> JOIN A ON A.a_id = </i></font><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px; "><i><font class="Apple-style-span" color="#919191">bugs.a_id</font></i></span></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> JOIN B ON B.a_id = A.a_id</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> JOIN C ON C.b_id = B.b_id</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> WHERE ...)</i></font></div><div><font class="Apple-style-span" color="#44901d" face="'Courier New'" style="font-size: 13px;"><b>END</b></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> as => '<font class="Apple-style-span" color="#0096fb"><b>subquery_hack1</b></font>',</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> join => 'LEFT',</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> };</span></font></div></div><div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> $joins->{'<font class="Apple-style-span" color="#ff7e7b"><b>c_val2</b></font>'} = {</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> from => 'bug_id',</span></font></div></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> to => '</span></font><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px; ">bug_id</span><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">'.</span></font></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> table => <<<font class="Apple-style-span" color="#44901d"><b>END</b></font>;</span></font></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"><i><font class="Apple-style-span" color="#919191"> (SELECT </font></i></span></font><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px; "><i><font class="Apple-style-span" color="#919191">bugs.bug_id</font></i></span><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"><i><font class="Apple-style-span" color="#919191">, </font><font class="Apple-style-span" color="#ff7e7b"><b>c.value2 AS </b><b>c_value2</b></font></i></span></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> FROM bugs</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px; "><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px; "><i> JOIN A ON A.a_id = </i></font><span class="Apple-style-span" style="font-family: 'Courier New'; font-size: 13px; "><i><font class="Apple-style-span" color="#919191">bugs.a_id</font></i></span></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> JOIN B ON B.a_id = A.a_id</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> </i></font><span class="Apple-style-span" style="color: rgb(145, 145, 145); font-family: 'Courier New'; font-size: 13px; "><i>INNER</i></span><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> JOIN C ON C.b_id = B.b_id</i></font></div><div><font class="Apple-style-span" color="#919191" face="'Courier New'" style="font-size: 13px;"><i> WHERE ...)</i></font></div><div><font class="Apple-style-span" color="#44901d" face="'Courier New'" style="font-size: 13px;"><b>END</b></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> as => '<font class="Apple-style-span" color="#ff7e7b"><b>subquery_hack2</b></font>',</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> join => 'LEFT',</span></font></div></div><div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;"> };</span></font></div></div><div></div><div><font class="Apple-style-span" face="'Courier New'"><span class="Apple-style-span" style="font-size: 13px;">}</span></font></div></div></blockquote><div><div><br></div><div>I tried to find a semi-obvious places to do this (or where to hack this into place), but I haven't been able to find a good candidate. Even a single subquery hack would be preferable to the above (which seems to require one per distinct buglist column).</div></div><div><br></div><div>As always, any guidance or nudge in the right direction would be greatly appreciated.</div><div>
<br>
<br>
--Matt<br>
<br>
</div>
<br></body></html>