example-sql-output.sql

SELECT 
  `Comments`.`id` AS `Comments__id`, 
  `Comments`.`article_id` AS `Comments__article_id`, 
  `Comments`.`votes` AS `Comments__votes`, 
  `Comments`.`comment` AS `Comments__comment`, 
  `Comments`.`created` AS `Comments__created` 
FROM 
  (
    SELECT 
      (
        (
          @row_number := IF(
            @foreign_key = (`article_id`), 
            @row_number + 1, 
            1
          )
        )
      ) AS `__row_number`, 
      (
        (@foreign_key := `article_id`)
      ) AS `__foreign_key`, 
      `id` AS `id`, 
      `article_id` AS `article_id`, 
      `votes` AS `votes`, 
      `comment` AS `comment`, 
      `created` AS `created` 
    FROM 
      `comments` `Comments`, 
      (
        SELECT 
          @row_number := 0, 
          @foreign_key := 0
      ) `__init_variables` 
    WHERE 
      `Comments`.`article_id` in (1, 2) 
    ORDER BY 
      `Comments`.`article_id` ASC, 
      `Comments`.`votes` DESC
  ) `Comments` 
WHERE 
  `Comments`.`__row_number` <= 2

GroupLimitedHasMany.php

<?php
namespace App\ORM\Association;

use App\ORM\Association\Loader\GroupLimitedSelectLoader;
use Cake\ORM\Association\HasMany;

class GroupLimitedHasMany extends HasMany
{
    /**
     * @var integer
     */
    protected $limit;

    /**
     * @return integer
     */
    public function getLimit()
    {
        return $this->limit;
    }

    /**
     * @param integer $limit
     * @return $this
     */
    public function setLimit($limit)
    {
        $this->limit = $limit;
        return $this;
    }

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

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

    public function eagerLoader(array $options)
    {
        $loader = new GroupLimitedSelectLoader([
            '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(),
            'finder' => [$this, 'find'],
            'target' => $this->getTarget(),
            'limit' => $this->getLimit()
        ]);

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

GroupLimitedSelectLoader.php

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

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

class GroupLimitedSelectLoader extends SelectLoader
{
    /**
     * @var integer
     */
    protected $limit;

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

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

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

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

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

        $key = $this->_linkField($options);

        $rankedSubQuery =
            $this->target->find();

        $rowNumberField = $rankedSubQuery
            ->newExpr()
            ->add('@row_number')
            ->setConjunction(':=')
            ->add(
                $rankedSubQuery->func()->IF([
                    $rankedSubQuery->newExpr()->eq(
                        '@foreign_key',
                        new IdentifierExpression($options['foreignKey'])
                    ),
                    '@row_number + 1' => 'literal',
                    1 => 'literal'
                ])
            );

        $foreignKeyField = $rankedSubQuery
            ->newExpr()
            ->add('@foreign_key')
            ->setConjunction(':=')
            ->add(new IdentifierExpression($options['foreignKey']));

        $columns = $this->target->getSchema()->columns();
        $rankedSubQuery = $this->target
            ->find()
            ->select([
                '__row_number' => $rowNumberField,
                '__foreign_key' => $foreignKeyField
            ])
            ->select(array_combine($columns, $columns))
            ->from([
                $this->targetAlias => $this->target->getTable(),
                '__init_variables' => '(SELECT @row_number := 0, @foreign_key := 0)'
            ])
            ->where($options['conditions'])
            ->order([
                $key => 'ASC'
            ]);

        $rankedSubQuery = $this->_addFilteringCondition($rankedSubQuery, $key, $options['keys']);

        if (!empty($options['sort'])) {
            $rankedSubQuery->order($options['sort']);
        }

        $finder = $this->finder;
        $fetchQuery = $finder()
            ->select($options['fields'])
            ->from([$this->targetAlias => $rankedSubQuery])
            ->where([
                $this->targetAlias . '.__row_number <=' => $options['limit']
            ], [
                $this->targetAlias . '.__row_number' => 'integer',
            ])
            ->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, (array)$key);

        return $fetchQuery;
    }
}

mysql-sepcific-group-limited-has-many-associations.php

<?php
// This example requires at least CakePHP 3.4.0!

//------------------------------------------------------------------
// table configuration

public function initialize(array $config)
{
	// ...

	$options = [
		'sourceTable' => $this,
		// two comments per article
		'limit' => 2,
		// ordered by votes
		'sort' => [
			'Comments.votes' => 'DESC'
		]
	];
	$association = new \App\ORM\Association\GroupLimitedHasMany('Comments', $options);

	$this->associations()->add($association->getName(), $association);
}

//------------------------------------------------------------------
// contain the association as usual... 

$Articles = \Cake\ORM\TableRegistry::get('Articles');

$query = $Articles
	->find()
	->contain(['Comments']);