连接表上的 CakePHP 分页条件 [英] CakePHP Paginate conditions on Join Table
问题描述
我的 cakephp 应用程序 (2.2) 应用程序具有以下内容:
My cakephp app (2.2) app has the following:
NewsArticle HMBTM NewsCategory
NewsCategory HMBTM NewsArticle
在我的新闻文章控制器函数 index() 中，我试图获取新闻类别 ID 为 2 的新闻文章的分页列表.
In my News article controller function index() I'm trying to get a paginated list of news articles that have the news category id 2.
这是我的代码(这是错误的):
Heres my code (which is wrong):
$this->paginate = array(
'conditions' => array('newsArticle.news_category_id = 2'),
'limit' => 10,
'order' => array(
'newsArticle.modified' => 'asc'
)
);
$this->set('newsArticles', $this->paginate());
有人能告诉我哪里出错了吗?我猜这与连接表有关.
Could someone tell me where I'm going wrong? I'm guessing this is to do with the join table.
这是我得到的错误:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'newsArticle.news_category_id' in 'where clause'
这是它产生的 SQL:
Heres the SQL its producing:
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.
I can see from this it's not even touching the join table news_articles_news_categories.
任何人都可以帮忙，这肯定很简单吗?我错过了什么?提前致谢.
Can anyone help, surely this is fairly simple to do? What am I missing? Thanks in advance.
推荐答案
Works for CakePHP 2.3.4(2013 年 6 月)
Works for CakePHP 2.3.4 (June 2013)
问题出在Controller/Component/PaginatorComponent.php
The trouble is in Controller/Component/PaginatorComponent.php
validateSort() 函数返回错误的订单行，因为它被设置为仅返回同一模型中属性的顺序.正如文档在 validateSort 上所说
The function validateSort(), return the wrong order line, as it is set to only return order on attributes in the same Model. As the documentation says on validateSort
只能对字段或虚拟字段进行排序.
Only fields or virtualFields can be sorted on.
要解决此问题:
在控制器代码中(以用户控制器为例):
In the Controller code (using User Controller as example):
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);
paginate 函数中的第三个参数是一个白名单，通常只有当前对象中的项目，但我们添加了所有白名单对象.
The third parameter in the paginate function is a white list, normally only items in the current object, but we added all white listed objects.
覆盖分页器，制作 Component/JoinedPaginator.php
Overriding Paginator, making the 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 );
}
}
如您所见，设置并未发送到新的分页对象，因此我也覆盖了分页功能，只是为了收集设置.
As you see the settings is not sent to the new paginate object, so I override the paginate function as well, just to collect the settings.
这让您可以在 JOINED 表上订购.
This let you order on JOINED tables.
这篇关于连接表上的 CakePHP 分页条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
