CakePHP - 分页和排序二级关联 [英] CakePHP - paginate and sort 2nd level association

问题描述

我知道这已被问到 100,000 次，但我几乎阅读了所有 100,000 条回复，但似乎没有一个与我所追求的相符.我已经尝试了所有可能的组合(显然不是)，恐怕我在一些相对简单的事情上被击败了.这是我的第二个蛋糕项目，所以我绝不是专家.

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 -> BelongsTo -> Store, Store -> BelongsTo -> Region, Region -> HasMany -> Stores

个人资料.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'),
    'contain' => array(
        'Store'=> array(
            'Region'
        )
    )
);
$UserArds = $this->Paginator->paginate('Profile');
$this->set(compact('UserArds'));

查看.php

<th><?php echo $this->Paginator->sort('Region.name', 'Region'); ?></th>

我想要做的就是在使用分页器时能够按区域名称排序.无论我使用什么组合，我似乎都无法对 Region.name 进行排序.order By 子句与所有其他 2 级深度关联被省略，但在其他任何时候都可以正常工作(具有相同级别或第一级).

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?

推荐答案

查看 SQL 调试输出，正在使用单独的查询检索区域，这就是 Cakes ORM 当前的工作方式.

Look at the SQL debug output, the regions are being retrieved using separate queries, that's how Cakes ORM currently works.

就您而言，有一个相对简单的解决方法.只需创建一个合适的动态关联，例如Profile所属区域.

In your case there's a relatively simple workaround. Just create a proper association on the fly, for example Profile belongsTo Region.

这是一个(未经测试的)示例，它添加了一个 belongsTo 关联，并将 foreignKey 选项设置为 false 以禁用自动外部由 ORM 生成密钥，这是必要的，否则它会寻找类似 Profile.region_id 的东西.请注意，因此 contain 配置也必须更改！

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')
);

这应该会生成一个正确的查询，包括 stores 以及 regions 表，从而可以对 Region.name 进行排序.注意生成的数组会有点不同，Region不会嵌套在Store中，所有的东西都会放在同一层，即:

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
        (
            ....

        )
)

要么相应地修改视图代码，要么在将检索到的数据传递给视图之前对其进行转换.第三种选择是在 contain 配置中包含一个嵌套的 Region，但是这会导致额外的查询是完全不必要的，因为主查询已经获取了数据，所以我不建议这样做.

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屋！