Yii2 DB Expression in ActiveQuery::andFilterWhere()

Question

I have the following code which filters an ActiveQuery

// Start date condition
if ($this->start_date) {
    $query->andWhere('event_datetime >= :start_date', [
        'start_date' => $this->start_date,
    ]);
}

// End date condition
if ($this->end_date) {
    $query->andWhere('event_datetime < :end_date + INTERVAL 1 DAY', [
        'end_date' => $this->end_date,
    ]);
}

where $this->start_date and $this->end_date are either null, '', or in the form Y-m-d, and event_datetime is an indexed DATETIME column

Provided that both date properties are true, this code produces the following SQL

WHERE event_datetime >= :start_date
  AND event_datetime < :end_date + INTERVAL 1 DAY

I can rewrite the start date condition as

$query->andFilterWhere(['>=', 'event_datetime', $this->start_date);

Is it possible to rewrite the end date condition simlarly, perhaps using some kind of DB expression?

I'd rather keep the '+ INTERVAL 1 DAY' in SQL, if possible, and still be able to use an index on event_datetime

It's not possible. "filterWhere" methods ignore conditions where operand is empty. But your operand would never be empty if you add '+ INTERVAL 1 DAY' string. — Michal Hynčica

Muhammad Omer Aslam Muhammad Omer Aslam · Accepted Answer · 2019-08-29T21:02:47

Apart from your date is null or empty '', what I am trying to understand is that why are you not using DateTime class along with DateInterval to set the $this->end_date to the final date after adding the interval and compare it with your column event_datetime rather than getting confused about it.

$endDate = $this->end_date;

if ($this->end_date !==null && $this->end_date!=='') {
    $endDateObj = new \DateTime($this->end_date);
    $endDateObj->add(new \DateInterval('P1D'));
    $endDate = $endDateObj->format('Y-m-d');
}

$query->andFilterWhere(['<','event_datetime', $endDate]);

BUT, if you are still feeling sleepy head and dizzy and not feel like doing it in PHP for having personal reasons like increase in Blood Pressure, insomnia or your body start shivering thinking of PHP :P you can do it via \yii\db\Expression() in the following way which will produce the query like WHERE created_at < '2019-10-22' + INTERVAL 1 DAY, i have tested it but not sure how accurate it will be so test it before you go for it.

$query->andFilterWhere(
    [
        '<',
        'event_datetime',
        new \yii\db\Expression("'$this->end_date' + INTERVAL 1 DAY")
    ]
);

EDIT

You have to check manually the value for null and empty before you use any of the methods above and that is the only way you can do that. A little addition is needed to control the blank or null value which wont be controlled in the Expression automatically and will give incorrect results so change to the following

$expression = null;
$isValidDate = null !== $this->end_date && $this->end_date !== '';

//prepare the expression if the date is not null or empty
if ($isValidDate) {

    $expression = new Expression(
        ":my_date + INTERVAL 1 DAY",
        [
            ':my_date' => $this->end_date,
        ]
    );
}

$query->andFilterWhere(
    [
        '>',
        'end_date',
        $expression,
    ]
);

Yii2 DB Expression in ActiveQuery::andFilterWhere()

2 Answers

EDIT