Changeset 1116 for trunk

Timestamp:

03/21/10 15:45:34 (2 years ago)

Author:

bogdan2412

Message:

Make rankings work slightly better for single rounds.

Added a round_id, score, user_id index to ia_score_user_round.
Make MySQL use that index for single round rankings by removing
the GROUP BY and SUM(score).

Moved identity_can from common/ into the macro, where it's supposed
to be.

Location:

trunk

Files:

• common/db/score.php (modified) (2 diffs)
• www/macros/macro_rankings.php (modified) (5 diffs)

trunk/common/db/score.php

@@ -78 +78 @@
     }
     if ($round != null) {
-        if (is_string($round))
+        if (is_string($round)) {
             $round = array($round);
-
-        if (is_array($round) && count($round) > 0) {
-            $rounds_where = "(`id` IN (" . db_escape_array($round) . "))";
-            $query = "SELECT `id`, `state`, `public_eval` FROM `ia_round` WHERE " . $rounds_where;
-            $round_objects = db_fetch_all($query);
-            $allowed_round_ids = array();
-
-            foreach ($round_objects as $round) {
-                if (identity_can('round-view-scores', $round)) {
-                    $allowed_round_ids[] = $round["id"];
-                }
-            }
-
-            if (count($allowed_round_ids) == 0) {
+        }
+
+        if (is_array($round)) {
+            if (count($round) > 0) {
+                $where[] = "(`round_id` IN (" . db_escape_array($round) . "))";
+            } else {
                 $where[] = "(TRUE = FALSE)";
-            } else {
-                $where[] = "(`round_id` IN (" . db_escape_array($allowed_round_ids) . "))";
             }
         }
@@ -370 +360 @@
 // if detail_task == true, extra columns for each task will be created
 // if detail_round == true, extra columns for each round will be created
-function score_get_rankings($rounds, $tasks, $start = 0, $count = 999999, $detail_task = false, $detail_round = false)
-{
+function score_get_rankings($rounds, $tasks, $start = 0, $count = 999999,
+                            $detail_task = false, $detail_round = false) {
+    if (count($rounds) == 0) {
+        return array();
+    }
     $where = score_build_where_clauses(null, null, $rounds);
 
     // Get the total score for all rounds
-    $query = "SELECT SUM(score) AS score, user_id, ia_user.username AS user_name, ia_user.full_name AS user_full,
-                    ia_user.rating_cache AS user_rating
-                FROM ia_score_user_round
-                LEFT JOIN ia_user ON ia_user.id = ia_score_user_round.user_id
-                WHERE ".implode('AND', $where)."
-                GROUP BY `user_id`
-                ORDER BY score DESC
-                LIMIT ".db_escape($start).", ".db_escape($count);
+    $query = "
+        SELECT ".(count($rounds) > 1 ? "SUM(score) AS score" : "score").",
+                user_id, ia_user.username AS user_name,
+                ia_user.full_name AS user_full,
+                ia_user.rating_cache AS user_rating
+        FROM ia_score_user_round
+        LEFT JOIN ia_user ON ia_user.id = ia_score_user_round.user_id
+        WHERE".implode('AND', $where)."
+        ".(count($rounds) > 1 ? "GROUP BY `user_id`" : "")."
+        ORDER BY score DESC
+        LIMIT ".db_escape($start).", ".db_escape($count);
 
     $rankings = db_fetch_all($query);

trunk/www/macros/macro_rankings.php

@@ -52 +52 @@
     foreach ($round_param as $param) {
         $round = preg_split('/\s*\:\s*/', $param);
+        if (!identity_can('round-view-scores', round_get($round[0]))) {
+            continue;
+        }
         array_push($rounds, array(
             'round_id' => $round[0],
             'round_name' => getattr($round, 1, ' ')
-            )
-        );
+        ));
     }
 
@@ -72 +74 @@
             'key' => 'user_full',
             'rowform' => create_function_cached('$row',
-                                         'return format_user_normal($row["user_name"], $row["user_full"], $row["user_rating"]);'),
+                'return format_user_normal($row["user_name"], $row["user_full"], $row["user_rating"]);'),
         ),
     );
@@ -83 +85 @@
 
             if ($detail_task == true) {
-            $new_tasks = round_get_tasks($round_id, null, null, null, null, null);
-            foreach ($new_tasks as $task) {
-                array_push($columns, array(
-                    'name' => $task['id'],
-                    'type' => 'task',
-                    'title' => $task['title']
-                ));
-                array_push($tasks, $task);
+                $new_tasks = round_get_tasks($round_id);
+                foreach ($new_tasks as $task) {
+                    array_push($columns, array(
+                        'name' => $task['id'],
+                        'type' => 'task',
+                        'title' => $task['title']
+                    ));
+                    array_push($tasks, $task);
                 }
             }
@@ -110 +112 @@
             'rowform' => create_function_cached('$row', 'return round($row[\''.$column['name'].'\']);'),
             'css_class' => 'number score'
-            ));
+        ));
     }
 
@@ -118 +120 @@
     }
     array_push($column_infos, array(
-            'title' => $total,
-            'key' => 'score',
-            'rowform' => create_function_cached('$row', 'return round($row[\'score\']);'),
-            'css_class' => 'number score'
-        )
-    );
+        'title' => $total,
+        'key' => 'score',
+        'rowform' => create_function_cached('$row', 'return round($row[\'score\']);'),
+        'css_class' => 'number score'
+    ));
 
     $round_ids = array();