Yii2 via relations using subqueries

ActiveQueryRelationViaSubquery.php

<?php
namespace app\components\db;
use yii\db\ActiveQuery;
use yii\db\Query;

/**
 * Class ActiveQueryRelationViaSubquery
 *
 * This class extends the Yii ActiveQuery class to allow the use
 * of sub queries for hasMany relations using the via() method by calling
 * `viaSubquery()` or `viaSubqueryThreshold()`
 *
 * This is useful when using an ActiveDataProvider to show a subset of
 * a relation with a large number of members. By default Yii will build the
 * query by getting all the primary keys for the relation and then adding them as
 * a where in condition, this can be slow (or even fail in MSSQL). When viaSubquery
 * is true the where in condition contains the query instead of the keys.
 *
 * Usage example:
 * ```
 * class Group extends ActiveRecord
 * {
 *      use ActiveRecordViaSubqueryTrait
 *      ...
 *
 *      public function getGroupMembers()
 *      {
 *           return $this->hasMany(GroupMember::class, ['Group_Id' => 'Id']);
 *      }
 *
 *      public function getUsers()
 *      {
 *           return $this->hasMany(User::class, ['Id' => 'User_Id'])
 *                ->via('groupMembers')->viaSubquery();
 *      }
 * }
 * ```
 * Note: the User class will have to override the find() function to return
 * an `ActiveQueryRelationViaSubquery` instance ex:
 * ```
 * static function find(){
 *   return \Yii::createObject(ActiveQueryRelationViaSubquery::class, [get_called_class()]);
 * }
 * ```
 * or `use ActiveRecordViaSubqueryTrait`
 *
 * @package app\components\db
 */
class ActiveQueryRelationViaSubquery extends \yii\db\ActiveQuery
{
    private $_useSubquery = false;
    private $_useSubqueryThreshold = 0;

    /**
     * Use subquery to in relational where clause instead
     * of enumerating all keys
     * @param bool $value
     * @return $this
     */
    public function viaSubquery($value = true){
        $this->_useSubquery = $value;
        return $this;
    }

    /**
     * Use sub queries only when the count on
     * the query is greater than `$value`
     * @param $value
     * @return $this
     */
    public function viaSubqueryThreshold($value){
        $this->_useSubquery = true;
        $this->_useSubqueryThreshold = $value;
        return $this;
    }

    /**
     * {@inheritdoc}
     */
    public function prepare($builder)
    {
        if($this->_useSubquery
            && $this->primaryModel !== null
            && is_array($this->via))
        {
            /* @var $viaQuery ActiveQuery */
            list($viaName, $viaQuery, $viaCallableUsed) = $this->via;
            if($viaQuery->multiple
                && !$this->primaryModel->isRelationPopulated($viaName)
                && ($this->_useSubqueryThreshold == 0
                    || $viaQuery->count() > $this->_useSubqueryThreshold)
            ) {
                // save values
                $where = $this->where;
                $via = $this->via;

                // pass no op query to parent's prepare method
                $emptyQuery = new ActiveQuery(self::class);
                $emptyQuery->multiple = false;
                $emptyQuery->emulateExecution = true;
                $this->via = [$viaName, $emptyQuery, true];

                $query = parent::prepare($builder);

                // restore values
                $this->via = $via;
                $this->where = $where;
                $query->where = $where;

                // clone query so select isn't modified
                $viaQueryCopy = clone $viaQuery;

                $viaQueryCopy->select( array_values( $this->link ) );
                $columns = array_keys($this->link);

                // allow to work with dbs that do not support array columns for in condition subquery
                if(count($columns) == 1){
                    $columns = $columns[0];
                }
                $query->andWhere(['in', $columns, $viaQueryCopy]);

                if (!empty($this->on)) {
                    $query->andWhere($this->on);
                }

                return $query;
            }
        }

        return parent::prepare($builder);
    }
}

ActiveRecordViaSubqueryTrait.php

<?php
namespace app\components\db;
use yii\db\ActiveQueryInterface;

/**
 * Trait ActiveRecordViaSubqueryTrait
 * Allows ActiveRecords class to work with {@see ActiveQueryRelationViaSubquery} by
 * overriding the find() method to return an ActiveQueryRelationViaSubquery instance
 *
 * Add trait to both models in relation so that IDE auto complete will show the viaSubquery methods
 * when calling hasMany()
 * @method ActiveQueryInterface|ActiveQueryRelationViaSubquery hasMany(string $class, array $link)
 *
 * @package app\components\db
 */
trait ActiveRecordViaSubqueryTrait
{
    /**
     * {@inheritdoc}
     * @return ActiveQueryRelationViaSubquery the newly created [[ActiveQueryRelationViaSubquery]] instance.
     * @throws \yii\base\InvalidConfigException
     */
    static function find(){
        return \Yii::createObject(ActiveQueryRelationViaSubquery::class, [get_called_class()]);
    }
}