5

I have a query that is extended by PagerDefault. I need to get the total count of results to show something like: "There are a total of 100 results, now showing 21-40". I can't figure out where to get that number without building a separate query to run that doesn't use PagerDefault.

First place I looked was theme_pager(), which I'm already overriding in template.php. The total result count is not passed there, only the current page (as $variables['element']).

I see that within includes/pager.inc in pager_default_initialize() only the current page number is sent to theme_pager, although the total items are passed to that function. (So close!)

Then I thought I could clone the query, but the query must be extended prior to adding conditions, so that wouldn't work. I'll note that this isn't ideal either, because ideally, I would be able to get this total count without two db hits (as it would hit it for the count every time a user navigates the pagination).

So, does anyone know the best way to get the total count?

Thanks!

6

With the existing pager class I don't think you can get that data without running the count query again. The total count is queried directly and saved to a local var before being passed off to pager_default_initialize()...it's not saved anywhere.

One option would be to extend the default class and use that for your pager queries. This is un-tested but I think it'll do the trick

class MyPager extends PagerDefault {

  /**
   * Overrides PagerDefault::execute().
   */
  public function execute() {
    // Add convenience tag to mark that this is an extended query. We have to
    // do this in the constructor to ensure that it is set before preExecute()
    // gets called.
    if (!$this->preExecute($this)) {
      return NULL;
    }

    // A NULL limit is the "kill switch" for pager queries.
    if (empty($this->limit)) {
      return;
    }
    $this->ensureElement();

    // Get the total items from our new statically cached function.
    $total_items = $this->getTotalItemCount();

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

    // Now that we've added our pager-based range instructions, run the query normally.
    return $this->query->execute();
  }

  /**
   * Cache the results of PagerDefault::getCountQuery().
   */
  public function getTotalItemCount() {
    static $total_items = NULL;

    if ($total_items === NULL) {
      $total_items = $this->getCountQuery()->execute()->fetchField();
    }

    return $total_items;
  }

}

You could then construct a query like the following

$query = db_select('table', 't')
  ->fields('t')
  ->condition('foo', 'bar')
  ->extend('MyPager');

$current_items = $query->execute();
$total_items = $query->getTotalItemCount();

There might be an easier way, but I just can't see it.

  • No, I think this is the easiest, and best, way. Especially when I was considering duplicating the query without extend(), which I was very much against. It requires an extra db hit, but you've mitigated that as much as possible by using static. I can't edit the post because there isn't enough changes to be made, but return $this->query->execute() is missing the ending semi-colon. Works perfectly, thanks! – Chris Rockwell Jul 25 '13 at 18:59
  • Glad it worked :) I added the semi-colon in, thanks – Clive Jul 25 '13 at 22:35
1

After executing a query extending PagerDefault, you can at any time after that (even between different functions) call a global that stored the total product count.

// $results = $paged_query->execute();
// OR
// $results = some_function_that_executes_a_paged_query();
global $pager_total_items;
$count = $pager_total_items[0];

This works in Drupal 7 as far as I know, and I have had to use it before when having searches or paginated displays that needed a total count as well. I believe this avoids us having to use a whole second separate query.

This is the most "formal documentation" I could find on it from Drupal. https://api.drupal.org/api/drupal/developer%21globals.php/global/pager_total_items/7.x https://api.drupal.org/api/drupal/core!globals.api.php/global/pager_total_items/8.2.x

0

You can get the total count of a query that is extended with PagerDefault with

  $num_rows = $query->countQuery()->execute()->fetchField();

This query fetches all nodes with pager and counts the total nodes

function _test() {
  $query = db_select('node', 'n')
          ->extend('PagerDefault');
  $query->addField('n', 'nid');
  $num_rows = $query->countQuery()->execute()->fetchField();
  $results = $query->execute();
  foreach ($results as $result) {
    drupal_set_message($result->nid); // print each row
    // do fancier stuff here
  }
  drupal_set_message($num_rows); // print total rows
  return 'test';
}

See http://dropbucket.org/node/414

  • This solution works only on the first page of the listing. – t1gor Feb 25 '14 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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