Index: common/db/job.php
===================================================================
--- common/db/job.php	(revision 948)
+++ common/db/job.php	(working copy)
@@ -94,7 +94,7 @@
         $field_list .= ", job.file_contents";
     }
     $query = sprintf("
-              SELECT $field_list 
+              SELECT $field_list
               FROM ia_job AS job
               LEFT JOIN `ia_task` AS `task` ON `job`.`task_id` = `task`.`id`
               LEFT JOIN `ia_user` AS `user` ON `job`.`user_id` = `user`.`id`
@@ -127,8 +127,9 @@
     return $sql;
 }
 
-// Returns a where clause array based on complex filters.
-function job_get_range_wheres($filters) {
+// Returns a where clause array based on complex filters
+// that relate only to ia_job table
+function job_get_range_wheres_job($filters) {
     $user = getattr($filters, 'user');
     $task_hidden = getattr($filters, 'task_hidden');
 
@@ -149,9 +150,6 @@
     if (!is_null($task)) {
         $wheres[] = sprintf("`job`.`task_id` = '%s'", db_escape($task));
     }
-    if (!is_null($user)) {
-        $wheres[] = sprintf("`user`.`username` = '%s'", db_escape($user));
-    }
     if (!is_null($round)) {
         $wheres[] = sprintf("`job`.`round_id` = '%s'", db_escape($round));
     }
@@ -178,15 +176,42 @@
     if (!is_null($status)) {
         $wheres[] = sprintf("`job`.`status` = '%s'", db_escape($status));
     }
+    if (!is_null($eval_msg)) {
+        $wheres[] = sprintf("`job`.`eval_message` LIKE '%s%%'", db_escape($eval_msg));
+    }
+
+    return $wheres;
+}
+
+// Returns a where clause array based on complex filters
+// that are not related to ia_job table
+function job_get_range_wheres($filters) {
+    $user = getattr($filters, 'user');
+    $task_hidden = getattr($filters, 'task_hidden');
+
+    $task = getattr($filters, 'task');
+    $round = getattr($filters, 'round');
+    $job_begin = getattr($filters, 'job_begin');
+    $job_end = getattr($filters, 'job_end');
+    $job_id = getattr($filters, 'job_id');
+    $time_begin = getattr($filters, 'time_begin');
+    $time_end = getattr($filters, 'time_end');
+    $compiler = getattr($filters, 'compiler');
+    $status = getattr($filters, 'status');
+    $score_begin = getattr($filters, 'score_begin');
+    $score_end = getattr($filters, 'score_end');
+    $eval_msg = getattr($filters, 'eval_msg');
+
+    $wheres = array("TRUE");
+    if (!is_null($user)) {
+        $wheres[] = sprintf("`user`.`username` = '%s'", db_escape($user));
+    }
     if (!is_null($score_begin) && is_whole_number($score_begin)) {
         $wheres[] = sprintf("(`job`.`score` >= '%s') AND (`round`.`public_eval` = 1)", db_escape($score_begin));
     }
     if (!is_null($score_end) && is_whole_number($score_end)) {
         $wheres[] = sprintf("(`job`.`score` <= '%s') AND (`round`.`public_eval` = 1)", db_escape($score_end));
     }
-    if (!is_null($eval_msg)) {
-        $wheres[] = sprintf("`job`.`eval_message` LIKE '%s%%'", db_escape($eval_msg));
-    }
     if (!is_null($task_hidden) && is_whole_number($task_hidden)) {
         $wheres[] = sprintf("`task`.`hidden` = %s", db_escape($task_hidden));
     }
@@ -200,48 +225,106 @@
     log_assert(is_whole_number($range));
     log_assert($start >= 0);
     log_assert($range >= 0);
-    $query = <<<SQL
-SELECT `job`.`id`,
-       `job`.`user_id` AS `user_id`,
-       `job`.`round_id` AS `round_id`,
-       `job`.`task_id` AS `task_id`, 
-       `job`.`submit_time`, 
-       `job`.`compiler_id`, 
-       `job`.`status`,
-       `job`.`score`, 
-       `job`.`eval_message`,
-       `job`.`eval_log`,
-       OCTET_LENGTH(`job`.`file_contents`) AS `job_size`,
-       `user`.`username` AS `user_name`, 
-       `user`.`full_name` AS `user_fullname`,
-       `task`.`page_name` AS `task_page_name`, 
-       `task`.`title` AS `task_title`,
-       `task`.`hidden` AS `task_hidden`, 
-       `task`.`user_id` AS `task_owner_id`,
-       `task`.`open_source` AS `task_open_source`,
-       `round`.`page_name` AS `round_page_name`, 
-       `round`.`title` AS `round_title`,
-       `round`.`public_eval` AS `round_public_eval`
-#       (CASE WHEN `status` = 'processing' THEN
-#                (SELECT `value` FROM `ia_parameter_value`
-#                 WHERE `ia_parameter_value`.`object_id` = `task_id` AND
-#                       `ia_parameter_value`.`object_type` = 'task' AND
-#                       `ia_parameter_value`.`parameter_id` = 'tests')
-#                ELSE NULL END) AS `total_tests`,
-#       (CASE WHEN `status` = 'processing' THEN
-#                (SELECT COUNT(*) FROM `ia_job_test`
-#                 WHERE `ia_job_test`.`job_id` = `job`.`id`)
-#                ELSE NULL END) AS `done_tests`
-      FROM `ia_job` AS `job`
-      LEFT JOIN `ia_user` AS `user` ON `job`.`user_id` = `user`.`id`
-      LEFT JOIN `ia_task` AS `task` ON `job`.`task_id` = `task`.`id`
-      LEFT JOIN `ia_round` AS `round` ON `job`.`round_id` = `round`.`id`
-SQL;
 
     $wheres = job_get_range_wheres($filters);
-    $query .= " WHERE (".implode(") AND (", $wheres).")";
-    $query .= sprintf(" ORDER BY `job`.`submit_time` DESC LIMIT %s, %s", $start, $range);
+    $wheres_job = job_get_range_wheres_job($filters);
 
+    if (!isset($wheres[1])) {
+        // if we have no filters outside of `ia_job` table then optimize query
+        $query = "
+        SELECT
+            `job`.`id`,
+            `job`.`user_id` AS `user_id`,
+            `job`.`round_id` AS `round_id`,
+            `job`.`task_id` AS `task_id`,
+            `job`.`submit_time`,
+            `job`.`compiler_id`,
+            `job`.`status`,
+            `job`.`score`,
+            `job`.`eval_message`,
+            `job`.`eval_log`,
+            OCTET_LENGTH(`job`.`file_contents`) AS `job_size`,
+            `user`.`username` AS `user_name`,
+            `user`.`full_name` AS `user_fullname`,
+            `task`.`page_name` AS `task_page_name`,
+            `task`.`title` AS `task_title`,
+            `task`.`hidden` AS `task_hidden`,
+            `task`.`user_id` AS `task_owner_id`,
+            `task`.`open_source` AS `task_open_source`,
+            `round`.`page_name` AS `round_page_name`,
+            `round`.`title` AS `round_title`,
+            `round`.`public_eval` AS `round_public_eval`
+        #    (CASE WHEN `status` = 'processing' THEN
+        #        (SELECT `value` FROM `ia_parameter_value` WHERE
+        #            `ia_parameter_value`.`object_id` = `task_id` AND
+        #            `ia_parameter_value`.`object_type` = 'task' AND
+        #            `ia_parameter_value`.`parameter_id` = 'tests')
+        #        ELSE NULL END) AS `total_tests`,
+        #    (CASE WHEN `status` = 'processing' THEN
+        #        (SELECT COUNT(*) FROM `ia_job_test`
+        #            WHERE `ia_job_test`.`job_id` = `job`.`id`)
+        #        ELSE NULL END) AS `done_tests`
+        FROM ";
+
+        $query .= sprintf("
+            (   SELECT *
+                FROM `ia_job`
+                WHERE (" . implode(") AND (", $wheres_job) . ")
+                ORDER BY `submit_time` DESC LIMIT %s, %s
+            ) AS `job`", $start, $range);
+        $query .= "
+            LEFT JOIN `ia_user` AS `user` ON `job`.`user_id` = `user`.`id`
+            LEFT JOIN `ia_task` AS `task` ON `job`.`task_id` = `task`.`id`
+            LEFT JOIN `ia_round` AS `round` ON `job`.`round_id` = `round`.`id`
+        ";
+        $query .= "WHERE (".implode(") AND (", $wheres).")";
+
+    } else {
+        // we have filters outside of `ia_job` table, we can't query in query
+        $query = "
+        SELECT
+            `job`.`id`,
+            `job`.`user_id` AS `user_id`,
+            `job`.`round_id` AS `round_id`,
+            `job`.`task_id` AS `task_id`,
+            `job`.`submit_time`,
+            `job`.`compiler_id`,
+            `job`.`status`,
+            `job`.`score`,
+            `job`.`eval_message`,
+            `job`.`eval_log`,
+            OCTET_LENGTH(`job`.`file_contents`) AS `job_size`,
+            `user`.`username` AS `user_name`,
+            `user`.`full_name` AS `user_fullname`,
+            `task`.`page_name` AS `task_page_name`,
+            `task`.`title` AS `task_title`,
+            `task`.`hidden` AS `task_hidden`,
+            `task`.`user_id` AS `task_owner_id`,
+            `task`.`open_source` AS `task_open_source`,
+            `round`.`page_name` AS `round_page_name`,
+            `round`.`title` AS `round_title`,
+            `round`.`public_eval` AS `round_public_eval`
+        #    (CASE WHEN `status` = 'processing' THEN
+        #        (SELECT `value` FROM `ia_parameter_value` WHERE
+        #            `ia_parameter_value`.`object_id` = `task_id` AND
+        #            `ia_parameter_value`.`object_type` = 'task' AND
+        #            `ia_parameter_value`.`parameter_id` = 'tests')
+        #        ELSE NULL END) AS `total_tests`,
+        #    (CASE WHEN `status` = 'processing' THEN
+        #        (SELECT COUNT(*) FROM `ia_job_test`
+        #            WHERE `ia_job_test`.`job_id` = `job`.`id`)
+        #        ELSE NULL END) AS `done_tests`
+        FROM
+            `ia_job` AS `job`
+            LEFT JOIN `ia_user` AS `user` ON `job`.`user_id` = `user`.`id`
+            LEFT JOIN `ia_task` AS `task` ON `job`.`task_id` = `task`.`id`
+            LEFT JOIN `ia_round` AS `round` ON `job`.`round_id` = `round`.`id`
+        ";
+
+        $query .= "WHERE (".implode(") AND (", $wheres).") AND (".implode(") AND (", $wheres_job).")";
+        $query .= sprintf(" ORDER BY `job`.`submit_time` DESC LIMIT %s, %s", $start, $range);
+    }
+
     return db_fetch_all($query);
 }
 
@@ -282,7 +365,7 @@
                      db_escape($job_id), db_escape($test_number));
     db_query($query);
     $query = sprintf("INSERT INTO ia_job_test
-                     (job_id, test_number, test_group, exec_time, mem_used, 
+                     (job_id, test_number, test_group, exec_time, mem_used,
                       grader_exec_time, grader_mem_used, points, grader_message)
                      VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                      db_escape($job_id), db_escape($test_number), db_escape($test_group),
@@ -293,10 +376,10 @@
 
 // Returns an array of test informations for a job, ordered by test group
 function job_test_get_all($job_id) {
-    $query = sprintf("SELECT * FROM `ia_job_test` 
+    $query = sprintf("SELECT * FROM `ia_job_test`
                       WHERE job_id = '%s' ORDER BY test_group, test_number",
-                     db_escape($job_id));  
+                     db_escape($job_id));
     return db_fetch_all($query);
 }
 
-?>
+?>
\ No newline at end of file
