CODEWITHSUNDEEP
phppaginationcakephp-2.0
Amit Chowdhury
Amit Chowdhury

Reputation: 613

Cakephp custom query with pagination?

I am developing an web application, for this I need to do custom query.I am giving my code samples below : 

function index() {
        $this->layout = 'reserved';
        $info = $this->Auth->user();
        $user_id = $info["id"];
        $club_info = $this->Club->find('first', array('conditions' => array('Club.user_id' => $user_id)));
        if ($club_info) {

            $club_id = $club_info['Club']['id'];
            $club_name = $club_info['Club']['club_name'];
            $this->set(compact('user_id', 'club_id', 'club_name'));
            $clubTables =$this->ClubTable->query("SELECT *FROM club_tables ClubTable LEFT JOIN categories Category ON ClubTable.category_id=Category.id LEFT JOIN deals Deal ON ClubTable.id=Deal.club_table_id AND ClubTable.club_id='".$club_id."' AND ClubTable.status='approved' ORDER BY Deal.status DESC");
            $this->set('clubTables', $clubTables);
        } else {
            $this->set('clubTables', false);
        }
    }

Everything is working fine but I am not able to add pagination.Any Idea, how can I add can pagination?

Upvotes: 1

Views: 4431

Answers (3)

laul
laul

Reputation: 57

In your model

public function paginate($conditions, $fields, $order, $limit, $page = 1,
    $recursive = null, $extra = array())
{    
    $recursive = -1;

    // Mandatory to have
    $this->useTable = false;
    $sql = '';

    $sql .= "Your custom query here just do not include limit portion";

    // Adding LIMIT Clause
    $sql .= (($page - 1) * $limit) . ', ' . $limit;

    $results = $this->query($sql);

    return $results;
}

And 

public function paginateCount($conditions = null, $recursive = 0, $extra = array())
{
    $sql = '';

    $sql .= "Your custom query here just do not include limit portion";

    $this->recursive = $recursive;

    $results = $this->query($sql);

    return count($results);
}

In your controler

public function index()
{
    $this->Offre->recursive = 0;

    $this->paginate = array('Offre'=>array('limit'=>10));

    $this->set('offres', $this->paginate('Offre'));
}

Upvotes: 0

usumoio
usumoio

Reputation: 3568

To do this you cannot use the cake's magic. This is because you are writing a custom query and cake's ORM understandably has no idea how to support arbitrary queries that do not necessarily fit its model structure. Instead, you need to manage the pagination yourself. In your case this means old-school query management using LIMIT and OFFSET in your query. There are plenty of guides on how to do this on the internet because we had to do this for years before frameworks made it easy. Here is a post that discusses this:

Simple PHP Pagination script

Upvotes: 0

Carlos Montoya
Carlos Montoya

Reputation: 88

You should look into using the find() method with the Contain option to do querys inside Cakephp and you can look into how to use pagination here http://book.cakephp.org/2.0/en/core-libraries/helpers/paginator.html

Upvotes: 1

Related Questions