Yii2: Comparing operators like >,< >=,<= to search in GridView

[indicalabs]

Yii1 Gridview has a feature to search with comparing operators like >,< >=,<= in GridView,

I can't see they are working now with gii generated code?

[Ragazzo]

where have you seen this feature? its only in debug module afaik in Yii2.

[cebe]

@Ragazzo yii1.1 has this.

[indicalabs]

I think that will be much useful having it.

[cebe]

related to #2315

[ItsReddi]

hmm. any workaround on this?
we used this feature heavily in our yii1 projects and was confused thats not working right now.

the use case is simple.. you have a grid with a bunch of dates and you only want to see the data after a specific date... or an int anything bigger than xyz and so on.

[ItsReddi]

if anyone needs a workaround:

 public function search($params)
    {
        $query = AllEdition::find();

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        if (isset($_GET['EvtSearch']) && !($this->load($params) && $this->validate())) {
            return $dataProvider;
        }

        foreach (AllEdition::getSearchColumns() as $columnname){
            $operator = $this->getOperator($this->$columnname);
            $operand = str_replace($operator,'',$this->$columnname);
            $query->andFilterWhere([$operator, $columnname, $operand]);
        }

        return $dataProvider;
    }

    private function getOperator($qryString){
        switch ($qryString){
            case strpos($qryString,'>=') === 0:
                $operator = '>='; 
            break;
            case strpos($qryString,'>') === 0:
                $operator = '>';
                break;
            case strpos($qryString,'<=') === 0:
                $operator = '<=';
                break;
            case strpos($qryString,'<') === 0:
                $operator = '<';
                break;
            default:
                $operator =  'like';
                break;
        }
        return $operator;
    }

[nikbas]

I did this to filter pages on number of views:

if (!empty($this->views)) {
            preg_match('/(<=|>=|<|>)/', $this->views, $operator);
            preg_match('/\d+/', $this->views, $views);
            $operator = isset($operator[0]) ? $operator[0] : '=';
            $views = isset($views[0]) ? $views[0] : '0';

            $query->andFilterWhere([$operator,'views', $views]);
        }

[ItsReddi]

how did you archive that the operator comes back to the filter field after filtering?

[nikbas]

Not sure what you mean.
Anything you enter in the filter field will stay there after submission. The operator is entered with the view count >100 so it will stay there.
This was just a quick fix because it was needed for that column only, if it was needed for multiple columns I would've created separate method to extract operator and value.

[ItsReddi]

the problem is you have to replace the operator in views otherwise the sql is wrong.

e.g. if operator is '>='
select * from foo where views >= '>=100';

because of that i replace the operator with an empty string.
$this->$columnname = str_replace($operator,'',$this->$columnname);
But then it will be removed in the filter field.

[nikbas]

What my example does is it extracts operator >= and operand 100 from filter input which is $this->views using two preg_match() functions. It doesn't modify the filter input $this->views.
So you'll get $query->andFilterWhere(['>=', 'views', '100']).

In your case you modify the filter input. Try this in your foreach:

 $operator = $this->getOperator($this->$columnname);
 $operand = str_replace($operator,'',$this->$columnname);
 $query->andFilterWhere([$operator, $columnname, $operand]);

[ItsReddi]

oh didnt saw that. thanks for the hint.

[nsanden]

@ItsReddi I'm trying your workaround but what is getSearchColumns()?

[ItsReddi]

its just an array of fields.
return ['fieldA','fieldB','fieldC','fieldD'];

[mklemarczyk]

:+1: What happen with compare function from CDbCriteria class form Yii 1.x?
I need this feature because a lot of my users use it.

Notice that:
I see some potential problem with this feature in Yii 2.0, because search attributes must be valid.
For example we can have quantity attribute with rule integer. In fact the expression >10 will be invalid.

[ilyapoz]

Hey, guys. I wonder how do you envision implementing this? I will need this for rest api in my project and can dig and make a contribution, will just need a few pointers.
Just improving the code generated by gii to replace simple andFilterWhere with more elaborate ones?
Where do you suggest putting the parsing code? @qiangxue @samdark

[cebe]

@ilyapoz we have no concrete ideas or plans on this. you could make a proposal for an implementation and we can discuss it.

[lennartvdd]

Something like that? ^^ I'd find this feature very helpful too. Yii 1's CDbCriteria::compare() was very user friendly.

[ilyapoz]

@lennartvdd I guess functions with the property "Note that when the value is empty, no comparison expression will be added to the search condition." have "filter" in their name in yii2 by convention.

[lennartvdd]

ah alright! Makes sense! how about andFilterCompare() ?

[ilyapoz]

I guess it's time to open a PR :)

[lennartvdd]

any updates on this issue?

[ArekX]

@ilyapoz
I would actually just extend andFilterWhere, orFilterWhere, andWhere, orWhere, etc...

To use currently available operator format [operator, operand1, operand2, ...] where operator would be an instance of an implementation of an abstract class QueryComparator, let's say NumericQueryComparator which implements comparator functions like compare($value, $withValue), in this case the comparing with operators which are in $withValue. Of course QueryComparator would at the very least extend yii\base\Object to allow configuration.

This way we would keep implementations of QueryInterface pure, and also allow for custom implementations of QueryComparator which people can use. And also it will allow for easier implementation for different DBMS which Yii uses.

As for validation, I would extend NumberValidator via additional allowOperators feature or make another class like ComparableNumberValidator which allows numbers with operators.

[Faryshta]

@ArekX i don't think its that simple and even if you manage to do that, how do you fix the validation rules?

[['price'], 'number']

for example, what if I send &price=<500 on the url?

[ArekX]

@Faryshta
That is why I said to implement additional parameter allowOperators so in this case it would be like:
[['price'], 'number', 'allowOperators' => true] and then it should recognize price=<500 as a value with operator.

[Faryshta]

thats the proposed syntax, but how do you solve it?

i mean how do you propose allowOperators to work internally?

[ArekX]

If you look at this line in the NumberValidator

You will see a number pattern which is used for checking whether or not a value is an actual number. You can easily prefix this regex with operators so it will look like this:
^(>|<|>=|<=|=|)\s*[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?\s*$

So if you make another public variable like operatorNumberPattern and switch the check with this pattern inside validateAttribute function when allowOperators is true checking numbers with operators becomes possible.

Of course you would have separate a number from operator to perform min and max checks, and by grouping the second part of a number like this: ^(>|<|>=|<=|=|)(\s*[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?\s*)$ that too becomes possible. Or you can also just strip the operator via str_replace since it's the first and sometimes second character. But I'm for the first solution since it's cleaner to implement (i.e. doesn't use switch-case) and allows more operators to be added easily if someone needs it (like for instance the <> operator to exclude this value from the search).

Another solution would be to put operators into a separate variable like $operators and generate a pattern in a way like $pattern = "/^({$this->operators})({$this->numberPattern})$/". This one has flaws, since $numberPattern has / at beginning and end, and those would have to be stripped, and by this people can use different delimiter which would make things endlessly complicated.

Same should also apply for when integerOnly is true by using with another public variable operatorIntegerPattern which is integerPattern prefixed with these operators which in this case would be ^(>|<|>=|<=|=|)(\s*[+-]?\d+\s*)$

Because this code could easily get jumbled up with standard NumberValidator behavior it may be better to make another validator class (i.e. ComparableNumberValidator which I mentioned) which only implements operatorNumberPattern and operatorIntegerPattern without numberPattern and integerPattern to keep code clean and simple.

[Faryshta]

thats a good idea. i think you have solved it to the point where you can make a patch and tests.

[werta600]

So? Is this implemented or any plans to implement this option?

[lennartvdd]

I did not implement anything from the latest comments, but there's a PR for a GridView comparison function ready and waiting to be merged for quite a while now in #8505

[cebe]

merged https://github.com/yiisoft/yii2/pull/8505, keeping this open for documentation.