frontend/models/SearchPerformerForm.php

<?php
    namespace frontend\models;
    use common\models\Portfolio;
    use common\models\User;
    use Yii;
    use yii\base\Model;
    use yii\data\ActiveDataProvider;
    use yii\helpers\ArrayHelper;
    /**
     * ContactForm is the model behind the contact form.
     */
    class SearchPerformerForm extends Model
    {
        public $city;
        public $specialization;
        public $working_conditions;
        public $type;
        public $additional_parameters;
        public $rating;
        public $online;
        public $search;
        /**
         * @inheritdoc
         */
        public function rules()
        {
            return [
                [
                    [
                        'specialization',
                        'city',
                        'type',
                        'additional_parameters',
                        'working_conditions',
                        'rating',
                        'online',
                        'search',
                    ],
                    'safe',
                ],
                [
                    [ 'rating' ],
                    'default',
                    'value' => 0,
                ],
            ];
        }
        /**
         * @inheritdoc
         */
        public function attributeLabels()
        {
            return [
                'city'                  => 'Город',
                'specialization'        => 'Специализация',
                'type'                  => 'Тип исполнителя',
                'additional_parameters' => 'Дополнительно',
                'working_conditions'    => 'Условия работы',
                'rating'                => 'Рейтинг',
                'online'                => 'Статус',
                'search'                => 'Найти',
            ];
        }
        /**
         * Creates data provider instance with search query applied
         *
         * @param array $params
         *
         * @return ActiveDataProvider
         */
        public function search($params)
        {
            $this->load($params);
            $query = User::find()
                         ->select([
                             'user.*',
                             'company_info.*',
                             'user_info.*',
                         ])
                         ->distinct(true)
                         ->joinWith([
                             'userInfo',
                             'specializations',
                             'companyInfo',
                         ])
                         ->where([ 'user_info.is_freelancer' => 1 ]);
            $dataProvider = new ActiveDataProvider([
                'query' => $query,
            ]);
            $dataProvider->setSort([
                'defaultOrder' => [
                    'name' => SORT_ASC,
                ],
                'attributes'   => [
                    'name'  => [
                        'asc'     => [
                            'company_info.name' => SORT_ASC,
                            'firstname'         => SORT_ASC,
                            'lastname'          => SORT_ASC,
                        ],
                        'desc'    => [
                            'company_info.name' => SORT_DESC,
                            'firstname'         => SORT_DESC,
                            'lastname'          => SORT_DESC,
                        ],
                        'default' => SORT_ASC,
                        'label'   => 'Название',
                    ],
                    'visit' => [
                        'asc'     => [
                            'user_info.date_visit' => SORT_ASC,
                        ],
                        'desc'    => [
                            'user_info.date_visit' => SORT_DESC,
                        ],
                        'default' => SORT_DESC,
                        'label'   => 'Последний визит',
                    ],
                    'city'  => [
                        'asc'     => [
                            'user_info.city' => SORT_ASC,
                        ],
                        'desc'    => [
                            'user_info.city' => SORT_DESC,
                        ],
                        'default' => SORT_ASC,
                        'label'   => 'Город',
                    ],
                ],
            ]);
            if(!$this->validate()) {
                // uncomment the following line if you do not want to any records when validation fails
                // $query->where('0=1');
                return $dataProvider;
            }
            $query2 = User::find()
                          ->select([ 'users.id' ])
                          ->alias('users')
                          ->distinct()
                          ->innerJoin([ 'spec' => 'user_specialization' ], 'spec.user_id = users.id')
                          ->asArray()
                          ->column();
            if(empty($query2)) {
                $query->where('0=1');
                return $dataProvider;
            }
            $query->andFilterWhere([
                'user_info.city'                   => $this->city,
                'specialization.specialization_id' => $this->specialization,
                'user.type'                        => $this->type,
                'user.id'                          => $query2,
            ]);
            $query->andFilterWhere([
                '>=',
                'user_info.rating',
                $this->rating,
            ]);
            if($this->online == 1) {
                $query->andWhere([
                    '>=',
                    'user_info.date_visit',
                    date('Y-m-d H:i:s.u', time() - 1800),
                ]);
            }
            if(!empty( $this->working_conditions )) {
                foreach($this->working_conditions as $working_conditions) {
                    if($working_conditions == 'guarantee') {
                        $query->andWhere([
                            '<>',
                            'user_info.guarantee',
                            '',
                        ]);
                    } else if($working_conditions == 'prepayment') {
                        $query->andWhere([
                            'user_info.prepayment' => 0,
                        ]);
                    } else {
                        $query->andFilterWhere([
                            'user_info.' . $working_conditions => 1,
                        ]);
                    }
                }
            }
            if(!empty( $this->additional_parameters )) {
                foreach($this->additional_parameters as $additional_parameters) {
                    switch($additional_parameters) {
                        case 'with_portfolio':
                            $query->andFilterWhere([
                                'user.id' => ArrayHelper::toArray(Portfolio::find()
                                                                           ->select('user_id')
                                                                           ->column()),
                            ]);
                            break;
                        case 'with_comments':
                            $query->andFilterWhere([
                                'user.id' => ArrayHelper::toArray(Portfolio::find()
                                                                           ->select('user_id')
                                                                           ->column()),
                            ]);
                            break;
                        case 'only_free':
                            $query->andFilterWhere([
                                'user_info.busy' => 0,
                            ]);
                            break;
                    }
                }
            }
            if(!empty( $this->search )) {
                $query->andFilterWhere([
                    'or',
                    [
                        'like',
                        'LOWER("user"."firstname")',
                        mb_strtolower($this->search),
                    ],
                    [
                        'like',
                        'LOWER("user"."lastname")',
                        mb_strtolower($this->search),
                    ],
                    [
                        'like',
                        'LOWER("company_info"."name")',
                        mb_strtolower($this->search),
                    ],
                ]);
            }
            return $dataProvider;
        }
    }