排序通过不工作与自定义模型查找分页 [英] Order by doesn't work with custom model find while paginating

问题描述

我在User模型中有自定义查找类型方法：

I've custom find type method in User model:

protected function _findStats($state, $query, $results = array()) {
    if ($state === 'before') {
        debug($query);
        $query['fields'] = array('User.id', 'User.username', 'Click.clicks', 'Click.unique', 'Sale.sales_1', 'Sale.sales_2');
        $query['joins'] = array(
            array(
                'table' => '(' . $this->__buildSubQueryClicks() . ')',
                'alias' => 'Click',
                'type' => 'LEFT',
                'conditions' => array('User.id = Click.id')
            ),
            array(
                'table' => '(' . $this->__buildSubQuerySales() . ')',
                'alias' => 'Sale',
                'type' => 'LEFT',
                'conditions' => array('User.id = Sale.id')
            )
        );
        return $query;
    }
    return $results;
}

现在从其他控制器（名为 ReportsController ）我想分页这个发现的结果。我这样做：

Now from other Controller (named ReportsController) I would like to paginate results of this find. I'm doing it like this:

public function admin_index() {     
    list($from, $to) = $this->_prepareCommonVariables();
    $this->paginate = array('stats');
    $this->User->recursive = 0;
    $this->set('users', $this->paginate('User',
        array(
            'Click.created BETWEEN ? AND ?' => array($from, $to),
            'Sale.sold BETWEEN ? AND ?' => array($from, $to)
        ),
        array('User.username', 'Click.unique', 'Click.clicks', 'Sale.sales_1', 'Sale.sales_2')
    ));
}

问题是我只能通过 .id 和 User.username 字段（通常为用户字段）。即使我通过 Controller :: paginate（）的第三个参数指定了允许的字段，它也不起作用。 Paginator Helper 链接会生成正确的网址（例如 /admin/reports/index/sort:Click.clicks/direction:asc ）但我没有看到ORDER BY部分在SQL查询。我在 _findStats（）中插入 debug（$ query），当字段例如 Click.clicks $ query ['order'] 为空。

The problem is that I can only order by User.id and User.username fields (generally User-fields). Even if I specified the allowed fields by third parameter of Controller::paginate() it doesn't work. The Paginator Helper links generate proper URLs (eg. /admin/reports/index/sort:Click.clicks/direction:asc) but I don't see ORDER BY part in SQL query. I've put debug($query) in _findStats() and when the field is for example Click.clicks the $query['order'] is empty.

推荐答案

好的，我发现$ query也recive'sort'和'direction'所以，这里的解决方案：

Ok, I've found that $query also recive 'sort' and 'direction' keys. So, here goes the solution:

protected function _findStats($state, $query, $results = array()) {
    if ($state === 'before') {
        $query['fields'] = array('User.id', 'User.username', 'Click.clicks', 'Click.unique', 'Sale.sales_1', 'Sale.sales_2');
        $query['joins'] = array(
            array(
                'table' => '(' . $this->__buildSubQueryClicks() . ')',
                'alias' => 'Click',
                'type' => 'LEFT',
                'conditions' => array('User.id = Click.id')
            ),
            array(
                'table' => '(' . $this->__buildSubQuerySales() . ')',
                'alias' => 'Sale',
                'type' => 'LEFT',
                'conditions' => array('User.id = Sale.id')
            )
        );
        if (isset($query['sort'], $query['direction'])) {
            $query['order'] = array($query['sort'] => $query['direction']);
        }
        return $query;
    }
    return $results;
}

当然这不是安全的解决方案，因为它省略了 PaginatorComponent :: validateSort（）。

Of course this is not safe solution, because it omits PaginatorComponent::validateSort().

这篇关于排序通过不工作与自定义模型查找分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！