frontend/models/ProductFrontendSearch.php

<?php

namespace frontend\models;

use common\modules\product\helpers\ProductHelper;
use common\modules\product\models\Brand;
use common\modules\product\models\BrandName;
use common\modules\product\models\CategoryName;
use common\modules\product\models\ProductCategory;
use common\modules\product\models\ProductOption;
use common\modules\product\models\ProductSearch;
use common\modules\rubrication\models\TaxGroup;
use common\modules\rubrication\models\TaxOption;
use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use yii\db\ActiveQuery;
use yii\db\ActiveRecord;
use yii\web\NotFoundHttpException;
use common\modules\product\models\Product;
use common\modules\product\models\ProductVariant;

class ProductFrontendSearch extends Product {
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['price_interval', 'brands'], 'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied for frontend
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($category = null, $params = []) {
        if (!empty($category)) {
            /** @var ActiveQuery $query */
//            $query = $category->getRelations('product_categories');
            $query = $category->getProducts();
        } else {
            $query = Product::find();
        }
        $query->select(['product.*']);
        $query->joinWith(['variant', 'image', 'brand', 'brand.brandName', 'category', 'category.categoryName']);

        $query->groupBy(['product.product_id', 'product_variant.price']);

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => [
                'pageSize' => 16,
            ],
            'sort' => [
                'attributes' => [
                    'name' => [
                        'asc' => ['name' => SORT_ASC],
                        'desc' => ['name' => SORT_DESC],
                        'default' => SORT_DESC,
                        'label' => 'имени',
                    ],
                    'price' => [
                        'asc' => [ProductVariant::tableName() .'.price' => SORT_ASC],
                        'desc' => [ProductVariant::tableName() .'.price' => SORT_DESC],
                        'default' => SORT_DESC,
                        'label' => 'по цене',
                    ],
                ],
            ]
        ]);

        if (!$this->validate()) {
            return $dataProvider;
        }

        ProductHelper::_setQueryParams($query, $params);

        $query->andWhere(['!=', ProductVariant::tableName() .'.stock', 0]);

        return $dataProvider;
    }

    public function optionsForCategory($category = null, $params = []) {
        $query = TaxOption::find()
            ->select([
                TaxOption::tableName() .'.*',
                'COUNT('. ProductOption::tableName() .'.product_id) AS _items_count'
            ])
            ->innerJoin(ProductOption::tableName(), ProductOption::tableName() .'.option_id='. TaxOption::tableName() .'.tax_option_id')
            ->innerJoin(ProductCategory::tableName(), ProductCategory::tableName() .'.product_id='. ProductOption::tableName() .'.product_id')
            ->innerJoin(TaxGroup::tableName(), TaxGroup::tableName() .'.tax_group_id='. TaxOption::tableName() .'.tax_group_id')
            ->where([TaxGroup::tableName() .'.is_filter' => true]);

        $query->leftJoin('product_variant', 'product_variant.product_id = '. ProductCategory::tableName() .'.product_id');
        $query->andWhere(['!=', 'product_variant.stock', 0]);
        $query->groupBy(TaxOption::tableName() .'.tax_option_id');
//        $query->having(['>', 'COUNT(product_variant.product_variant_id)', 0]);

        if (!empty($category)) {
            $query->andWhere([ProductCategory::tableName() .'.category_id' => $category->category_id]);
        }

        $query->orderBy(TaxOption::tableName() .'.sort', SORT_ASC);
        $query->limit(null);
        if (!empty($params['prices'])) {
            if ($params['prices']['min'] > 0 || $params['prices']['max'] > 0) {
                $query->innerJoin(ProductVariant::tableName(), ProductVariant::tableName() .'.product_id='. ProductCategory::tableName() .'.product_id');
            }
            if ($params['prices']['min'] > 0) {
                $query->andWhere(['>=', ProductVariant::tableName() .'.price', $params['prices']['min']]);
            }
            if ($params['prices']['max'] > 0) {
                $query->andWhere(['<=', ProductVariant::tableName() .'.price', $params['prices']['max']]);
            }
        }
        if (!empty($params['brands'])) {
            $query->innerJoin(Product::tableName(), Product::tableName() .'.product_id='. ProductCategory::tableName() .'.product_id');
            $query->andWhere([Product::tableName() .'.brand_id' => $params['brands']]);
        }

        return $query;
    }

    public function priceLimits($category = null, $params = []) {
        if (!empty($category)) {
            /** @var ActiveQuery $query */
//            $query = $category->getRelations('product_categories');
            $query = $category->getProducts();
        } else {
            $query = Product::find();
        }
        $query->joinWith('variant');

        ProductHelper::_setQueryParams($query, $params, false);

//        $query->select([
//            'MIN('. ProductVariant::tableName() .'.price) AS priceMIN',
//            'MAX('. ProductVariant::tableName() .'.price) AS priceMAX',
//        ]);

        return [
            'min' => $query->min(ProductVariant::tableName() .'.price'),
            'max' => $query->max(ProductVariant::tableName() .'.price'),
        ];
    }
}