CODEWITHSUNDEEP
yii2
G'ofur N
G'ofur N

Reputation: 2652

Yii2 where() variable condition

I want when currency = 'sum' sort by and Where(['between', 'price', $min_price, $max_price]) and when currency = 'y.e.' sort by andWhere(['between', 'price', $min_price*2, $max_price*2]). How to write the sql query in yii2?

$anmt_t = (new \yii\db\Query())
    ->select(['*'])
    ->from('anmt')
    ->where(['status' => 'confirmed'])
    ->andWhere(['between', 'price', $min_price, $max_price, when (currency = 'sum')])
    ->andWhere(['between', 'price', $min_price*2, $max_price*2, when (currency = 'y.e.')])
    ->all();

Upvotes: 1

Views: 1125

Answers (2)

mrateb
mrateb

Reputation: 2499

I'd personally favor using Yii2, rather than writing a long query

$condition = currency == 'y.e.' ? ['between', 'price', $min_price *2, $max_price*2] : ['between',  'price', $min_price, $max_price];

then

$anmt_t = (new \yii\db\Query())
    ->select(['*'])
    ->from('anmt')
    ->where(['status' => 'confirmed'])
    ->andWhere($condition)
    ->all();

Upvotes: 1

Insane Skull
Insane Skull

Reputation: 9358

Try using CASE :

$anmt_t = (new \yii\db\Query())
        ->select(['*'])
        ->from('anmt')
        ->where(['status' => 'confirmed'])
        ->andWhere('
            CASE
                WHEN currency = "sum" THEN price BETWEEN :mp1 AND :mx1
                WHEN currency = "y.e." THEN price BETWEEN :mp2 AND :mx2
            END
        ')
        ->params([
            'mp1' => $min_price,
            'mx1' => $max_price,
            'mp2' => $min_price * 2,
            'mx2' => $max_price * 2,
        ])
        ->all();

Not tested

Upvotes: 2

Related Questions