Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Phalcon with large database

I have news_item table with 176541 rows. This is result witch i run in phpMyAdmin

phpMyAdmin

But i run paging in backend with news_item, i can't get result return. It run always (i set timeout 3000). This is my indexAction (backend/news/index)

<?php 
function indexAction(){
        $currentPage        = 1;
        $myUri              = null;
        $menu_id            = null;
        $keyword            = null;
        $conditions         = null;
        if($this->request->get("page")):
            $currentPage = (int)$this->request->get("page");
        endif;
        //menu item
        $item       = Menu::find(array("conditions" => "type in (2,8,9)",
                                        "order"     => "sort ASC",
                                        "columns" => "id, title, sort, parents"));
        $recursive  = new Recursive($item->toArray());
        $menu       = $recursive->buildArray(0);
        $this->view->setVar("menu",$menu);

        //SELECT ITEM IN ONE CATEGORY
        $this->view->setVar("cid", $menu_id);
        if($this->request->get("cid")){
            $menu_id    = $this->request->get("cid");
            $conditions .= ' menu_id = '.(int)$menu_id;
            $this->view->setVar("cid", $menu_id);
        }
        //FOR SEARCH
        if($this->request->get("keyword")){
            $keyword    = $this->request->get("keyword");
            $conditions .= ' title LIKE \'%'.(string)$keyword."%'";
            $this->view->setVar("keyword", $keyword);
        }
        $items      = News::find(array(
                                "conditions" => $conditions,
                                "order"      => "id DESC",
                                "columns"    => "id,title,created,sort,menu_id,special,status,featured"
                            ));
        $paginator = new \Phalcon\Paginator\Adapter\Model(
            array(
                "data" => $items,
                "limit"=> 10,
                "page" => $currentPage
            )
        );
        // Get the paginated results
        $page       = $paginator->getPaginate();
        $myUri      = '&cid='.$menu_id.'&keyword='.$keyword;
        $this->view->setVar("myUri", $myUri);
        $this->view->setVar("page",$page);
    }
?>

Certainly, my action run with several hundred rows. My friend has same problem, he used CI framwork is ok.

Test:
- Localhost / Win7 / RAM 2G
- Xampp: PHP Version 5.6.8 / Server version: 5.6.24

Could you please help me :( Thanks Phalcon so much.



7.3k

This is a problem of Pagination work in Phalcon. As you see, firstly you get all records. Then Paginator make slice of this result.

You can use custom paginator, which will find only page limit number of records.

Because of this problem, I refused Phalcon\Paginator

Did you use the like statement when you access this page?

You can use custom paginator, which will find only page limit number of records.

It mean, i only display 2 button: before and next. i can't show page 1, page 2 page 3... Total page.

Could you please give me more info ?

Did you use the like statement when you access this page?

My action run like when has keyword. Default it doesn't run like :(

The problem you're having is that you're retrieving all the records first with find(), then you're paginating. It's like pulling an entire filing cabinet into your office, then pulling out one file - it's a lot more work than necessary.

What you want to do is change your approach so you just retrieve the desired page. Look at this section in the documentiation: https://docs.phalconphp.com/en/latest/reference/pagination.html#adapters-usage, specifically the 3rd example with using the QueryBuilder. This technique changes the query used to retrieve a page, rather than paging an already retrieve resultset.

@Oleg - I imagine you're doing the same thing as the OP

It's like pulling an entire filing cabinet into your office, then pulling out one file - it's a lot more work than necessary.

i know this, but i don't know how to fix.

Thank you so so so much. It works very well.

edited Jul '15

U better use modelsManager. See in Phalcon doc. Here an example:

        // Build query and Paginator
        $builder = $this->modelsManager->createBuilder()
            ->from("Models\Logs")
            ->orderBy('created_at DESC');

        $paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
            "builder" => $builder,
            "limit" => 12, // Or u can use some predefined value
            "page" => $this->request->getQuery('page', 'int')
        ));

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

In view (phtml or volt) u must use page value. Like this:

    {% if page.items|length > 0 %}
        {% for item in page.items %}
        ...
        {% endfor %}
    {% endif %}

And paginator :

    {% if page is defined and page.total_pages > 1 %}
      <form action="" method="get" class="pagination">
          <span>Current page: {{ page.current }}</span>

          {% if page.before != page.current %}
              <a href="?page={{page.before}}">Prev</a>
          {% endif %}

          {% if page.next != page.current %}
              <a href="?page={{page.next}}">Next</a>
          {% endif %}

          <fieldset>
              Or specific page: <input type="number" name="page" placeholder="1 / {{ page.total_pages }}"  min="1" max="{{ page.total_pages }}" value="" />
          </fieldset>
      </form>
    {% endif %}

Good luck!



7.3k

@quasipickle

I don't know is it aactual or not, but when I use , for example

$paginator = new \Phalcon\Paginator\Adapter\Model([
            "data"  => KitPart::find(),
            "limit" => 10,
            "page"  => 1,
            ]);
        $page = $paginator->getPaginate();

I have in database monitor

Connect   [email protected] as anonymous on inventroy
                    8 Query     SELECT `part`.`id`, `part`.`title`, `part`.`description`,  `part`.`updated_at` FROM `part`
                    8 Query     SELECT `part`.`id`, `part`.`title`, `part`.`description`,  `part`.`updated_at` FROM `part`

Other words, just query full data and duplicated (issue). Phalcon 1.3.... (Because project was started on 1.3)

I hope this issue has solved in new Phalcon

Therefore I use custom pagination, which querying page length records only.

@Oleg - you're doing a KitPart::find() before the pagination is done. That is the problem.

That's fine, but not every example is suitable for every application - as you've seen. That's why I suggested the OP read the documentation:

Look at this section in the documentiation: https://docs.phalconphp.com/en/latest/reference/pagination.html#adapters-usage, specifically the 3rd example with using the QueryBuilder.

This will probably be applicable to you as well.



7.3k

@quasipickle

Yes I agree. Somewhere crept error.

But the meaning is the same.

In \Phalcon\Paginator\Adapter\Model we get all data : KitPart::find() (as general)

I.e. if our model has 1000000 records, for paginator we get 1000000 records, after get only, for example first 30 (page length).

I use cutom paginator, where I get only page length slice from model fro each page. Of course, I just use limit and offset.