How to sort & filter a Yii2 active query generated with findBySql()

MyModelSearch.php

<?php

/**
 * If you try to pass a query generated with findBySql() to an ActiveDataProvider,
 * you will find that the convenient filtering and sorting methods won't work on your query,
 * because the SQL statement is already specified.
 *
 * See: https://www.yiiframework.com/doc/api/2.0/yii-db-activerecord#findBySql()-detail
 *
 * However, if you use an ArrayDataProvider instead of an ActiveDataProvider, 
 * not only will your query be sortable, but pagination will work too.
 *
 * This is what I'm going to demonstrate here, with a workaround for filtering.
 *
 * Please note that generally, you are better off using ActiveDataProvider, but sometimes
 * findBySql() might be needed for very complex queries.
 */

namespace app\modules\example\models;

use Yii;
use yii\data\ArrayDataProvider;
use app\modules\example\models\MyModel;

class MyModelSearch extends MyModel
{
    public function rules()
    {
        return [
            [['model_id'], 'integer']
            [['model_name'], 'string']
        ]; // define your rules here like you always do
    }
  
    public function search($params)
    {
        $this->load($params);

        $filters = [];
        
        if ($this->model_id) $filters[] = ' "table_name"."model_id" = '.$this->model_id.' ';
        if ($this->model_name) $filters[] = ' "table_name"."model_name" ILIKE \'%'.$this->model_name.'%\' ';
        
        // ugly ifs can be put into a foreach if you don't need to customize each condition too much

        $filterCondition = count($filters) > 0 ? ' AND '. implode(' AND ', $filters) : ' ';
        
        $query = MyModel::findBySql(
            // your query here
            .$filterCondition.
            // group by, order by etc.
        );

        $sort = [
            'attributes' => [
                'model_id',
                'model_name'
            ],
            'defaultOrder' => [
                'model_name' => SORT_ASC
            ]
        ];

        $dataProvider = new ArrayDataProvider([
            'allModels' => $query->all(),
            'sort' => $sort,
            'pagination' => [
                'pageSize' => 30,
            ]
        ]);
        
        return $dataProvider;
    }
}

Switch condition for grouping filters:

        foreach ($params['MyModel'] as $key => $value) {
            if ($value && $value !== '') {
                switch ($key) {
                    case 'model_id':
                    case 'model_field1':
                    case 'model_field2':
                        // basic equals
                        $filters[] = ' "table_name"."'.$key.'" = '.$value.' ';
                        break;
                    case 'model_field3':
                    case 'model_field4':
                        // basic ilike
                        if (is_string($value)) $filters[] = ' "table_name"."'.$key.'" ILIKE \'%'.$value.'%\' ';
                        break;
                    case 'model_field5':
                    case 'model_field6':
                        // basic in (multiple choice)
                        if (is_array($value)) $filters[] = ' "table_name"."'.$key.'" IN ('.implode(', ', $value).') ';
                        break;
                    case 'model_field7_date':
                        // basic date equal
                        $filters[] = ' CAST("table_name"."'.$key.'" AS DATE) = \'%'.$value.'%\' ';
                        break;
                    case 'fullName':
                        // complex ilike
                        $filters[] = ' ("table_name"."model_first_name" ILIKE \'%'.$value.'%\' OR "table_name"."model_last_name" ILIKE \'%'.$value.'%\' OR "table_name"."model_org_name" ILIKE \'%'.$value.'%\') ';
                        break;
                }
            }
        }