We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Paginator

Pagination optimization

Hey guys,

New guy to phalcon here, sorry I tried to RTFM https://docs.phalcon.io/en/latest/reference/pagination.html but nothing of help there nor on the forum

The way I get it, Phalcon retrieves all data first, then displays whatever result based on the cursor and other pagination settings. The thing is my data set is quite large and the query is taking forever. Think of any way to retrieve only what you want to display based on the settings?

I'm thinking of an optional "SELECT COUNT(*)..." query first to get the number of pages (optional because we are talking optimization here) and then "SELECT .... LIMIT x OFFSET y"...

Cheers



5.7k
Accepted
answer
edited Jun '15

You need to use the Query Builder : https://docs.phalcon.io/en/latest/reference/pagination.html#adapters-usage

Check out the example //Passing a querybuilder as data

$builder = $this->modelsManager->createBuilder()
    ->columns('id, name')
    ->from('Robots')
    ->orderBy('name');

$paginator = new PaginatorQueryBuilder(array(
    "builder" => $builder,
    "limit"   => 20,
    "page"    => 1
));

What will only query the data you need for that specific page.

Then, pass the paginated info to the view.

$this->view->myStuff = $paginator->getPaginate();

Now in your view, you can access all the paginated data : https://docs.phalcon.io/en/latest/reference/pagination.html#page-attributes

For example, lets loop through your list of results:

// Volt Example
{% for item in myStuff.items %}
Item : {{ item.id }} :: {{ item.name }} <br />
{% endfor %}


31.1k
edited Jun '15

Your know how pagination work so I think your issue here is not the pagination but rather optimizing the query which is taking way too long.

If you search on the internet on optimizing database queries you will get articles describing how you can perfectly optimize Database select satament.

You can then convert the approach in Phalcon way



1.0k
in reply to Dominic Kinyanjui

Steven was actually right, the QueryBuilder enables a "... LIMIT ... OFFSET ..."-like behaviour when retrieving the sets instead of retrieving it all and paginating after in PHP, thus making the execution much faster

Your know how pagination work so I think your issue hear is not the pagination but rather optimizing the query which is taking way too long.

If you search on the internet on optimizing database queries you will get articles describing how you can perfectly optimize Database select satament.

You can then convert the approach in Phalcon way