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(
      $result->status ? l($result->title, "node/{$result->merchant_nid}") : $result->title,
  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?

  • This looks like the d6 approach and has been asked before. see the approved answer in this thread. I think you want to change your query to use db_select() and the TableSort Extender.
    – tenken
    May 30, 2012 at 1:23
  • I don't think dynamic query is the answer. I wish there is an option to use with static query as we did in D6.
    – Sivaji
    Jun 4, 2012 at 7:06

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.