Custom pagination query in CakePHP

First, here is the link describing custom pagination:

http://book.cakephp.org/view/249/Custom-Query-Pagination

To accomplish pagination with custom code, it's quite simple.  In your model you create two functions:

1. function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array())
2. function paginateCount($conditions = null, $recursive = 0, $extra = array())

Inside of those function contains your custom queries that you would like to perform.  This can be a $this->query or a $this->find, whatever suits your needs.

If you continue to read the documentation, inside the paginateCount function, the example does a standard query and returns the count of results!  Not only that it has to use a DISTINCT!  The combination of these two items could not make your pagination count slower.  I suppose in the example they use, the distinct is kind of important, but returning all results and retrieving the count is not.

Does it achieve the correct results?  Yes, it does, but you can achieve the same results in 10 times less the time, if not 100 times less depending on the size of the data.

The example from CakePHP must first return the data from the database server, if the record size is large enough this can take time.  Next CakePHP, needs to perform it's regular post query functions on the data.  Again, large record sets take time.

Instead, if you simply use a SELECT COUNT() function, the database server only has to return 1 record and CakePHP only needs to process one record.

I saw this example used in an ASP.NET project where a page would take over 2 minutes to load because it was returning a count of over 10,000 records.  This is not CakePHP related, simply database related.  But changing that query from returning the number of records to a COUNT() made the page load instaneously!  Over two minutes to instant, I think the results speak for themselves.

To conclude, if you wish to use custom pagination in CakePHP, it's a great idea if you can't accomplish it with the built-in tools with CakePHP, but ensure you use a SELECT COUNT() instead of returning the count of records!

Published on Apr 13, 2009

Tags: CakePHP Tutorial | count()