Query condition including NULL values

Yii 2.0 General Discussions
1

I am trying to generate a query which will include a condition for NULL values.

Basically the query I want to generate is this:


SELECT *

FROM `product`

WHERE (`active`=1 AND `deleted`=0) AND ((`date` BETWEEN 1800 AND 2016) OR (`date` IS NULL))

Here is the code I am using to try and generate the query:


$query = $this->find();


$query->where(['active' => 1, 'deleted' => 0]);


$query->andFilterWhere([

    'between', 'date', $this->year_min, $this->year_max,

]);


$query->orWhere([

    'date' => null,

]);

This is obviously incorrect, as the query it is generating is this:


SELECT *

FROM `product`

WHERE ((`active`=1) AND (`deleted`=0)) AND (`date` BETWEEN 1800 AND 2016) OR (`date` IS NULL)

Does anyone know the correct way to do this?

2

I think that you could use andWhere instead andFilterWhere, so query should be:




$query->andWhere([

    'or', 

    ['between', 'date', $this->year_min, $this->year_max],

    ['date' => null]

]);
3

Something like this should work:




...

$query->filterWhere([

    'or',

    ['between', 'date', $this->year_min, $this->year_max],

    ['date' => null],

]);

...

See this docs.

4

Sorry Frabrizio, we were formulating the answer at the same time.

5

You are welcome!

6

Thanks. This works perfectly :)