可以在查询中直接在关联上使用indexby吗? [英] Can indexby be used on associations directly in the query?

问题描述

在很多情况下，我需要按ID交叉引用各种记录，并且发现用该ID索引数组时最容易做到这一点.例如，部门有很多团队，部门有很多游戏，而游戏属于HomeTeam和AwayTeam.当我想阅读一个部门的所有球队和比赛时，我会做类似的事情:

  $ division = $ this-> Divisions-> get($ id，['contain'=>[团队"，游戏"]]); 

我不这样做

  $ division = $ this-> Divisions-> get($ id，['contain'=>['Teams，'Games'=>['HomeTeam'，'AwayTeam']]]); 

因为这似乎会增加内存需求，尤其是当我在团队中进一步包含其他模型(人员等)时.所以，我代替了

  $ division-> teams = collection($ division-> teams)-> indexBy('id')-> toArray(); 

在重新索引该数组之后，然后当我遍历 $ division-> games 时，要获取主队记录，我使用 $ division-> teams[$ game-> home_team_id] .一切都很好(除了将团队的财产设置为肮脏，给您带来的不便之处).

但是似乎ORM的queryBuilder功能非常神奇，我知道我可以做到

  $ teams = $ this-> Divisions-> Teams-> find()->其中([['division_id'=> $ id])-> indexBy('id')-> toArray(); 

让一组团队对我的需求进行索引，所以我想知道是否存在某种将indexBy包含在关联中的方法.我尝试过

  $ division = $ this-> Divisions-> get($ id，['contain'=>['团队'=>['queryBuilder'=>函数(查询$ q){返回$ q-> indexBy('id');}，]，游戏"，]]); 

但是，毫不奇怪，这没有用.有什么想法吗?

解决方案

仅作记录，您应该已经知道了， indexBy()不属于查询，而是结果设置，因此要调用它，需要先执行查询.不能将其用于关联查询构建器，因为它必须返回查询而不是结果集.

虽然可以使用关联的结果格式化程序 并相应地修改结果集，问题是结果集将保留 all 的 all 团队结果>部门，并且当团队实体分布在它们所属的各个部门实体上时，将分别对数组进行重新索引"，将不考虑结果集的索引来填充数组.简短地说，那是行不通的.

全局结果格式化程序

但是，主查询的结果格式化程序应该可以正常工作，并且您可能已经想到，事后可以简单地重置脏状态，以防它引起任何问题，例如

  $ division = $ this->部门-> find()->包含(['团队'])->其中(['Divisions.id'=>$ id])-> formatResults(function($ results){/* @var $ results \ Cake \ Datasource \ ResultSetInterface | \ Cake \ Collection \ CollectionInterface */返回$ results-> map(function($ row){如果(isset($ row ['teams'])){$ row ['teams'] = collection($ row ['teams'])-> indexBy('id')-> toArray();}if($ row instanceof EntityInterface){$ row-> dirty('teams'，false);}返回$ row;});})-> firstOrFail(); 

自定义关联和特定于关联的结果格式化程序

另一种选择是使用自定义关联类，该类将覆盖 ExternalAssociationTrait :: _ buildResultMap()，以便它尊重结果集的索引，因为这是问题开始的地方./p>

默认情况下，从结果集中获取关联的实体，并将其附加到新数组中，该新数组随后将分配给结果所属的实体上的相应关联属性.因此，这就是丢失可能的自定义索引结果集中的键的地方.

这里有个例子，变化很小，但是我不确定可能会有副作用！

src/Model/Association/IndexAwareHasMany.php

 命名空间App \ Model \ Association;使用Cake \ ORM \ Association \ HasMany;IndexAwareHasMany类扩展HasMany{受保护的函数_buildResultMap($ fetchQuery，$ options){$ resultMap = [];$ key =(array)$ options ['foreignKey'];//在这里获取索引foreach($ fetchQuery-> all()as $ index => $ result){$ values = [];foreach($ key as $ k){$ values [] = $ result [$ k];}//并在这里使用它$ resultMap [implode(';'，$ values)] [$ index] = $ result;}返回$ resultMap;}} 

原文: https://github.com/cakephp/...ORM/Association/ExternalAssociationTrait.php#L109

现在，您当然必须利用新的关联，为了简化此示例，让我们重写表类的默认 hasMany()方法

 公共函数hasMany($ associated，array $ options = []){$ options + = ['sourceTable'=>$ this];$ association = new \ App \ Model \ Association \ IndexAwareHasMany($ associated，$ options);返回$ this-> _associations-> add($ association-> name()，$ association);} 

现在，最后，您可以将结果格式化程序用于关联:

  $ division = $ this-> Divisions-> get($ id，['contain'=>['团队'=>['queryBuilder'=>函数(查询$ query){返回$ query-> formatResults(function($ results){/* @var $ results \ Cake \ Datasource \ ResultSetInterface | \ Cake \ Collection \ CollectionInterface */返回$ results-> indexBy('id');});}]，游戏"，]]); 

I have a number of situations where I need to cross-reference various records by ID, and find it's easiest to do so when the array is indexed by that ID. For example, Divisions hasMany Teams, Divisions hasMany Games, and Games belongTo HomeTeam and AwayTeam. When I want to read all of the teams and games in a division, I do something like this:

$division = $this->Divisions->get($id, [
    'contain' => ['Teams', 'Games']
]);

I don't do

$division = $this->Divisions->get($id, [
    'contain' => ['Teams', 'Games' => ['HomeTeam', 'AwayTeam']]
]);

because it seems that would increase memory requirements, especially when I'm further containing other models (People, etc.) in the Teams. So, instead I do

$division->teams = collection($division->teams)->indexBy('id')->toArray();

after the get to reindex that array, and then when I'm iterating through $division->games, to get the home team record I use $division->teams[$game->home_team_id]. This is all well and good (except that it sets the teams property as being dirty, a minor inconvenience).

But it seems that the queryBuilder functionality of the ORM is pretty magical, and I know that I can do

$teams = $this->Divisions->Teams->find()
    ->where(['division_id' => $id])
    ->indexBy('id')
    ->toArray();

to get an array of teams indexed how I want, so I'm wondering if there's some way to include indexBy on the associations. I tried

$division = $this->Divisions->get($id, [
    'contain' => [
        'Teams' => [
            'queryBuilder' => function (Query $q) {
                return $q->indexBy('id');
            },
        ],
        'Games',
    ]
]);

but, unsurprisingly, this didn't work. Any ideas?

解决方案

Just for the record, guess you know this already, indexBy() doesn't belong to the query, but to the result set, so being able to call it requires the query to be executed first. It's not possible to use this for an association query builder, as it must return a query, not a result set.

While it would be possible to use result formatters for the associations and modify the result set accordingly, the problem is that the result set will hold all team results for all divisions, and when the team entities are being distributed on the various division entities that they belong to, the arrays will be "reindexed", respectively, the arrays will be populated without respect to the indices of the result set, so long story short, that won't work.

Global result formatter

However, a result formatter for the main query should work fine, and as you probably already figured, you can simply reset the dirty state afterwards in case it causes any problems, something like

$division = $this->Divisions
    ->find()
    ->contain([
        'Teams'
    ])
    ->where([
        'Divisions.id' => $id
    ])
    ->formatResults(function($results) {
        /* @var $results \Cake\Datasource\ResultSetInterface|\Cake\Collection\CollectionInterface */
        return $results
            ->map(function ($row)  {
                if (isset($row['teams'])) {
                    $row['teams'] = collection($row['teams'])->indexBy('id')->toArray();
                }
                if ($row instanceof EntityInterface) {
                    $row->dirty('teams', false);
                }
                return $row;
            });
    })
    ->firstOrFail();

Custom association and association specific result formatters

Another option would be to use a custom association class, which overrides ExternalAssociationTrait::_buildResultMap(), so that it respects the indices of the result set, as this is where the problem starts.

By default the associated entities are fetched from the result set and appended to a new array, which is later assigned to the respective association property on the entity the results belong to. So this is where the the keys from the possible custom indexed result set are being lost.

Here's an example, the change is really small, but I'm not sure about possible side effects!

src/Model/Association/IndexAwareHasMany.php

namespace App\Model\Association;

use Cake\ORM\Association\HasMany;

class IndexAwareHasMany extends HasMany
{
    protected function _buildResultMap($fetchQuery, $options)
    {
        $resultMap = [];
        $key = (array)$options['foreignKey'];

        // grab the index here
        foreach ($fetchQuery->all() as $index => $result) {
            $values = [];
            foreach ($key as $k) {
                $values[] = $result[$k];
            }
            // and make use of it here
            $resultMap[implode(';', $values)][$index] = $result;
        }
        return $resultMap;
    }
}

Original: https://github.com/cakephp/...ORM/Association/ExternalAssociationTrait.php#L109

Now you must of course make use of the new association, to simplify it for this example, let's just override the table class' default hasMany() method

public function hasMany($associated, array $options = [])
{
    $options += ['sourceTable' => $this];
    $association = new \App\Model\Association\IndexAwareHasMany($associated, $options);
    return $this->_associations->add($association->name(), $association);
}

And now, finally, you could use a result formatter for the association:

$division = $this->Divisions->get($id, [
    'contain' => [
        'Teams' => [
            'queryBuilder' => function (Query $query) {
                return $query
                    ->formatResults(function($results) {
                        /* @var $results \Cake\Datasource\ResultSetInterface|\Cake\Collection\CollectionInterface */
                        return $results->indexBy('id');
                    });
            }
        ],
        'Games',
    ]
]);

这篇关于可以在查询中直接在关联上使用indexby吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！