Add support for dynamic filters

[dan-developer]

Congratulations for the best developed extensions. Have a suggestion, which I believe does not exist: have you thought of adding support for it > http://mistic100.github.io/jQuery-QueryBuilder/ or for it:

Thanks.

[Enrica-r]

@kzweb The excel filter dialog is good for some data rows only. But you are right, there should be a better filter helper extension.

Normally in where clauses we use equal (=), like, greater than (>=), less than (<=) and the IN statement. These could be combined with NOT and NOT NULL.

I would integrate a filter icon per column if the extension is activated. Activation could be per column. Like:

The current filter input field shows the generated statement or a part of this. If you klick on the filter icon a modal is opened which could look like this:

With radio button you can choose the filter statement, the options are:

• one value equal, not equal, like, not like. The input box could be one of the same classes as in Kartik grid filter classes.
• two inputs -> fist is >= and second is <=, if both are filled with a value the where clause is "searchField >= input 1 AND searchField <= input 2". If input 1 is null statement is "searchField <= input 2" or if input 2 is null statement is "searchField >= input 1".
• Select2 muliselect to generate an IN statement. Alternatively two combined sortable widgets could be used. But then this needs AJAX and filter input support also. A very comfortable solution would be a generic controller which builds the query with current model and current column. For more complex situation this should be done manually.

The grid filter must have a hidden field in which the filter extsion stores the complete where expression in Json format. The searchModel must get as params a JSON array which can be converted in search function to an array. This array is handed over to andFilterWhere. Example:

->andFilterWhere(Json::decode($this->column))

The internal result will be: ->andFilterWhere(['like', 'attributeName', 'value'])

What do you think @kzweb or @kartik-v ?

[dan-developer]

Thank you for your response, @Enrica-r . It's almost it, I did something temporary for testing. In the zip file is the base. Documentation is in Portuguese. I do not know if it can be harnessed, but already gives you an idea of what my thought. Follows the example of grid and result:

http://yoocloud.com.br/yoocloud.zip

namespace app\modules\financeiro\grids;

use app\modules\financeiro\models\FinanceiroLancamento;

class LancamentoGrid extends \yoocloud\grid\BaseGrid
{

    /**
     * @inheritdoc
     */
    public $modelClass = 'app\modules\financeiro\models\FinanceiroLancamento';

    /**
     * @inheritdoc
     */
    public function filters()
    {
        return [
            'data' => [
                'filterType' => self::FILTER_TYPE_DATE,
            ],
            'tipo' => [
                'filterType' => self::FILTER_TYPE_SMALLINT,
                'dropDownOptions' => FinanceiroLancamento::getTipoOpts(),
            ],
            'valor' => [
                'filterType' => self::FILTER_TYPE_FLOAT,
            ],
            'status' => [
                'filterType' => self::FILTER_TYPE_SMALLINT,
                'dropDownOptions' => FinanceiroLancamento::getStatusOpts(),
            ],
        ];
    }

    /**
     * @inheritdoc
     */
    function gridColumns()
    {
        return [
            [
                'attribute' => 'descricao',
            ],
            [
                'attribute' => 'data',
            ],
            [
                'attribute' => 'tipo',
                'value' => function ($model) {
                    return $model->getTipoTxt();
                },
            ],
            [
                'attribute' => 'valor',
                'value' => function ($model) {
                    return \yoocloud\helpers\CurrencyHelper::format(\yoocloud\widgets\FloatField::unformat($model->valor), $model->moeda->codigo_iso_3);
                },
            ],
        ];
    }

    /**
     * @inheritdoc
     */
    public function sortAttributes()
    {
        return [
            'id',
            'data',
        ];
    }

    /**
     * @inheritdoc
     */
    public function search($params)
    {
        $query = FinanceiroLancamento::find();

        $dataProvider = $this->createDataProvider([
            'query' => $query,
        ]);

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

        $this
            ->buildFilter($query, 'data')
            ->buildFilter($query, 'tipo')
            ->buildFilter($query, 'valor')
            ->buildFilter($query, 'status');

        $this
            ->buildOrder($query);

        return $dataProvider;
    }
}

In the controller:

class LancamentoController extends Controller
{
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
            ],
        ];
    }

    public function actionIndex()
    {
        $grid = new LancamentoGrid();

        return $this->render('index', [
            'grid' => $grid,
        ]);
    }
}

In the view:

<div class="financeiro-lancamento-index">
    <?= $grid ?>
</div>

Result:

[dan-developer]

Another example with relationship:

namespace app\exemplos\grid;

use app\modules\cadastro\models\CadastroEndereco;
use Yii;
use app\modules\cadastro\models\Cadastro;
use app\modules\cadastro\models\Cliente;
use yoocloud\grid\BaseGrid;

class ClienteGrid extends BaseGrid
{

    public $modelClass = 'app\modules\cadastro\models\Cliente';

    /**
     * @inheritdoc
     */
    public function gridColumns()
    {
        return [
            'nome_exibicao',
        ];
    }

    /**
     * @inheritdoc
     */
    public function filters()
    {
        return [
            'nome' => [
                'filterType' => self::FILTER_TYPE_STRING,
            ],
            'data_nascimento' => [
                'filterType' => self::FILTER_TYPE_DATE,
            ],
            'pais_id' => [
                'filterType' => self::FILTER_TYPE_RELATIONSHIP,
                'queryColumnName' => CadastroEndereco::tableName() . '.pais_id',
                'widgetOptions' => [
                    'pluginOptions' => [
                        'ajax' => [
                            'url' => ['/admin/api/localization-country/suggest'],
                        ],
                    ],
                ],
            ]
        ];
    }

    /**
     * @inheritdoc
     */
    public function sortAttributes()
    {
        return [
            'nome',
            'pais' => CadastroEndereco::tableName() . '.id',
        ];
    }

    /**
     * @inheritdoc
     */
    public function search($params)
    {
        $query = Cliente::find();

        $query
            ->joinWith([
                'enderecos',
            ]);

        $dataProvider = $this->createDataProvider([
            'query' => $query,
        ]);

        $query->andFilterWhere(['tipo_cadastro' => Cadastro::TIPO_CADASTRO_CLIENTE]);

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

        $this
            ->buildFilter($query, 'data_nascimento')
            ->buildFilter($query, 'pais_id')
            ->buildFullTextSearch($query, 'nome', ['nome', 'sobre_nome', 'nome_fantasia', 'razao_social']);

        $this
            ->buildOrder($query);

        return $dataProvider;
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        $labels = parent::attributeLabels();
        $labels['pais_id'] = Yii::t('app', 'País');

        return $labels;
    }

}

Result:

[kartik-v]

Good thoughts - let know if you can create a PR to these.

But I can suggest some simpler solutions without much changes to the current code (you just need to modify a bit of your search method that generates the dataProvider)

Number range filtering

Use my kartik-v/yii2-widget-slider extension as the filter input

Date range filtering

Use my kartik-v/yii2-date-range extension as the filter input

See a list of filter values like Excel

Use my kartik-v/yii2-widget-select2 extension as the filter input.

[dan-developer]

Ok. I just need to pass the documentation to English. To be exact, this process uses kartik-V components, only change the settings. The classes I extends to what would eventually have to make a change, make it easier, I think it's better this way than using ClassMap, etc ... So in reality "BaseGrid" not extends "kartik\grid\GridView", but uses it. Download the code for http://yoocloud.com.br/yoocloud.zip link and see how I can help you. I believe that this implementation will leave even richer components and greatly help people who are certainly doing these manual filters with "_search_form".

Thank you.

[dan-developer]

Perhaps the most interesting would be to create a widget, such as DynaGrid. I'm not sure if it developed that solves all possible situations, but so far in my project solved all kinds of filters need.

Many of our customers are not happy with simple filters, they want more options, "like", "different", "between", "contains", "relationship", etc...

Thank you.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[Subbu12321]

@dan-developer That filter looks great, but the link you provided seems to be broken http://yoocloud.com.br/yoocloud.zip Can you please add any other link for that if possible.