Cakephp custom query with pagination?

Question

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?

score 1 · Answer 1 · answered Mar 24 '14 at 20:46

1

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

answered Mar 24 '14 at 20:46

Carlos Montoya

88
4

score 0 · Answer 2 · edited May 23 '17 at 10:30

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

score 0 · Answer 3 · edited Feb 09 '15 at 15:00

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'));
}

Cakephp custom query with pagination?

3 Answers3