CakePHP 3 Paginator Custom Query (Stored Procedure) removing columns from results

Question

I am trying to paginate the results of a MySQL stored procedure in CakePHP 3. Currently, my stored procedure is working as expected, I can successfully call it in CakePHP, and the paginator is almost working as it should. It is removing a column (customer_name), and is displaying the user_id instead of the username for the given user_id. I am using the CacheSP plugin to call the SP. https://github.com/genellern/Cakephp3-CacheSP

Here is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `ordersIndex`()
BEGIN
SELECT o.id, o.order_number, DATE_FORMAT(o.created, '%m/%d/%y %h:%s %p') AS 
created, d.customer_name, o.order_amount, u.username, o.status FROM orders AS o 
INNER JOIN order_details AS d
ON o.order_number=d.order_number
INNER JOIN users as u
ON o.created_by=u.id;
END

Here is the code to call the stored procedure:

$orderQuery =  $this->Orders->callSP('ordersIndex');
$orderRecords = $orderQuery->fetchAll('assoc');
debug($orderRecords);
$orderQuery->closeCursor();

Which produces this structure:

[
  (int) 0 => [
    'id' => '43',
    'order_number' => '51',
    'created' => '02/17/16 06:10 PM',
    'customer_name' => 'cust test',
    'order_amount' => '72015.00',
    'username' => 'BSounder',
    'status' => 'pending'
  ],
  (int) 1 => [
    'id' => '44',
    'order_number' => '84',
    'created' => '02/18/16 05:06 PM',
    'customer_name' => 'cust test',
    'order_amount' => '500.00',
    'username' => 'BSounder',
    'status' => 'pending'
  ],
....
]

To get the paginator working w/ the custom query, I followed/tweaked the two functions from this link: http://technet.weblineindia.com/web/pagination-with-custom-queries-in-cakephp/ Which resulted in these functions:

public function paginate($conditions, $fields, $order, $limit, $page =1, 
        $recursive = null, $extra = array())
{
    $recursive = -1;
    $this->useTable = false;
    $results = $this->Orders->callSP('ordersIndex');
    debug($results);
    return $results;
}

public function paginateCount($conditions = null, $recursive = 0, $extra = array())
{
    $this->recursive = $recursive;
    $results = $this->Orders->callSP('ordersIndex');
    return count($results);
}

When viewing Orders/Index, everything works except the column for Customer Name is empty and the created by column displays the user_id instead of the user's username. Any help would be much much appreciated! :)

BSounder BSounder · Accepted Answer · 2016-03-02T16:06:45

I abandoned using Cake's default pagination. Instead, I used DataTables and so far I'm very happy with it. I'm able to populate a table based on the results of my stored procedure and then Datatables handles the pagination. Even includes searching and changing the number of results per page.

https://datatables.net/

CakePHP 3 Paginator Custom Query (Stored Procedure) removing columns from results

1 Answers