- Patch #299267 by Crell: add extender support to the SELECT query builder.

4 files changed, 236 insertions, 3 deletions

diff --git a/modules/comment/comment.admin.inc b/modules/comment/comment.admin.inc
index 9a35014b9..8d88ec77e 100644
--- a/modules/comment/comment.admin.inc
+++ b/modules/comment/comment.admin.inc
@@ -66,13 +66,26 @@ function comment_admin_overview($type = 'new', $arg) {
     'operations' => array('data' => t('Operations')),
   );
 
-  $result = pager_query('SELECT c.subject, c.nid, c.cid, c.comment, c.timestamp, c.status, c.name, c.homepage, u.name AS registered_name, u.uid, n.title as node_title FROM {comment} c INNER JOIN {user} u ON u.uid = c.uid INNER JOIN {node} n ON n.nid = c.nid WHERE c.status = %d' . tablesort_sql($header), 50, 0, NULL, $status);
+    $query = db_select('comment', 'c');
+  $query->join('user', 'u', 'u.uid = c.uid');
+  $query->join('node', 'n', 'n.nid = c.nid');
+  $query->addField('u', 'name', 'registered_name');
+  $query->addField('n', 'title', 'node_title');
+  $query
+    ->fields('c', array('subject', 'nid', 'cid', 'comment', 'timestamp', 'status', 'name', 'homepage'))
+    ->fields('u', array('uid'))
+    ->condition('c.status', $status)
+    ->extend('PagerDefault')->extend('TableSort')
+    ->limit(50)
+    ->setHeader($header);
+  $result = $query->execute();
+
 
   // Build a table listing the appropriate comments.
   $options = array();
   $destination = drupal_get_destination();
 
-  while ($comment = db_fetch_object($result)) {
+  foreach ($result as $comment) {
     $options[$comment->cid] = array(
       'subject' => l($comment->subject, 'node/' . $comment->nid, array('attributes' => array('title' => truncate_utf8($comment->comment, 128)), 'fragment' => 'comment-' . $comment->cid)),
       'author' => theme('username', $comment),
diff --git a/modules/node/node.module b/modules/node/node.module
index 2f1d38bec..e601f2260 100644
--- a/modules/node/node.module
+++ b/modules/node/node.module
@@ -2024,7 +2024,18 @@ function node_build_multiple($nodes, $teaser = TRUE, $weight = 0) {
  * Menu callback; Generate a listing of promoted nodes.
  */
 function node_page_default() {
-  $nids = pager_query(db_rewrite_sql('SELECT n.nid, n.sticky, n.created FROM {node} n WHERE n.promote = 1 AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC'), variable_get('default_nodes_main', 10))->fetchCol();
+  $select = db_select('node', 'n')
+    ->fields('n', array('nid'))
+    ->condition('promote', 1)
+    ->condition('status', 1)
+    ->orderBy('sticky', 'DESC')
+    ->orderBy('created', 'DESC')
+    ->extend('PagerDefault')
+    ->limit(variable_get('default_nodes_main', 10))
+    ->addTag('node_access');
+
+  $nids = $select->execute()->fetchCol();
+
   if (!empty($nids)) {
     $nodes = node_load_multiple($nids);
     $build = node_build_multiple($nodes);
diff --git a/modules/simpletest/tests/database_test.module b/modules/simpletest/tests/database_test.module
index 573075078..2c1d22597 100644
--- a/modules/simpletest/tests/database_test.module
+++ b/modules/simpletest/tests/database_test.module
@@ -48,6 +48,19 @@ function database_test_menu() {
     'access callback' => TRUE,
     'page callback' => 'database_test_db_query_temporary',
   );
+  $items['database_test/pager_query_even'] = array(
+    'access callback' => TRUE,
+    'page callback' => 'database_test_even_pager_query',
+  );
+  $items['database_test/pager_query_odd'] = array(
+    'access callback' => TRUE,
+    'page callback' => 'database_test_odd_pager_query',
+  );
+  $items['database_test/tablesort'] = array(
+    'access callback' => TRUE,
+    'page callback' => 'database_test_tablesort',
+  );
+
   return $items;
 }
 
@@ -66,3 +79,80 @@ function database_test_db_query_temporary() {
   ));
   exit;
 }
+
+/**
+ * Run a pager query and return the results.
+ *
+ * This function does care about the page GET parameter, as set by the
+ * simpletest HTTP call.
+ */
+function database_test_even_pager_query($limit) {
+
+  $query = db_select('test', 't');
+  $query
+    ->fields('t', array('name'))
+    ->orderBy('age');
+
+  // This should result in 2 pages of results.
+  $query = $query->extend('PagerDefault')->limit($limit);
+
+  $names = $query->execute()->fetchCol();
+
+  drupal_json(array(
+    'names' => $names,
+  ));
+  exit;
+}
+
+/**
+ * Run a pager query and return the results.
+ *
+ * This function does care about the page GET parameter, as set by the
+ * simpletest HTTP call.
+ */
+function database_test_odd_pager_query($limit) {
+
+  $query = db_select('test_task', 't');
+  $query
+    ->fields('t', array('task'))
+    ->orderBy('pid');
+
+  // This should result in 4 pages of results.
+  $query = $query->extend('PagerDefault')->limit($limit);
+
+  $names = $query->execute()->fetchCol();
+
+  drupal_json(array(
+    'names' => $names,
+  ));
+  exit;
+}
+
+/**
+ * Run a tablesort query and return the results.
+ *
+ * This function does care about the page GET parameter, as set by the
+ * simpletest HTTP call.
+ */
+function database_test_tablesort() {
+  $header = array(
+    'tid' => array('data' => t('Task ID'), 'field' => 'tid', 'sort' => 'desc'),
+    'pid' => array('data' => t('Person ID'), 'field' => 'pid'),
+    'task' => array('data' => t('Task'), 'field' => 'task'),
+    'priority' => array('data' => t('Priority'), 'field' => 'priority', ),
+  );
+
+  $query = db_select('test_task', 't');
+  $query
+    ->fields('t', array('tid', 'pid', 'task', 'priority'));
+  
+  $query = $query->extend('TableSort')->setHeader($header);
+
+  // We need all the results at once to check the sort.
+  $tasks = $query->execute()->fetchAll();
+
+  drupal_json(array(
+    'tasks' => $tasks,
+  ));
+  exit;
+}
\ No newline at end of file
diff --git a/modules/simpletest/tests/database_test.test b/modules/simpletest/tests/database_test.test
index faaeb01ab..09d209bc4 100644
--- a/modules/simpletest/tests/database_test.test
+++ b/modules/simpletest/tests/database_test.test
@@ -1530,6 +1530,125 @@ class DatabaseSelectComplexTestCase extends DatabaseTestCase {
   }
 }
 
+class DatabaseSelectPagerDefaultTestCase extends DatabaseTestCase {
+
+  function getInfo() {
+    return array(
+      'name' => t('Pager query tests'),
+      'description' => t('Test the pager query extender.'),
+      'group' => t('Database'),
+    );
+  }
+
+  /**
+   * Confirm that a pager query returns the correct results.
+   *
+   * Note that we have to make an HTTP request to a test page handler
+   * because the pager depends on GET parameters.
+   */
+  function testEvenPagerQuery() {
+    // To keep the test from being too brittle, we determine up front
+    // what the page count should be dynamically, and pass the control
+    // information forward to the actual query on the other side of the
+    // HTTP request.
+    $limit = 2;
+    $count = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
+
+    $correct_number = $limit;
+    $num_pages = floor($count / $limit);
+
+    // If there is no remainder from rounding, subtract 1 since we index from 0.
+    if (!($num_pages * $limit < $count)) {
+      $num_pages--;
+    }
+    
+    for ($page = 0; $page <= $num_pages; ++$page) {
+      $this->drupalGet('database_test/pager_query_even/' . $limit, array('query' => array('page' => $page)));
+      $data = json_decode($this->drupalGetContent());
+
+      if ($page == $num_pages) {
+        $correct_number = $count - ($limit * $page);
+      }
+
+      $this->assertEqual(count($data->names), $correct_number, t('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
+    }
+  }
+
+  /**
+   * Confirm that a pager query returns the correct results.
+   *
+   * Note that we have to make an HTTP request to a test page handler
+   * because the pager depends on GET parameters.
+   */
+  function testOddPagerQuery() {
+    // To keep the test from being too brittle, we determine up front
+    // what the page count should be dynamically, and pass the control
+    // information forward to the actual query on the other side of the
+    // HTTP request.
+    $limit = 2;
+    $count = db_query("SELECT COUNT(*) FROM {test_task}")->fetchField();
+
+    $correct_number = $limit;
+    $num_pages = floor($count / $limit);
+
+    // If there is no remainder from rounding, subtract 1 since we index from 0.
+    if (!($num_pages * $limit < $count)) {
+      $num_pages--;
+    }
+
+    for ($page = 0; $page <= $num_pages; ++$page) {
+      $this->drupalGet('database_test/pager_query_odd/' . $limit, array('query' => array('page' => $page)));
+      $data = json_decode($this->drupalGetContent());
+
+      if ($page == $num_pages) {
+        $correct_number = $count - ($limit * $page);
+      }
+
+      $this->assertEqual(count($data->names), $correct_number, t('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
+    }
+  }
+}
+
+
+class DatabaseSelectTableSortDefaultTestCase extends DatabaseTestCase {
+
+  function getInfo() {
+    return array(
+      'name' => t('Tablesort query tests'),
+      'description' => t('Test the tablesort query extender.'),
+      'group' => t('Database'),
+    );
+  }
+
+  /**
+   * Confirm that a tablesort query returns the correct results.
+   *
+   * Note that we have to make an HTTP request to a test page handler
+   * because the pager depends on GET parameters.
+   */
+  function testTableSortQuery() {
+    $sorts = array(
+      array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
+      array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
+      array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
+      array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
+      // more elements here
+
+    );
+
+    foreach ($sorts as $sort) {
+      $this->drupalGet('database_test/tablesort/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
+      $data = json_decode($this->drupalGetContent());
+
+      $first = array_shift($data->tasks);
+      $last = array_pop($data->tasks);
+
+      $this->assertEqual($first->task, $sort['first'], t('Items appear in the correct order.'));
+      $this->assertEqual($last->task, $sort['last'], t('Items appear in the correct order.'));
+    }
+  }
+}
+
 /**
  * Select tagging tests.
  *