Custom pagination query in CakePHP

C

My pet peeve list seems to grow on a regular basis.  One of them is poorly optimized code.  Not only is the code I’m going to discuss poorly optimized, it’s from the documentation of CakePHP!

If you search for creating custom pagination in CakePHP, you will find quite a few results, potentially this one.  The one that I would assume the best would be the one from CakePHP itself, but it’s not and let me show you why.

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!

Other useful articles

About the author

  • tg

    I see what you are saying but in no way would I burden myself with the speed times of SELECT COUNT(), I’d just start up a counterScope and be on my way. Much faster, much simpler.

  • http://www.maplestorymesosshop.com maple story mesos

    this is exactly the post I needed to see!

  • http://mischorradas.nishilua.com mischorradas

    I thought the same when I read cakephp’s page.

    @tg: the counterScope tells how many related elements it has, so you would need to specify more concretely how: accessing a parent,… furthemore, if you play with constrains on retrieved data, the counterScope will be useless. (I think. If you have a counterexample tell 😉

  • zahid

    where is pagination implementation in CakePhp

  • http://www.itelmobiledialer.com Masud

    can anyone show me any example using $this->query().
    where page limit is woking. thanks

  • http://www.gamcc.com/2moons/ 2moons dil

    This is my first time comment at your blog. Good recommended website.

  • http://abcoder.com Adnan

    CakePHP Advanced Pagination – sort by derived field
    http://abcoder.com/php/cakephp/cakephp-advanced-pagination-sort-by-derived-field/

  • http://www.bonzzay.com Yises

    Could you explain how to do a pagination in cake with a custom sql query? (but one which allows to use the paginator->prev())
    Thank you!!

  • http://www.nike-sb-air-max-shoes.com/nike-air-max-turbulence-c-22/ Nike Air Max Turbulence

    It’s important to take responsibilities, because no one else is going to do it for you

  • Pingback: makeup

  • Pingback: skin tags removal at home

  • Pingback: top rc zone

  • Pingback: how to keep fit

  • Pingback: Goozle Zone

  • Pingback: bad credit loans

  • Pingback: one buck resume

  • Pingback: ultimate power profits

  • Pingback: zig zagz

  • Pingback: Motorbike Accident Claim Solicitors

  • Pingback: Personal Injury Solicitors in Manchester

  • Pingback: ZigZag

  • Pingback: calaguas tour

  • Pingback: ZCode System

  • Pingback: new york asian escorts

  • Pingback: Recycle

  • Pingback: Studios

  • Pingback: Elite

  • Pingback: Climate

  • Pingback: Collecting

By Jamie

My Books