CODEWITHSUNDEEP
cakephppagination
Tsukimoto Mitsumasa
Tsukimoto Mitsumasa

Reputation: 582

CakePHP pagination not working for a customized query

I have a query in CakePHP which somehow joins other tables to get the result I wanted. 

$options = array();
    $options['fields'] = array('Order.*', 'OrderCustomer.*', 'Payment.*','OrderCoupon.*', 'OrderSalesrep.*', 'OrderPackage.*');
    $options['order'] = "Order.added DESC";

    $options['joins'] = array(
                            array(
                                'table'=>'ordercustomers',
                                'alias'=>'OrderCustomer',
                                'type'=>'inner',
                                'conditions'=>array('OrderCustomer.order_id=Order.id')
                            ),
                            array(
                                'table'=>'payments',
                                'alias'=>'Payment',
                                'type'=>'inner',
                                'conditions'=>array('Payment.id=Order.payment_id')
                            ),
                            array(
                                'table'=>'ordercoupons',
                                'alias'=>'OrderCoupon',
                                'type'=>'left',
                                'conditions'=>array('OrderCoupon.order_id=Order.id')
                            ),
                            array(
                                'table'=>'ordersalesreps',
                                'alias'=>'OrderSalesrep',
                                'type'=>'left',
                                'conditions'=>array('OrderSalesrep.order_id=Order.id')
                            ),
                            array(
                                'table'=>'orderpackages',
                                'alias'=>'OrderPackage',
                                'type'=>'left',
                                'conditions'=>array('OrderPackage.order_id=Order.id')
                            )
    );

$orders = $this->Order->find('all', $options);
    $pageSettings = array();
    $pageSettings['limit'] = 20;
    $pageSettings['order'] = array('Order.added'=>'desc');
    $this->Paginator->settings = $pageSettings;
    $this->set('orders', $this->Paginator->paginate());

And then I want to paginate it on my view. Somehow it doesn't work. However I did try having only these line $orders = $this->Order->find('all', $options);$this->set('orders', $this->Paginator->paginate()); and it works. Is the pagination only working for direct queries to the model or will it also work to queries like my query above which joins other tables. Thanks.

Upvotes: 0

Views: 672

Answers (1)

Nunser
Nunser

Reputation: 4522

(Don't know your cakephp version, assuming 2.x)

You are confused. You do not find something and then paginate it. The paginator component does a whole query on it's own. So what you are doing is basically

  1. here, cakephp, find me this complex query
  2. got it, thanks a lot, now paginate me this completely unrelated query
  3. hey, wait, that didn't work

So, don't use simple finds for queries intended to be paginated, and likewise, don't use Paginator for simple find queries intended for something else (not saying you are doing it now, just a friendly reminder).

For the problem at hand cakephp docs has examples. This one for example

$this->Paginator->settings = array(
    'conditions' => array('Recipe.title LIKE' => 'a%'),
    'limit' => 10
);
$data = $this->Paginator->paginate('Recipe');
$this->set(compact('data'));

So, changing it to your problem would be something like

//the same options array
//$pageSettings = array();
$options['limit'] = 20; //this is by default, I think, so you can omit it
$options['order'] = array('Order.added'=>'desc');
$this->Paginator->settings = $options;
$this->set('orders', $this->Paginator->paginate());

and you are done. If there are errors, you can handle them case by case (or ask them here if you don't find a solution online).

Here is the Paginator API if that helps you clear doubts about functions and parameters required.

Upvotes: 1

Related Questions