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 = []) {


        $dataProvider = new ActiveDataProvider([
            'query' => $this->getSearchQuery($category, $params),
            '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;
        }


        return $dataProvider;
    }

    public function getSearchQuery($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']);

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

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

        return $query;
    }

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

        $query->innerJoin('product_variant', 'product_variant.product_id = '. ProductOption::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->innerJoin(ProductCategory::tableName(), ProductCategory::tableName() .'.product_id='. ProductOption::tableName() .'.product_id');
            $query->andWhere([ProductCategory::tableName() .'.category_id' => $category->category_id]);
        }

        $query->orderBy(TaxOption::tableName() .'.sort', SORT_ASC);
        $query->limit(null);

        $queryCount = ProductOption::find()
            ->select(['COUNT('. ProductOption::tableName() .'.product_id)'])
            ->where(ProductOption::tableName() .'.option_id = '. TaxOption::tableName() .'.tax_option_id');
        $queryCount->andWhere('(SELECT COUNT(pv.product_variant_id) FROM "product_variant" "pv" WHERE pv.stock != 0 AND pv.product_id = '. ProductOption::tableName() .'.product_id) > 0');
        if (!empty($category)) {
            $queryCount->andWhere('(SELECT COUNT(pc.product_id) FROM product_category pc WHERE pc.product_id = '. ProductOption::tableName() .'.product_id AND pc.category_id = '. $category->category_id .') > 0');
        }
        if (!empty($params['options'])) {
            $queryCount->innerJoin('tax_option', 'tax_option.tax_option_id = product_option.option_id');
            $queryCount->innerJoin('tax_group', 'tax_group.tax_group_id = tax_option.tax_group_id');
            foreach ($params['options'] as $group => $options) {
                $queryCount->andWhere([
                    'tax_group.alias' => $group,
                    'tax_option.alias' => $options
                ]);
            }
        }
        if (!empty($params['brands'])) {
            $queryCount->innerJoin(Product::tableName(), 'product.product_id='. ProductOption::tableName() .'.product_id');
            $queryCount->andWhere(['product.brand_id' => $params['brands']]);
        }
        if (!empty($params['prices'])) {
            if ($params['prices']['min'] > 0) {
                $queryCount->andWhere(['>=', 'pv.price', $params['prices']['min']]);
            }
            if ($params['prices']['max'] > 0) {
                $queryCount->andWhere(['<=', 'pv.price', $params['prices']['max']]);
            }
        }
        $query->addSelect(['_items_count' => $queryCount]);

        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'),
        ];
    }
}