CakePHP3: How to change an association strategy on-the-fly?

Question

I would like to change an association strategy (hasMany) on the fly to "in" (default) to "select". Because this will correct the result for this situation:

"Get all publishers and only the first five books":

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

$publishersTable->getAssociation('Books')->setStrategy('select');       
        $query = $publishersTable->find()
                ->contain(['Books'=> function(Query $q){

                    return $q->limit(5);

                }]);

Unfortunately, Cake still using "in" to run the query and not "separated queries" and the result is only 5 publishers (and not all publishers with the first 5 books).

Is it possible to change the strategy on-the-fly? Thanks in advance !

score 1 · Answer 1 · answered Apr 13 '19 at 14:36

A hasMany association will always use a single separate query, never multiple separate queries. The difference between the select and subquery strategies is that one will directly compare against an array of primary keys, and the other against a joined subquery that will match the selected parent records.

What you are trying is to select the greatest-n-per-group, that's not possible with the built in association loaders, and it can be a little tricky depending on the DBMS that you are using, check for example How to limit contained associations per record/group? for an example for MySQL < 8.x using a custom association and loader.

For DBMS that do support it, look into window functions. Here's an example of a loader that uses native window functions, it should be possible to simply replace the one in the linked example with it, but keep in mind that it's not really tested or anything, I just had it laying around from some experiments:

namespace App\ORM\Association\Loader;

use Cake\Database\Expression\OrderByExpression;
use Cake\ORM\Association\Loader\SelectLoader;

class GroupLimitedSelectLoader extends SelectLoader
{
    /**
     * The group limit.
     *
     * @var int
     */
    protected $limit;

    /**
     * The target table.
     *
     * @var \Cake\ORM\Table
     */
    protected $target;

    /**
     * {@inheritdoc}
     */
    public function __construct(array $options)
    {
        parent::__construct($options);

        $this->limit = $options['limit'];
        $this->target = $options['target'];
    }

    /**
     * {@inheritdoc}
     */
    protected function _defaultOptions()
    {
        return parent::_defaultOptions() + [
            'limit' => $this->limit,
        ];
    }

    /**
     * {@inheritdoc}
     */
    protected function _buildQuery($options)
    {
        $key = $this->_linkField($options);
        $keys = (array)$key;
        $filter = $options['keys'];
        $finder = $this->finder;

        if (!isset($options['fields'])) {
            $options['fields'] = [];
        }

        /* @var \Cake\ORM\Query $query */
        $query = $finder();
        if (isset($options['finder'])) {
            list($finderName, $opts) = $this->_extractFinder($options['finder']);
            $query = $query->find($finderName, $opts);
        }

        $rowNumberParts = ['ROW_NUMBER() OVER (PARTITION BY'];
        for ($i = 0; $i < count($keys); $i ++) {
            $rowNumberParts[] = $query->identifier($keys[$i]);
            if ($i < count($keys) - 1) {
                $rowNumberParts[] = ',';
            }
        }
        $rowNumberParts[] = new OrderByExpression($options['sort']);
        $rowNumberParts[] = ')';

        $rowNumberField = $query
            ->newExpr()
            ->add($rowNumberParts)
            ->setConjunction('');

        $rowNumberSubQuery = $this->target
            ->query()
            ->select(['__row_number' => $rowNumberField])
            ->where($options['conditions']);

        $columns = $this->target->getSchema()->columns();
        $rowNumberSubQuery->select(array_combine($columns, $columns));

        $rowNumberSubQuery = $this->_addFilteringCondition($rowNumberSubQuery, $key, $filter);

        $fetchQuery = $query
            ->select($options['fields'])
            ->from([$this->targetAlias => $rowNumberSubQuery])
            ->where([$this->targetAlias . '.__row_number <=' => $options['limit']])
            ->eagerLoaded(true)
            ->enableHydration($options['query']->isHydrationEnabled());

        if (!empty($options['contain'])) {
            $fetchQuery->contain($options['contain']);
        }

        if (!empty($options['queryBuilder'])) {
            $fetchQuery = $options['queryBuilder']($fetchQuery);
        }

        $this->_assertFieldsPresent($fetchQuery, $keys);

        return $fetchQuery;
    }
}

score 1 · Answer 2 · answered Apr 13 '19 at 17:33

Thanks @ndm but I found another shorter solution:

$publishersTable->find()
                    ->formatResults(function ($results) use ($publishersTable) {
                       return $results->map(function ($row) use ($publishersTable) {
                            $row['books'] = $publishersTable->Books->find()
                                                ->where(['publisher_id'=>$row['id']])
                                                ->limit(5)
                                                ->toArray();
                            return $row;
                        });
            });

CakePHP3: How to change an association strategy on-the-fly?

2 Answers2