CODEWITHSUNDEEP
cakephp
napolin
napolin

Reputation: 75

Pagination in CakePHP 2.0

I use CakePHP 2.x and have a thread with many posts in it, which I want to paginate. But when I take a look into the sql-log, there is a SELECT-statement which affects ALL the posts (25k) of the thread. Actually there are only 20 Posts displayed, so is there a way to avoid this overhead?

Here is the code of my ThreadsController

public function view($id = null) {
    $this->Thread->id = $id;
    $this->paginate = array(
        'conditions' => array(
            'thread_id' => $id,
        ),
        'order' => array(
            'Post.id' => 'DESC')
    );
    if (!$this->Thread->exists()) {
        throw new NotFoundException(__('Invalid thread'));
    }

    $this->set('thread', $this->Thread->read(null, $id));
    $this->set('posts', $this->paginate('Post'));
}

Here is the SQL-query which affects all the 25k rows:

SELECT Post.id, Post.user_id, Post.post, Post.created, Post.modified, Post.thread_id FROM ppv3.posts AS Post WHERE Post.thread_id = (1)

Upvotes: 0

Views: 6627

Answers (2)

Arun Jain
Arun Jain

Reputation: 5464

Everything is fine with your code except that you did not defined the "limit" in pagination i.e. how many records you want to fetch for a single page.

Your controller's method should looks like:

public function view($id = null) {
$this->Thread->id = $id;
$this->paginate = array(
    'limit' => 20, // this was the option which you forgot to mention
    'conditions' => array(
        'thread_id' => $id,
    ),
    'order' => array(
        'Post.id' => 'DESC')
);
if (!$this->Thread->exists()) {
    throw new NotFoundException(__('Invalid thread'));
}

$this->set('thread', $this->Thread->read(null, $id));
$this->set('posts', $this->paginate('Post'));
}

Kindly ask if it not worked for you.

Upvotes: 0

Sixthpoint
Sixthpoint

Reputation: 1193

You should look at your queries, then use cakePHP custom pagination method. Cakephp Custom Query Pagination. You can override the default paginate method. Hope this helps

Upvotes: 1

Related Questions