GroupLimitedBelongsToMany

example-sql-output.sql

SELECT
    ArticlesTags.article_id AS `RecentTags_CJoin__article_id`,
    ArticlesTags.tag_id AS `RecentTags_CJoin__tag_id`,
    RecentTags.id AS `RecentTags__id`,
    RecentTags.name AS `RecentTags__name`,
    RecentTags.created AS `RecentTags__created` 
FROM
    tags RecentTags 
    INNER JOIN
        articles_tags ArticlesTags 
        ON RecentTags.id = (ArticlesTags.tag_id)
    INNER JOIN (
        SELECT
            RecentTags.id AS `id`,
            ArticlesTags.article_id AS `article_id`,
            ((ROW_NUMBER() OVER (PARTITION BY ArticlesTags.article_id ORDER BY RecentTags.created DESC))) AS `__row_number` 
        FROM
            tags RecentTags 
            INNER JOIN
                articles_tags ArticlesTags 
                ON RecentTags.id = (ArticlesTags.tag_id)
        WHERE
            ArticlesTags.article_id in (1, 2, 3)
    )
    __ranked ON (
        __ranked.id = (RecentTags.id)
        AND
        __ranked.article_id = (ArticlesTags.article_id)
    )
WHERE (
    ArticlesTags.article_id in (1, 2, 3)
    AND
    __ranked.__row_number <= 3
)
ORDER BY
    RecentTags.created DESC

GroupLimitedBelongsToMany.php

<?php
namespace App\ORM\Association;

use App\ORM\Association\Loader\GroupLimitedSelectWithPivotLoader;
use Cake\ORM\Association\BelongsToMany;

class GroupLimitedBelongsToMany extends BelongsToMany
{
    protected $limit;

    public function getLimit()
    {
        return $this->limit;
    }

    public function setLimit($limit)
    {
        if (!is_int($limit)) {
            throw new \InvalidArgumentException(
                sprintf('The limit must be of type `integer`, `%s` given.', gettype($limit))
            );
        }

        if ($limit <= 0) {
            throw new \InvalidArgumentException(
                sprintf('The limit must be greater than or equal `1`, `%s` given.', $limit)
            );
        }

        $this->limit = $limit;

        return $this;
    }

    protected function _options(array $opts)
    {
        parent::_options($opts);

        if (isset($opts['limit'])) {
            $this->setLimit($opts['limit']);
        }
    }

    public function eagerLoader(array $options)
    {
        $limit = $this->getLimit();
        if (empty($limit)) {
            throw new \RuntimeException(
                sprintf('The `limit` option must be an integer greater than or equal `1`, `%s` given.', $limit)
            );
        }

        $name = $this->_junctionAssociationName();
        $loader = new GroupLimitedSelectWithPivotLoader([
            'alias' => $this->getAlias(),
            'sourceAlias' => $this->getSource()->getAlias(),
            'targetAlias' => $this->getTarget()->getAlias(),
            'foreignKey' => $this->getForeignKey(),
            'bindingKey' => $this->getBindingKey(),
            'strategy' => $this->getStrategy(),
            'associationType' => $this->type(),
            'sort' => $this->getSort(),
            'junctionAssociationName' => $name,
            'junctionProperty' => $this->_junctionProperty,
            'junctionAssoc' => $this->getTarget()->getAssociation($name),
            'junctionConditions' => $this->junctionConditions(),
            'finder' => function () {
                return $this->_appendJunctionJoin($this->find(), []);
            },
            'limit' => $limit,
        ]);

        return $loader->buildEagerLoader($options);
    }
}

GroupLimitedSelectWithPivotLoader.php

<?php
namespace App\ORM\Association\Loader;

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

class GroupLimitedSelectWithPivotLoader extends SelectWithPivotLoader
{
    protected $limit;

    public function __construct(array $options)
    {
        parent::__construct($options);

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

    protected function _buildQuery($options)
    {
        $query = parent::_buildQuery($options);

        $key = $this->_linkField($options);
        $keys = (array)$key;
        $filter = $options['keys'];

        /* @var \Cake\ORM\Query $rankedSubQuery */
        $rankedSubQuery = ($this->finder)();
        if (isset($options['finder'])) {
            list($finderName, $opts) = $this->_extractFinder($options['finder']);
            $rankedSubQuery = $rankedSubQuery->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[] = ',';
            }
        }
        if (!empty($options['sort'])) {
            $rowNumberParts[] = new OrderByExpression($options['sort']);
        }
        $rowNumberParts[] = ')';

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

        $rankedSubQuery
            ->select([
                $this->bindingKey => "{$this->alias}.{$this->bindingKey}",
                $this->foreignKey => "{$this->junctionAssociationName}.{$this->foreignKey}",
                '__row_number' => $rowNumberField,
            ])
            ->innerJoinWith($this->junctionAssociationName);
        $rankedSubQuery = $this->_addFilteringCondition($rankedSubQuery, $key, $filter);

        $query
            ->innerJoin([
                '__ranked' => $rankedSubQuery
            ], [
                "__ranked.{$this->bindingKey}" => $query->identifier("{$this->alias}.{$this->bindingKey}"),
                "__ranked.{$this->foreignKey}" => $query->identifier("{$this->junctionAssociationName}.{$this->foreignKey}"),
            ])
            ->where([
                '__ranked.__row_number <=' => $this->limit
            ]);

        return $query;
    }
}

window-based-group-limited-belongs-to-many-associations.php

<?php
// table configuration
public function initialize(array $config)
{
    /** @var GroupLimitedBelongsToMany $association */
    $association = $this->_associations->load(
        \App\ORM\Association\GroupLimitedBelongsToMany::class,
        'RecentTags',
        ['sourceTable' => $this]
    );

    $association
        ->setClassName('Tags')
        ->setForeignKey('article_id')
        ->setTargetForeignKey('tag_id')
        ->setThrough('ArticlesTags')
        
        // three tags per article
        ->setLimit(3)
        
        // ordered by creation date
        ->setSort(['RecentTags.created' => 'DESC']);
}

//------------------------------------------------------------------

// contain the association as usual... 
$Articles = \Cake\ORM\TableRegistry::get('Articles');
$query = $Articles
    ->find()
    ->contain(['RecentTags']);