Is theme('pager') designed to work only with dynamic query?

Question

I have a custom callback function that queries data from a custom table.

The trimmed version of code that I have been writing looks as below,

function cb_user_cashback_orders($account) {
  $sql = 'SOME SQL QUERY';
  $results = db_query($sql, array(':uid' => $account->uid));
  //date, Merchant name, commission, status
  $header = array(t('Date'), t('Merchant Name'), t('Commission'), t('Status'));
  $rows = array();
  foreach ($results as $result) {
    $rows[] = array(
      format_date($result->timestamp),
      $result->status ? l($result->title, "node/{$result->merchant_nid}") : $result->title,
      $result->commission,
      $result->status,
    );
  }
  pager_default_initialize(count($rows), 10, 0); // Pager doesn't appear without this call
  $output = theme('table', array(
    'header' => $header,
    'rows' => $rows,
    'empty' => t('You have not made any purchase yet.'),
    'attributes' => array('class' => array('cb-user-orders')),
    )
  );
  $output .= theme('pager'); // as a replacement for pager_query() in D6.
  return $output;
}

I'm trying to figure out how I could get the pager work as expected.

With the code above I'm able to get the pager links (first, next, last, etc.) however the table always show the same set of items (infact all items) irrespective of pager argument (?page=1,2 OR N).

Would like to hear some input to fix this. Is theme('pager') designed to work only with dynamic query?

Answer 1

theme('pager') is not designed to work only with dynamic queries. The documentation for page_default_initialize() shows example code where dynamic queries are not used.

  // First find the total number of items and initialize the pager.
  $where = "status = 1";
  $total = mymodule_select("SELECT COUNT(*) FROM data " . $where)->result();
  $num_per_page = variable_get('mymodule_num_per_page', 10);
  $page = pager_default_initialize($total, $num_per_page);

  // Next, retrieve and display the items for the current page.
  $offset = $num_per_page * $page;
  $result = mymodule_select("SELECT * FROM data " . $where . " LIMIT %d, %d", $offset, $num_per_page)->fetchAll();
  $output = theme('mymodule_results', array('result' => $result));

  // Finally, display the pager controls, and return.
  $output .= theme('pager');
  return $output;

The other example given in that documentation page shows code that uses an external service to retrieve information which is shown. Also in that case, dynamic queries are not used. (The external service could not even running Drupal.)

The only difference I can see between that code, and the PagerDefault class is that PagerDefault uses a unique ID that passes as last argument of page_default_initialize().

/**
 * The unique ID of this pager on this page.
 *
 * @var int
 */
protected $element = NULL;

$total_items = $this->getCountQuery()->execute()->fetchField();
$current_page = pager_default_initialize($total_items, $this->limit, $this->element);
$this->range($current_page * $this->limit, $this->limit);

The other difference is that, as PagerDefault uses dynamic queries, its query can be altered with hook_query_alter(). In this case, as the added tag is pager, the query can be also altered with hook_query_pager_alter() which is a hook_query_TAG_alter() hook.

  public function __construct(SelectQueryInterface $query, DatabaseConnection $connection) {
    parent::__construct($query, $connection);

    // Add pager tag. Do this here to ensure that it is always added before
    // preExecute() is called.
    $this->addTag('pager');
  }