CakePHP 对连接表进行分页条件

Question

我的 cakephp 应用程序 (2.2) 应用程序具有以下内容:

NewsArticle HMBTM NewsCategory
NewsCategory HMBTM NewsArticle 

在我的新闻文章 Controller 函数 index() 中，我试图获取新闻类别 id 为 2 的新闻文章的分页列表。

这是我的代码(这是错误的):

$this->paginate = array(
        'conditions' => array('newsArticle.news_category_id = 2'),
        'limit' => 10,
        'order' => array(
            'newsArticle.modified' => 'asc'
        )
    );
    $this->set('newsArticles', $this->paginate());

有人可以告诉我哪里出错了吗？我猜这与连接表有关。

这是我收到的错误:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'newsArticle.news_category_id' in 'where clause'

这是它生成的 SQL:

SQL 查询:

SELECT
    `NewsArticle`.`id`,
    `NewsArticle`.`title`,
    `NewsArticle`.`brief`,
    `NewsArticle`.`body`,
    `NewsArticle`.`filename`,
    `NewsArticle`.`dir`,
    `NewsArticle`.`mimetype`,
    `NewsArticle`.`filesize`,
    `NewsArticle`.`live`,
    `NewsArticle`.`user_id`,
    `NewsArticle`.`created`,
    `NewsArticle`.`modified`,
    `User`.`id`,
    `User`.`username`,
    `User`.`password`,
    `User`.`forename`,
    `User`.`surname`,
    `User`.`company`,
    `User`.`position`,
    `User`.`role`,
    `User`.`version_numbers_id`,
    `User`.`support_case_reference`,
    `User`.`support_web_password`,
    `User`.`webforms_email`,
    `User`.`tech_email`,
    `User`.`group_id`,
    `User`.`user_status_id`,
    `User`.`view_uat`,
    `User`.`manuals`,
    `User`.`filename`,
    `User`.`dir`,
    `User`.`mimetype`,
    `User`.`filesize`,
    `User`.`created`,
    `User`.`modified`,
    `User`.`live`,
    `User`.`tokenhash`
FROM `cakeclientarea`.`news_articles` AS `NewsArticle`
LEFT JOIN `cakeclientarea`.`users` AS `User`
    ON (`NewsArticle`.`user_id` = `User`.`id`)
WHERE
    `newsArticle`.`news_category_id` = 2
ORDER BY
    `newsArticle`.`modified` asc
LIMIT 10 

我可以从中看到它甚至没有触及连接表 news_articles_news_categories。

任何人都可以帮忙吗，这肯定很简单吗？我缺少什么？提前致谢。

Answer 1

适用于 CakePHP 2.3.4(2013 年 6 月)

问题出在Controller/Component/PaginatorComponent.php

函数 validateSort() 返回错误的顺序行，因为它设置为仅返回同一模型中属性的顺序。正如 validateSort 文档所述

Only fields or virtualFields can be sorted on.

要解决此问题:

在 Controller 代码中(以用户 Controller 为例):

public $components = array(
    'Paginator' => array(
        'className' => 'JoinedPaginator'),
    );

在函数中:

$this->paginate = array(
    'recursive'=>-1, // should be used with joins
    'joins'=>array(
        array(
            'table'=>'groups',
            'type'=>'inner',
            'alias'=>'Group',
            'conditions'=>array('User.group_id = Group.id')
        )
    )
)

$fields = array('User.name', 'User.id', 'Group.name');

$users = $this->paginate('User', array(), $fields);

分页函数中的第三个参数是一个白名单，通常只有当前对象中的项目，但我们添加了所有白名单对象。

重写Paginator，制作Component/JoinedPaginator.php

<?php
App::uses('PaginatorComponent', 'Controller/Component');
class JoinedPaginatorComponent extends PaginatorComponent {

    public $Controller;

    function startup(Controller $controller) {
        $this->Controller = $controller;
    }

    public function validateSort(Model $object, array $options, array $whitelist = array()) {
        if (isset($options['sort'])) {
            $direction = null;
            if (isset($options['direction'])) {
                $direction = strtolower($options['direction']);
            }
            if (!in_array($direction, array('asc', 'desc'))) {
                $direction = 'asc';
            }
            $options['order'] = array($options['sort'] => $direction);
        }

        if (!empty($whitelist) && isset($options['order']) && is_array($options['order'])) {
            $field = key($options['order']);
            if (!in_array($field, $whitelist)) {
                $options['order'] = null;
                return $options;
            }
        }

        if (!empty($options['order']) && is_array($options['order'])) {
            $order = array();
            foreach ($options['order'] as $key => $value) {
                $field = $key;
                $alias = $object->alias;
                if (strpos($key, '.') !== false) {
                    list($alias, $field) = explode('.', $key);
                }
                // Changed the order field list, to include items in join tables
                if (isset($object->{$alias}) && $object->{$alias}->hasField($field, true)) {
                    $order[$alias . '.' . $field] = $value;
                } else if(in_array($alias.'.'.$field, $whitelist)){
                    // Adding a white list order, to include items in the white list
                    $order[$alias . '.' . $field] = $value;
                } else if ($object->hasField($field)) {
                    $order[$object->alias . '.' . $field] = $value;
                } elseif ($object->hasField($key, true)) {
                    $order[$field] = $value;
                }
            }
            $options['order'] = $order;
        }

        return $options;
    }

    public function paginate($object = null, $scope = array(), $whitelist = array()) {
        $this->settings = am ($this->Controller->paginate, $this->settings);
        return parent::paginate($object, $scope, $whitelist );
    }
}

如您所见，设置不会发送到新的分页对象，因此我也覆盖了分页函数，只是为了收集设置。

这让您可以在 JOINED 表上订购。