From d92b9d91400f4a026dbb22b2c8a4f4af4eba2c3e Mon Sep 17 00:00:00 2001
From: jamiesensei <jamiesensei>
Date: Mon, 21 May 2007 09:26:17 +0000
Subject: [PATCH] new sql to fetch question counts for categories all in one
 sql query. Question count now included in drop down box.

---
 lib/questionlib.php | 15 +++++++++++----
 1 file changed, 11 insertions(+), 4 deletions(-)

diff --git a/lib/questionlib.php b/lib/questionlib.php
index 1c819030ad..ebd60fd906 100644
--- a/lib/questionlib.php
+++ b/lib/questionlib.php
@@ -1680,11 +1680,17 @@ function question_category_options($contexts, $top = false, $currentcat = 0, $po
 
 
     $categories = get_records_sql("
-            SELECT *
-            FROM {$CFG->prefix}question_categories
-            WHERE contextid IN ($contextslist)
+            SELECT c.*, count(q.id) as questioncount
+            FROM {$CFG->prefix}question_categories as c
+            LEFT JOIN {$CFG->prefix}question as q ON c.id = q.category
+            WHERE c.contextid IN ($contextslist) AND 
+            (q.id IS NULL OR (q.hidden='0' AND q.parent='0'))
+            GROUP BY c.id
             ORDER BY parent, sortorder, name ASC");
+
+
     $categories = question_add_context_in_key($categories);
+
     if ($top){
         $categories = question_add_tops($categories, $pcontexts);
     }
@@ -1698,7 +1704,8 @@ function question_category_options($contexts, $top = false, $currentcat = 0, $po
             if ($category->contextid == $pcontext){ 
                 $cid = $category->id;
                 if ($currentcat!= $cid) {
-                    $categoriesarray[$contextstring][$cid] = $category->indentedname;
+                    $countstring = (!empty($category->questioncount))?"($category->questioncount)":'';
+                    $categoriesarray[$contextstring][$cid] = $category->indentedname.$countstring;
                 }
             }
         }
-- 
2.39.5