doctrine/DoctrineModule — pull request #414 by bakura10 (2014-05-12 08:44:59+00:00)
Title: Add caching to paginator adapters

Hi,

I'm sorry to push this again but this is REALLY needed. I've experienced a lot of duplicated COUNT queries because of this. I've investigated a bit and here is why there are multiple queries. In ZfrRest for instance, we are doing two things in the renderer:

1. We iterate through the pagiantor to get each items:

```php
foreach ($paginator as $item) {
}
```

2. We get the total count of items when rendering the metadata, using the `getTotalItemCount` method.

This triggers two count queries (and while trigger one additional each time you iterate again, or call the getTotalItemCount again).

I've looked through the paginator code, and it appears that it puts in cache the current items per page: https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Paginator.php#L439

Which means that the query that actually retrieves records is executed only once. However, the count is made at several places:

* Here, for getting the number of total items: https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Paginator.php#L356

* And here, for calculating the total number of page count: https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Paginator.php#L881

As you can see, even a trivial use will trigger two queries! And a naive use of paginator may potentially trigger a lot of useless queries without knowing anything.

Clearly, this has to be done in the adapters. I've checked other built-in adapters, and that's exactly what it is done in Zend Db adapter: https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php#L101

I clearly know what you will say: if you persist new records or add items to the Collection (which can be a PersistentCollection so it may triggers additional calls!!), then the count is outdated. Sure. But I think that's really not the typical use case. Typical use case of pagiantor is to iterate through it either in a view, or in a custom renderer, so at the very end of the request. However, potentially triggering hundreds of useless count calls is, to my opinion, much worse.

This is really really needed, I'd be even ok to add a flag to disable this behavior, but really, we need this!