How to use aggregated SUM function in cakephp?

Question

I have this request, I want to order with the amount amount. I have to use this result in paginate, please help me ..

SELECT (SUM( OrderItem.quantity )) AS qty, `OrderItem`.`item_name` , `OrderItem`.`size` FROM `order_items` AS `OrderItem` WHERE 1 =1 GROUP BY `OrderItem`.`item_name` , `OrderItem`.`size` ORDER BY (SUM( OrderItem.quantity )) DESC 

I tried the code below, but it does not work

 $this->paginate = array('fields'=>array('(SUM(OrderItem.quantity)) as qty', 'OrderItem.item_name', 'OrderItem.size'), 'group'=>array('OrderItem.item_name','OrderItem.size'), 'order' => array('(SUM(OrderItem.quantity))'=>'DESC')); 

Answer 1

you can use the "Custom Query Pagination" below at the link:

http://book.cakephp.org/2.0/en/core-libraries/components/pagination.html#custom-query-pagination

Use this in your model:

  public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) { $page--; $offset=$limit*$page; $result= $this->find('all',array('fields'=>array('(SUM(OrderItem.quantity)) as qty', 'OrderItem.item_name', 'OrderItem.size'),'group'=>array('OrderItem.item_name','OrderItem.size'),'order' => array('qty'=>'DESC'),'conditions'=>$conditions,'limit'=>$limit, 'offset'=>$offset)); return $result; }