how to build query like `select ... where id % 2 = 1` using Yii 2 ？

Question

Surely, I can Yii::$app->db->createCommand($sql)->query(), but what if I wanna use a ActiveRecord::find()->where($conditions) to do this job ?

Answer 1

Here is one of the options with using yii\db\Expression:

use yii\db\Expression;

...

$models = Customer::find()
    ->select(['id', 'name', ...])
    ->where(new Expression('id % 2 = 1')])
    ->all();

It's definetely better than raw sql and ['%2=', 'id', 1] because it follows the order and more readable in my opinion.

['%2=', 'id', 1] also is not suitable here because %2= is not actually an operator like not in or like for example, so operator, value and = sign are kind of mixed together.

Official docs:

• yii\db\Expression

Update: I asked samdark, one of the main framework contributors in official Gitter chat and he said that the right way to do it is using yii\db\Expression.

Answer 2

You can achieve this using Active Query too. This may help.

$customers = Customer::find()
    ->select(['id', 'name', ...])
    ->where('id % 2 = 1')
    ->all();

or

$customers = Customer::find()
    ->select(['id', 'name', ...])
    ->where(['% 2 =', 'id', 1])
    ->all();

Reference.