From f301c9241af577f8f3df94338f1329d91c82d252 Mon Sep 17 00:00:00 2001 From: "mkanat%bugzilla.org" <> Date: Sat, 1 Jul 2006 23:45:53 +0000 Subject: Bug 343248: SQL query to get all keywords with bug count is duplicated in editkeywords.cgi and describekeywords.cgi Patch By Remi Zara r=mkanat, a=justdave --- Bugzilla/Keyword.pm | 45 +++++++++++++++++++++++ describekeywords.cgi | 14 +------ editkeywords.cgi | 13 +------ template/en/default/admin/keywords/list.html.tmpl | 2 +- template/en/default/filterexceptions.pl | 2 +- template/en/default/reports/keywords.html.tmpl | 11 +++--- 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 + + 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 @@ [% keyword.description %] - [% IF keyword.bugcount > 0 %] + [% IF keyword.bug_count > 0 %] Search [% ELSE %] @@ -64,9 +65,9 @@ [% END %] - [% IF keyword.bugcount > 0 %] + [% IF keyword.bug_count > 0 %] - [% keyword.bugcount %] + [% keyword.bug_count %] [% ELSE %] none [% END %] -- cgit v1.2.3-24-g4f1b