Tag: pagination

Speed up your pagination with a simple hack…

March 17th, 2011 · · CakePHP 7 comments - Tags: , , , ,

Before I go into the example in this little post, let me just say that this situation won’t be applicable to everyone…

But let’s consider the following:
We have a table with tens of thousands of records, that need to be paginated.

As you know, cake will execute two queries; first to get the count of total records, second to get the actual records.

The questions one might ask are:
“Would any user really go through 5 thousand pages to find what they are looking for?”
“If I bring the last 1,000 records wouldn’t that be enough for a vast majority of needs?”
“How many pages in Google do you go through, when searching for something, before you give up?”
“Is it not better to provide filters, or search tools to help your users narrow down the results to something manageable?”

If you’ve answered “Yes” to two or more questions, please consider the hack…

In your model, which needs to be paginated, do the following:

public function paginateCount($conditions = null,
                                 $recursive = 0,
                                 $extra = array()) {
   return 1000;
}

Yep, we are overriding paginateCount() and simply returning 1,000 because we know that this will be the maximum amount of records that our paginator needs to know about.
Depending on how complex the underlying query is (for example you might have JOIN’s or various conditions, which would usually need to be taken into the account in your typical count query), the above hack can dramatically increase the performance of your pagination.

Let's help out CakePHP's pagination

January 30th, 2010 · · CakePHP 15 comments - Tags: , , , ,

While it would be sweet, if the paginated data was usually not much more involved than a simple SELECT * FROM some_table… Unfortunately it is not.

In many cases there are a few, JOINs to related models, and likely a GROUP BY statement somewhere (maybe DISTINCT ?, who knows).

What I am getting at, is an SQL issue if that’s what you’d call it…

count(*) (which CakePHP uses properly, indeed) will yield pretty strange results if you have JOINs and GROUP BY (or any variation thereof… or “worse” yet, things like HAVING, etc.).

The display of records to the end-user will “disagree” with the number, which count(*) produces.
This is because count(*) actually counts everything (meaning across various models in the case of a JOIN).
The main issue comes up when one attempts to paginate the result-set from a complex query. You might see only 20 records displayed due to GROUP BY, but count(*) will actually return 43 (for example) because it does not actually count in the same way SELECT returns the records.

However, with a little help this problem is quite easily resolved…

Remember, we can always override the paginateCount() method inside a model.

For example, if we have an Order Model, just to get the expected count of records we’ll add:

public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    if($this->statusId) {
      $conditions = array('conditions' => array('Order.order_status_id' => $this->statusId));
    }
    $results = $this->find('count', array_merge(array('recursive' => -1), $conditions));
    return $results;
  }

With this scenario, we can get an accurate count of all orders, or just the ones that have some specific status.

The bottom line is that we know exactly what needs to be counted, and overriding paginateCount() allows us to do just that, regardless of what the actual record fetching query is like.