CakePHP - 分页和排序第二级协会 [英] CakePHP - paginate and sort 2nd level association
问题描述
我明白，这已被要求100,000次，但我几乎读了所有10万的回复，没有一个似乎匹配我的后。我试过了每一种可能的组合（显然不是），恐怕我得到的东西这么相对简单。
个人资料 - > BelongsTo - > Store，Store - > 属性 - >区域，地区 - > HasMany - >商店
个人资料。 php
class Profile extends AppModel {
public $ belongsTo = array（
'Store'=> array（
'className'=>'Store'，
'foreignKey'=>'store_id'....
商店 .php
class Store extends AppModel {
public $ belongsTo = array（
'Region'=> array（
'className'=>'Region'，
'foreignKey'=>'region_id' ....
地区 .php
class Region extends AppModel {
public $ hasMany = array（
'Store'=> array（
'className '=> 'Store'，
'foreignKey'=> 'region_id'....
ProfileController .php
$ this-> Paginator-> settings = array（
'conditions'=> array（'Profile.job_title_id' =>'1'），
'include'=> array（
'Store'=> array（
'Region'
）
）
）;
$ UserArds = $ this-> Paginator-> paginate（'Profile'）;
$ this-> set（compact（'UserArds'））;
查看 .php
< th><？php echo $ this-> Paginator-> sort（'Region.name'，'Region'）; ？>< / th>
我所要做的就是使用paginator时可以按Region名称排序。不管我使用什么组合，我似乎不能排序Region.name。 order By 子句与所有其他2级深度关联一起省略，但在任何其他时间（具有相同级别或第一级）都可以正常工作。
看看SQL调试输出，正在使用单独的查询检索区域，这就是Cakes ORM当前的工作原理。
在您的情况下，有一个相对简单的解决方法。只需创建正确的例如个人档案所属区域。
这是一个（未经测试的）它添加 belongsTo 与 foreignKey 选项设置为 false 为了禁用ORM自动生成外键，这是必要的，因为否则会查找 Profile.region_id 。请注意，因此包含 config也必须更改！
$ this-> Profile-> bindModel（array（
'belongsTo'=&array; array（
'Region'=> array（
'foreignKey'=> false，
'conditions'=> array（'Region.id = Store.region_id'）
），
）
））;
$ this-> Paginator-> settings = array（
'conditions'=> array（'Profile.job_title_id'=>'1'），
'contains'=> array（'Store'，'Region'）
）;
这应该生成一个包含商店以及 regions 表，可以在 Region.name 上进行排序。注意，结果数组会有一点不同， Region 不会嵌套在 Store 相同的级别，即：
数组
（
[Profile] =& b（
....
）
[Store] => Array
（
....
$ b b）
[Region] => Array
（
....
）
）
您可以相应地修改视图代码，也可以在将检索数据传递给视图之前进行转换。第三个选项是在包含配置中包含嵌套 Region ，但是这将导致额外的查询完全不必要，因为数据已经用主查询提取，所以我不会推荐。
I understand that this has been asked 100,000 times but I've pretty much read all 100,000 replies and none of them seem to match what I'm after. I've tried every possible combination (obviously not) and I'm afraid I'm getting beat at something so relatively simple. This is my 2nd Cake project, so I'm by no means an expert.
Profile -> BelongsTo -> Store, Store -> BelongsTo -> Region, Region -> HasMany -> Stores
Profile.php
class Profile extends AppModel {
public $belongsTo = array(
'Store' => array(
'className' => 'Store',
'foreignKey' => 'store_id'....
Store.php
class Store extends AppModel {
public $belongsTo = array(
'Region' => array(
'className' => 'Region',
'foreignKey' => 'region_id'....
Region.php
class Region extends AppModel {
public $hasMany = array(
'Store' => array(
'className' => 'Store',
'foreignKey' => 'region_id'....
ProfileController.php
$this->Paginator->settings = array(
'conditions' => array('Profile.job_title_id' => '1'),
'contain' => array(
'Store'=> array(
'Region'
)
)
);
$UserArds = $this->Paginator->paginate('Profile');
$this->set(compact('UserArds'));
view.php
<th><?php echo $this->Paginator->sort('Region.name', 'Region'); ?></th>
All I'm looking to do, is to be able to sort by Region name when using paginator. No matter what combination I use, I just can't seem to be able to sort Region.name. The order By clause is omitted with all other 2-level-deep associations but works fine any other time (with same level or 1st level).
Can anyone suggest a fix for this simple mistake?
Look at the SQL debug output, the regions are being retrieved using separate queries, that's how Cakes ORM currently works.
In your case there's a relatively simple workaround. Just create a proper association on the fly, for example Profile belongsTo Region.
Here's an (untested) example, it adds a belongsTo association with the foreignKey option set to false in order to disable the automatic foreign key generation by the ORM, this is necessary as it would otherwise look for something like Profile.region_id. Note that consequently the contain config has to be changed too!
$this->Profile->bindModel(array(
'belongsTo' => array(
'Region' => array(
'foreignKey' => false,
'conditions' => array('Region.id = Store.region_id')
),
)
));
$this->Paginator->settings = array(
'conditions' => array('Profile.job_title_id' => '1'),
'contain' => array('Store', 'Region')
);
That should generate a proper query that includes the stores as well as the regions table, making it possible to sort on Region.name. Note that the resulting array will be a little different, Region will not be nested in Store, everything will be placed in the same level, ie:
Array
(
[Profile] => Array
(
....
)
[Store] => Array
(
....
)
[Region] => Array
(
....
)
)
Either you modify your view code accordingly, or you transform the retrieved data before passing it to the view. A third option would be to include a nested Region in the contain config, however that would result in additional queries that are totally unnecessary as the data was already fetched with the main query, so I wouldn't recommend that.
这篇关于CakePHP - 分页和排序第二级协会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
