Yii2 ActiveQuery: is it possible to combine andWhere and orWhere?

Question

Not sure if the title fully describes the problem, anyway here is the situation: suppose I have an ActiveQuery descendant class that looks like this:

class OrderQuery extends ActiveQuery
{
    public function expired() {
        $this->andWhere(['<=', 'order.expiresAt', date("Y-m-d H:i:s")]);
        return $this;
    }

    public function cancelled() {
        $this->andWhere(['NOT', ['order.cancelled' => null]]);
        return $this;
    }
}

I'd like to add an additional method, archived(), to find orders that are expired OR cancelled.

public function archived() {
    $this->andWhere(["OR",
        ['<=', 'order.expiresAt', date("Y-m-d H:i:s")],
        ['NOT', ['order.cancelled' => null]],
    ]);
    return $this;
}

The method above works fine, but I wonder, is it possible to reuse the existing methods expired() and cancelled() in the new archived() method?

---

Or, in other words, is it possible to combine andWhere and orWhere somehow to make them work together like this:

// pseudocode, not a real code!
$this->orWhere([
    $this->andWhere([...]),
    $this->andWhere([...]),
]);

Thanks!

Answer 1

Currently there is no straight way to join conditions of multiple queries. On GitHub there is already a feature request for this, but it is not implemented yet.

Currently you may try 2 solutions:

1. If you don't want to repeat conditions, you may create helper for them:

   private function getExpiredContition() {
       return ['<=', 'order.expiresAt', date('Y-m-d H:i:s')];
   }
   
   private function getCancelledContition() {
       return ['NOT', ['order.cancelled' => null]];
   }
   
   public function expired() {
       return $this->andWhere($this->getExpiredContition());
   }
   
   public function cancelled() {
       return $this->andWhere($this->getCancelledContition());
   }
   
   public function archived() {
       return $this->andWhere([
           'OR',
           $this->getExpiredContition(),
           $this->getExpiredContition(),
       ]);
   }
   

2. You may try to access $where property directly and merge it in one query.

   public function archived() {
       return $this->andWhere([
           'OR',
           (new static())->cancelled()->where,
           (new static())->expired()->where,
       ]);
   }
   

Answer 2

$this->orWhere([
    $this->andWhere([...]),
    $this->andWhere([...]),
]);

I'm afraid there is no way to use where() functions inside another where() functions. Yii2 is simply not designed that way.. it would be possible if Yii2 where(), orWhere(), andWhere() functions received a Closures, like it do in joinWith(), with(), etc. methods (receiving a nested models Query in anonymous function).

You can put closures in Laravel's where conditions (off topic)

I guess, as a workaround you can create private methods and return those conditions in array format as follow:

class OrderQuery extends ActiveQuery
{
    private function conditionExpired() {
        return ['<=', 'order.expiresAt', date("Y-m-d H:i:s")];
    }

    private function conditionCancelled() {
        return ['NOT', ['order.cancelled' => null]];
    }

    public function expired() {
        $this->andWhere($this->conditionExpired());
        return $this;
    }

    public function cancelled() {
        $this->andWhere($this->conditionCancelled());
        return $this;
    }

    public function archived() {
        $this->andWhere(["OR",
            $this->conditionExpired(),
            $this->conditionCancelled(),
        ]);
        return $this;
    }
}