Bug 343248: SQL query to get all keywords with bug count is duplicated in editkeywords.cgi and describekeywords.cgi

Patch By Remi Zara <remi_zara@mac.com> r=mkanat, a=justdave

6 files changed, 57 insertions, 30 deletions

diff --git a/Bugzilla/Keyword.pm b/Bugzilla/Keyword.pm
index 6ba72479c..2e0f19f4c 100644
--- a/Bugzilla/Keyword.pm
+++ b/Bugzilla/Keyword.pm
@@ -38,6 +38,16 @@ use constant DB_TABLE => 'keyworddefs';
 
 sub description       { return $_[0]->{'description'}; }
 
+sub bug_count {
+    ($_[0]->{'bug_count'}) ||=
+      Bugzilla->dbh->selectrow_array('SELECT COUNT(keywords.bug_id) AS bug_count
+                                        FROM keyworddefs
+                                   LEFT JOIN keywords
+                                          ON keyworddefs.id = keywords.keywordid
+                                       WHERE keyworddefs.id=?', undef, ($_[0]->id));
+    return $_[0]->{'bug_count'};
+}
+
 ###############################
 ####      Subroutines    ######
 ###############################
@@ -48,6 +58,29 @@ sub keyword_count {
     return $count;
 }
 
+sub get_all_with_bug_count {
+    my $class = shift;
+    my $dbh = Bugzilla->dbh;
+    my $keywords =
+      $dbh->selectall_arrayref('SELECT ' . join(', ', DB_COLUMNS) . ',
+                                       COUNT(keywords.bug_id) AS bug_count
+                                  FROM keyworddefs
+                             LEFT JOIN keywords
+                                    ON keyworddefs.id = keywords.keywordid ' .
+                                  $dbh->sql_group_by('keyworddefs.id',
+                                                     'keyworddefs.name,
+                                                      keyworddefs.description') . '
+                                 ORDER BY keyworddefs.name', {'Slice' => {}});
+    if (!$keywords) {
+        return [];
+    }
+    
+    foreach my $keyword (@$keywords) {
+        bless($keyword, $class);
+    }
+    return $keywords;
+}
+
 1;
 
 __END__
@@ -64,6 +97,8 @@ Bugzilla::Keyword - A Keyword that can be added to a bug.
 
  my $description = $keyword->description;
 
+ my $keywords = Bugzilla::Keyword->get_all_with_bug_count();
+
 =head1 DESCRIPTION
 
 Bugzilla::Keyword represents a keyword that can be added to a bug.
@@ -87,6 +122,16 @@ implements.
  Params:      none
  Returns:     An integer, the count of keywords.
 
+=item C<get_all_with_bug_count()> 
+
+ Description: Returns all defined keywords. This is an efficient way
+              to get the associated bug counts, as only one SQL query
+              is executed with this method, instead of one per keyword
+              when calling get_all and then bug_count.
+ Params:      none
+ Returns:     A reference to an array of Keyword objects, or an empty
+              arrayref if there are no keywords.
+
 =back
 
 =cut
diff --git a/describekeywords.cgi b/describekeywords.cgi
index e06350d2e..12c57ea7f 100755
--- a/describekeywords.cgi
+++ b/describekeywords.cgi
@@ -27,25 +27,15 @@ use lib ".";
 use Bugzilla;
 use Bugzilla::Error;
 use Bugzilla::User;
+use Bugzilla::Keyword;
 
 Bugzilla->login();
 
 my $cgi = Bugzilla->cgi;
-my $dbh = Bugzilla->dbh;
 my $template = Bugzilla->template;
 my $vars = {};
 
-my $keywords = $dbh->selectall_arrayref(
-                   q{SELECT keyworddefs.name, keyworddefs.description,
-                            COUNT(keywords.bug_id) AS bugcount
-                       FROM keyworddefs
-                  LEFT JOIN keywords
-                         ON keyworddefs.id = keywords.keywordid } .
-         $dbh->sql_group_by('keyworddefs.id',
-                            'keyworddefs.name, keyworddefs.description') .
-                 " ORDER BY keyworddefs.name", {'Slice' => {}});
-
-$vars->{'keywords'} = $keywords;
+$vars->{'keywords'} = Bugzilla::Keyword->get_all_with_bug_count();
 $vars->{'caneditkeywords'} = UserInGroup("editkeywords");
 
 print Bugzilla->cgi->header();
diff --git a/editkeywords.cgi b/editkeywords.cgi
index 9696480bd..7b906c30b 100755
--- a/editkeywords.cgi
+++ b/editkeywords.cgi
@@ -27,6 +27,7 @@ use Bugzilla;
 use Bugzilla::Constants;
 use Bugzilla::Util;
 use Bugzilla::Error;
+use Bugzilla::Keyword;
 
 my $cgi = Bugzilla->cgi;
 my $dbh = Bugzilla->dbh;
@@ -79,17 +80,7 @@ $vars->{'action'} = $action;
 
 
 if ($action eq "") {
-    my @keywords;
-
-    $vars->{'keywords'} =
-      $dbh->selectall_arrayref('SELECT keyworddefs.id, keyworddefs.name,
-                                       keyworddefs.description,
-                                       COUNT(keywords.bug_id) AS bug_count
-                                  FROM keyworddefs
-                             LEFT JOIN keywords
-                                    ON keyworddefs.id = keywords.keywordid ' .
-                                  $dbh->sql_group_by('id', 'name, description') . '
-                                 ORDER BY keyworddefs.name', {'Slice' => {}});
+    $vars->{'keywords'} = Bugzilla::Keyword->get_all_with_bug_count();
 
     print $cgi->header();
     $template->process("admin/keywords/list.html.tmpl", $vars)
diff --git a/template/en/default/admin/keywords/list.html.tmpl b/template/en/default/admin/keywords/list.html.tmpl
index 84eb6e9f1..999538561 100755
--- a/template/en/default/admin/keywords/list.html.tmpl
+++ b/template/en/default/admin/keywords/list.html.tmpl
@@ -22,7 +22,7 @@
   #%]
 
 [%# INTERFACE:
-  # keywords: array of hashes having the properties:
+  # keywords: array keyword objects having the properties:
   #   - id: number. The ID of the keyword.
   #   - name: string. The name of the keyword.
   #   - description: string. The description of the keyword.
diff --git a/template/en/default/filterexceptions.pl b/template/en/default/filterexceptions.pl
index 19f040c52..eee3b8963 100644
--- a/template/en/default/filterexceptions.pl
+++ b/template/en/default/filterexceptions.pl
@@ -114,7 +114,7 @@
 
 'reports/keywords.html.tmpl' => [
   'keyword.description', 
-  'keyword.bugcount', 
+  'keyword.bug_count', 
 ],
 
 'reports/report-table.csv.tmpl' => [
diff --git a/template/en/default/reports/keywords.html.tmpl b/template/en/default/reports/keywords.html.tmpl
index c4aee3cae..979c50163 100644
--- a/template/en/default/reports/keywords.html.tmpl
+++ b/template/en/default/reports/keywords.html.tmpl
@@ -21,10 +21,11 @@
   #%]
 
 [%# INTERFACE:
-  # keywords: array of hashes. May be empty. Each has has three members:
+  # keywords: array keyword objects. May be empty. Each has has four members:
+  #   id: id of the keyword
   #   name: the name of the keyword
   #   description: keyword description. May be HTML.
-  #   bugcount: number of bugs with that keyword
+  #   bug_count: number of bugs with that keyword
   # caneditkeywords: boolean. True if this user can edit keywords
  %]
 
@@ -56,7 +57,7 @@
     </th>
     <td>[% keyword.description %]</td>
     <td align="center">
-      [% IF keyword.bugcount > 0 %]
+      [% IF keyword.bug_count > 0 %]
         <a href="buglist.cgi?keywords=[% keyword.name FILTER url_quote %]&amp;resolution=---">
           Search</a>
       [% ELSE %]
@@ -64,9 +65,9 @@
       [% END %]
     </td>
     <td align="right">
-      [% IF keyword.bugcount > 0 %]
+      [% IF keyword.bug_count > 0 %]
         <a href="buglist.cgi?keywords=[% keyword.name FILTER url_quote %]">
-          [% keyword.bugcount %]</a>
+          [% keyword.bug_count %]</a>
       [% ELSE %]
         none
       [% END %]