Allow relational operators (>, <, <>) when specifying select conditions

[gpoehl]

Only a limited number of relational operatorts can be set when specifying select conditions.
The basic relational operators like <, >, <> and others like 'least' are not supported.

->andWhere(['between','plz',40000,50000])

works well but

->andWhere(['>','plz',40000])

should also be possible.

Right now you have to write

->andWhere('plz > 40000'])

[Ragazzo]

->andWhere('plz > 40000'])

Should be

->andWhere('plz > :plz',[':plz' => 40000]);

Dont see any profit from adding this, since there no custom operators that can not be supported by rdbms, can you show some other use cases?

[gpoehl]

Right, that's a a way to set the value. But the column will still not be quoted.
I also think that this request would result in a more consitent way to create a select condition.

[Ragazzo]

But the column will still not be quoted.

well, this is true, and is bug, should be fixed. For example:

Users::find()->andWhere('login = :login',[':login' => 'bernier.marcella.someone'])->one()

// SELECT * FROM "users" WHERE login = 'bernier.marcella.someone'

column login not quoted.

I also think that this request would result in a more consitent way to create a select condition.

can you give an example of that use case?

[gpoehl]

There is no special use case. I'm just wondering why some relational operators are supported and others not. Why not just accept every operator and let the database do the job. If the operator is invalid you'll get an error message from the database.

 $adrs=  \frontend\models\Adresse::find()
                ->andWhere(['like','adr','hotel'])
                ->andWhere(['between','plz', '40000', '80000'])
                ->andWhere('aend_dat > "2000-01-01"')
                ->all();

The example shows that syntax is differnet for field aend_dat and that you also need to pay attention to qoute the value when it is a non numeric data type.
This example makes no use of any params to keep it simple.

[samdark]

Related docs: https://github.com/yiisoft/yii2/blob/master/docs/guide/database-basics.md#quoting-table-and-column-names

[Ragazzo]

@samdark but that docs refers to the example when i manually create query string, building it with methods is just another case, it was quoted in Yii1, why not quote it in Yii2? or i missed something?

[samdark]

Same in your case. Here's manual creating a string:

Users::find()->andWhere('login = :login',[':login' => 'bernier.marcella.someone'])->one()

And here's not manual:

Users::find()->andWhere(['login' => 'bernier.marcella.someone'])->one()

[Ragazzo]

Oh, ok then, can you add to docs that my above example is also counted as manual creating and will not be qouted?

[samdark]

Will do. The original request is valid as well, need to think about adding more operation shortcuts.

[SDKiller]

Vote for the feature.

Having the same approach for most used comparison operators would be fine:

$q->where(['>=', 'order_date', date('Y-m-d H:i:s', $time)]);

At list something like this could be added to QueryBuilder (all comparison operators can be processed in one method):

    protected $conditionBuilders = [
       ...
        '>'  => 'buildCompareCondition',
        '>=' => 'buildCompareCondition',
        '<'  => 'buildCompareCondition',
        '<=' => 'buildCompareCondition',
        '<>' => 'buildCompareCondition',
        '!=' => 'buildCompareCondition',
    ];

About <> (!=) - yes, as of now it can be achieved with existing methods.
But getting <> via not in and single array value in buildInCondition() is a little bit hacky.
As for negating equal operator in a way NOT ('column' = "value") - unexpected collisions may occur due to different operator precedence http://dev.mysql.com/doc/refman/5.6/en/operator-precedence.html
That is why its better to have unified <> usage with other comparison operators.

[samdark]

Fixed.

[samdark]

I'll take care of the rest of the issue today.

[RomeroMsk]

@samdark, does filterWhere also support relational operators too (in latest release)? If so, I will test and close my #3396.

[samdark]

It should. You're very welcome to check it.

[RomeroMsk]

It works, thank you!

[tumdav]

Operator '!=' not working when attribute value is null.

andWhere(['!=', 'attr', 'value']) 

[cebe]

that is expected because the operator is passed directly to the SQL. If you need more magic, you can use ['not', ['attr' => $value]]. Next time please open a new issue instead of posting under closed old issues. Thanks.