CakePHP分页条件加入表 [英] CakePHP Paginate conditions on Join Table
问题描述
我的cakephp应用程序（2.2）应用程序有以下：
NewsArticle HMBTM新闻类别
新闻类别HMBTM新闻稿$ b 在我的新闻文章控制器功能索引（）我试图获得一个分页的新闻文章列表新闻类别ID 2。
我的代码（错误）：
$ this-> paginate = array（
'conditions'=> array（'newsArticle.news_category_id = 2'），
'limit'=> 10，
'order'=> array（
'newArticle.modified'=>'asc'
）
）;
$ this-> set（'newsArticles'，$ this-> paginate（））;
有人能告诉我我会出错吗？我猜这是与连接表。
遇到我得到的错误：
错误：SQLSTATE [42S22]：未找到列：1054'where子句'中的未知列'newsArticle.news_category_id'
它生成的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```````````````````````````````tokenhash`从`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。
任何人都可以帮助，肯定这是相当简单的事情吗？我缺少什么？提前感谢。
适用于CakePHP 2.3.4（2013年6月）
麻烦的是在Controller / Component / PaginatorComponent.php
函数validateSort（）返回错误的订单行，因为它设置为只返回order在同一模型中的属性。正如文档在validateSort上说的
只能对字段或virtualField进行排序。
要解决此问题：
在控制器代码中（使用User Controller作为示例）：
public $ components = array（
'Paginator'=> array（
'className'=>'JoinedPaginator'），
）;
在功能中：
$ this-> paginate = array（
'recursive'=> -1，//应与joins一起使用
'joins'=& $ b array（
'table'=>'groups'，
'type'=>'inner'，
'alias'=>'Group'，
'conditions'=> array（'User.group_id = Group.id'）
）
）
）
$ fields = array '，'User.id'，'Group.name'）;
$ users = $ this-> paginate（'User'，array（），$ fields）;
paginate函数中的第三个参数是一个白名单，通常只有当前对象中的项目，我们添加了所有白名单对象。
覆盖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）;
}
//更改订单字段列表，以包括连接表中的项
if（isset（$ object-> {$ alias}）&& $ object-> {$ alias} - > hasField（$ field，true））{
$ order [$ alias。 '。'。 $ field] = $ value;
} else if（in_array（$ alias。'。'。$ field，$ whitelist））{
//添加一个白名单，包括白名单中的项目
$ 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）;
}
}
如您所见，设置不会发送到新的p / p>
这样就可以在JOINED表上进行排序。
My cakephp app (2.2) app has the following:
NewsArticle HMBTM NewsCategory
NewsCategory HMBTM NewsArticle
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.
Heres the error I'm getting:
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'newsArticle.news_category_id' in 'where clause'
Heres the SQL its producing:
SQL Query: 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
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 (June 2013)
The trouble is in Controller/Component/PaginatorComponent.php
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.
To work around this:
In the Controller code (using User Controller as example):
public $components = array(
'Paginator' => array(
'className' => 'JoinedPaginator'),
);
In the function:
$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);
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.
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.
This let you order on JOINED tables.
这篇关于CakePHP分页条件加入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
