Pagination

[leevigraham]

There's two pagination bundles in the wild that seem to be widely used:

• https://github.com/whiteoctober/Pagerfanta (most widely used)
• https://github.com/KnpLabs/KnpPaginatorBundle (not actively maintained until recently)

Doctrine also implements a Paginator which PagerFanta implements. https://doctrine-orm.readthedocs.org/en/latest/tutorials/pagination.html

symfony2-standard-template is currently implementing PagerFanta via the HateoasBundle.

There are a couple of issues with PagerFanta which we discovered in Costimator. Namely large complex queries are really slow with Pagination. We solved this by just returning the entity ids and then passing them through to the Pagination bundle.

[ghost]

I didn't have that much to do with the pagination in Costimator, but from my understanding the root of the issue is the fact that pagination bundles typically get the total number of results first then from that get the page results (i.e. LIMIT 50, 150)?

The processing time is on account that in some cases we're using many joins and/or there are many results and these results will be hydrated by doctrine? Returning just id's for the first query solved this issue.

If this is the case, why do we need to use a different TP bundle at all? Could we use our own, simple one with Hateoas?

[leevigraham]

Pagerfanta is basically a wrapper for Doctrines ORM pagination implementation now as far as I can tell with a couple of extra template tags.

The issue is the three queries needed to determine pagination:

• Perform a Count query using DISTINCT keyword.
• Perform a Limit Subquery with DISTINCT to find all ids of the entity in from on the current page.
• Perform a WHERE IN query to get all results for the current page.

Our queries selected a lot of data which slowed the queries down quite a bit. We ended up creating a simple adapter to get the ids and then select the entities by ID only joining / selecting the required relationships.

why do we need to use a different TP bundle at all?

Pagerfanta is pretty well battle tested and provides helpers for count, number of pages etc. We could probably replace it with something home grown but then we would need to consider how to handle the HATEOS integration

[leevigraham]

There are a couple of issues with PagerFanta which we discovered in Costimator. Namely large complex queries are really slow with Pagination.

After doing a bit more reading this is an issue with Pagination not Pagerfanta.

[leevigraham]

The PagerFanta DoctrineORM adapter is pretty simple: https://github.com/whiteoctober/Pagerfanta/blob/master/src/Pagerfanta/Adapter/DoctrineORMAdapter.php and so is the actual pagination class: https://github.com/whiteoctober/Pagerfanta/blob/master/src/Pagerfanta/Pagerfanta.php

There is a bundle which provides a bunch of helpers but they are probably not needed: https://github.com/whiteoctober/WhiteOctoberPagerfantaBundle

[leevigraham]

Still thinking about this :smiley_cat: From what I can tell so far here are the rules for performant pagination (basically what Saxman implemented):

1. The query passed to the Adpater (PagerFanta) or Paginator (Doctrine) should not:
   • fetch joined collections (joining collections is ok)
   • only select distinct ids
2. Return the current page ids from the query result and build a full query for the page results.

I think we could extend the Paginator additional methods found in:

• http://laravel.com/api/class-Illuminate.Pagination.Paginator.html or
• https://github.com/whiteoctober/Pagerfanta/blob/master/src/Pagerfanta/Pagerfanta.php