optimize SQL on rate edit screen

1 files changed, 19 insertions, 11 deletions

diff --git a/httemplate/edit/rate.cgi b/httemplate/edit/rate.cgi
index ce56768..1771f01 100644
--- a/httemplate/edit/rate.cgi
+++ b/httemplate/edit/rate.cgi
@@ -17,12 +17,12 @@ my %granularity = (
   '60' => 'minute',
 );
 
-my $nous = <<END;
-  WHERE 0 < ( SELECT COUNT(*) FROM rate_prefix
-               WHERE rate_region.regionnum = rate_prefix.regionnum
-                 AND countrycode != '1'
-            )
-END
+#my $nous = <<END;
+#  WHERE 0 < ( SELECT COUNT(*) FROM rate_prefix
+#               WHERE rate_region.regionnum = rate_prefix.regionnum
+#                 AND countrycode != '1'
+#            )
+#END
 
 %>
 
@@ -56,11 +56,17 @@ Rate plan
 </TR>
 
 <% foreach my $rate_region (
-     qsearch( 'rate_region',
-              {},
-              '',
-              "$nous ORDER BY regionname",
-            )
+     sort { lc($a->regionname) cmp lc($b->regionname) }
+     qsearch({
+               'select'    => 'DISTINCT ON ( regionnum ) rate_region.*',
+               'table'     => 'rate_region',
+               'addl_from' => 'INNER JOIN rate_prefix USING ( regionnum )',
+               'hashref'   => {},
+               'extra_sql' => "WHERE countrycode != '1'",
+                              # 'ORDER BY regionname'
+                              # ERROR: SELECT DISTINCT ON expressions must
+                              #        match initial ORDER BY expressions
+            })
    ) {
      my $n = $rate_region->regionnum;
      my $rate_detail =
@@ -70,6 +76,7 @@ Rate plan
                                 'sec_granularity' => '60'
                               };
 %>
+
   <TR>
     <TD><A HREF="<%=$p%>edit/rate_region.cgi?<%= $rate_region->regionnum %>"><%= $rate_region->regionname %></A></TD>
     <TD><%= $rate_region->prefixes_short %></TD>
@@ -82,6 +89,7 @@ Rate plan
         <% } %>
       </SELECT>
   </TR>
+
 <% } %>
 
 <TR>