CakePHP 3 Paginator自定义查询（存储过程）从结果中删除列

问题描述：

我想分页CakePHP 3中的MySQL存储过程的结果。目前，我的存储过程按预期工作，我可以在CakePHP中成功调用它，和paginator几乎是应该的。它将删除一列（customer_name），并显示user_id而不是给定user_id的用户名。我正在使用CacheSP插件来调用SP。 https://github.com/genellern/Cakephp3-CacheSP CakePHP 3 Paginator自定义查询（存储过程）从结果中删除列

这里是我的存储过程：

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

下面是代码来调用存储过程：

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

将会产生这样的结构：

[ 
    (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' 
    ], 
.... 
]

要获得paginator工作瓦特/自定义查询，我跟着/调整了这个链接的两个功能： http://technet.weblineindia.com/web/pagination-with-custom-queries-in-cakephp/ 这就造成了这些功能：

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

当查看订单/指数，一切正常，除了客户名称栏为空，由列创建显示USER_ID，而不是用户的用户名。任何帮助将非常感谢！ :)

答

我放弃了使用Cake的默认分页。相反，我使用了DataTables，到目前为止我对此非常满意。我可以根据存储过程的结果填充表，然后Datatables处理分页。甚至包括搜索和更改每页结果的数量。

https://datatables.net/

CakePHP 3 Paginator自定义查询（存储过程）从结果中删除列

相关推荐