CakePHP3：如何即时更改关联策略？

Question

我想将关联策略（hasMany）即时更改为“输入”（默认）为“选择”。因为这样可以纠正这种情况下的结果：

“获取所有出版商，仅获取前五本书”：

$publishersTable = TableRegistry::getTableLocator()->get('Publishers');

$publishersTable->getAssociation('Books')->setStrategy('select');       

        $query = $publishersTable->find()

                ->contain(['Books'=> function(Query $q){

                    return $q->limit(5);

                }]);

不幸的是，Cake仍然使用“输入”来运行查询，而不是“单独的查询”，结果只有5个出版商（而不是所有拥有前5本书的出版商）。

是否可以即时更改策略？提前致谢 ！

Answer 1

一个hasMany协会将始终使用一个单一的单独的查询，从不多个单独的查询。select和subquery策略之间的区别在于，一个策略将直接与主键数组进行比较，另一个将与将与所选父记录匹配的联接子查询进行比较。您要尝试的是选择 每组最多，这对于内置的关联加载程序来说是不可能的，并且这可能会有些棘手，具体取决于您所使用的DBMS，例如检查如何限制每个记录/组包含的关联？有关使用自定义关联和加载程序的MySQL <8.x的示例。对于确实支持它的DBMS，请查看窗口函数。这是一个使用本机窗口函数的加载器示例，应该可以用它替换链接示例中的那个，但是请记住，它并不是经过真正测试的东西，我还是从一些实验中获得了它：namespace App\ORM\Association\Loader;use Cake\Database\Expression\OrderByExpression;use Cake\ORM\Association\Loader\SelectLoader;class GroupLimitedSelectLoader extends SelectLoader{&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* The group limit.&nbsp; &nbsp; &nbsp;*&nbsp; &nbsp; &nbsp;* @var int&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; protected $limit;&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* The target table.&nbsp; &nbsp; &nbsp;*&nbsp; &nbsp; &nbsp;* @var \Cake\ORM\Table&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; protected $target;&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* {@inheritdoc}&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; public function __construct(array $options)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; parent::__construct($options);&nbsp; &nbsp; &nbsp; &nbsp; $this->limit = $options['limit'];&nbsp; &nbsp; &nbsp; &nbsp; $this->target = $options['target'];&nbsp; &nbsp; }&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* {@inheritdoc}&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; protected function _defaultOptions()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; return parent::_defaultOptions() + [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'limit' => $this->limit,&nbsp; &nbsp; &nbsp; &nbsp; ];&nbsp; &nbsp; }&nbsp; &nbsp; /**&nbsp; &nbsp; &nbsp;* {@inheritdoc}&nbsp; &nbsp; &nbsp;*/&nbsp; &nbsp; protected function _buildQuery($options)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; $key = $this->_linkField($options);&nbsp; &nbsp; &nbsp; &nbsp; $keys = (array)$key;&nbsp; &nbsp; &nbsp; &nbsp; $filter = $options['keys'];&nbsp; &nbsp; &nbsp; &nbsp; $finder = $this->finder;&nbsp; &nbsp; &nbsp; &nbsp; if (!isset($options['fields'])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $options['fields'] = [];&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; /* @var \Cake\ORM\Query $query */&nbsp; &nbsp; &nbsp; &nbsp; $query = $finder();&nbsp; &nbsp; &nbsp; &nbsp; if (isset($options['finder'])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; list($finderName, $opts) = $this->_extractFinder($options['finder']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $query = $query->find($finderName, $opts);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberParts = ['ROW_NUMBER() OVER (PARTITION BY'];&nbsp; &nbsp; &nbsp; &nbsp; for ($i = 0; $i < count($keys); $i ++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $rowNumberParts[] = $query->identifier($keys[$i]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if ($i < count($keys) - 1) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $rowNumberParts[] = ',';&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberParts[] = new OrderByExpression($options['sort']);&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberParts[] = ')';&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberField = $query&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->newExpr()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->add($rowNumberParts)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->setConjunction('');&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberSubQuery = $this->target&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->query()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->select(['__row_number' => $rowNumberField])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where($options['conditions']);&nbsp; &nbsp; &nbsp; &nbsp; $columns = $this->target->getSchema()->columns();&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberSubQuery->select(array_combine($columns, $columns));&nbsp; &nbsp; &nbsp; &nbsp; $rowNumberSubQuery = $this->_addFilteringCondition($rowNumberSubQuery, $key, $filter);&nbsp; &nbsp; &nbsp; &nbsp; $fetchQuery = $query&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->select($options['fields'])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->from([$this->targetAlias => $rowNumberSubQuery])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where([$this->targetAlias . '.__row_number <=' => $options['limit']])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->eagerLoaded(true)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->enableHydration($options['query']->isHydrationEnabled());&nbsp; &nbsp; &nbsp; &nbsp; if (!empty($options['contain'])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $fetchQuery->contain($options['contain']);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; if (!empty($options['queryBuilder'])) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $fetchQuery = $options['queryBuilder']($fetchQuery);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; $this->_assertFieldsPresent($fetchQuery, $keys);&nbsp; &nbsp; &nbsp; &nbsp; return $fetchQuery;&nbsp; &nbsp; }}

Answer 2

我发现了另一个更短的解决方案：$publishersTable->find()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->formatResults(function ($results) use ($publishersTable) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;return $results->map(function ($row) use ($publishersTable) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $row['books'] = $publishersTable->Books->find()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where(['publisher_id'=>$row['id']])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->limit(5)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->toArray();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return $row;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });