Trouble With Between Statement In Querybuilder

Yii 2.0 General Discussions
1

I use Yii2 with a MySQL database. In a query wanted to use ‘between’ in QueryBuilder. Unfortunately I didn’t get a result back.

My code Fragment is:




$now = new \yii\db\Expression('NOW()');

$query->where = ['between', $now, $attrValidFrom, $attrValidUntil ];

The produced raw SQL is:




"SELECT COUNT(*) FROM `rcv_generic_code_value` WHERE NOW() BETWEEN 'rcv_valid_from' AND 'rcv_valid_until'"

This query produces an error within MySQL because the column names are quoted instead of backquoted. I found out that Yii2 always expects that the between statment is: "COLUMN BETWEEN fixValue1 AND fixVALUE2". My statement is "fixVALUE BETWEEN COLUMN1 AND COLUMN2"

The correct SQL should be:




"SELECT COUNT(*) FROM `rcv_generic_code_value` WHERE NOW() BETWEEN `rcv_valid_from` AND `rcv_valid_until`"

If I want to use QueryBuilder at the moment I have to substitute the where clause by "rcv_valid_from <= NOW() AND rcv_valid_until >= NOW()"

I want to know your opinion. Is my between type an exotic one or could this happen often? Should the function "buildBeetweenConditon" in QueryBuilder more complex to find out which operand is a column name?

2

Looks like exotic to me.

Value BETWEEN col1, col2 == (value >= col1 && value <= col2), so you can use two andWhere() instead.

3

You need to use yii&#092;db&#092;Expression in this case. Otherwise the parameter will be treated as a string and thus quoted as a string.

4

I am also having problems with the same scenario. But I cant get it to work as expected.

This one works ok:


$this->andWhere('current_date() BETWEEN [[start]] AND [[end]]');

But this does not:


$this->andWhere(['BETWEEN', new \yii\db\Expression('current_date()'),  'start',  'end']);

$this->andWhere(['BETWEEN', new \yii\db\Expression('current_date()'),  '[[start]]',  '[[end]]']);

$this->andWhere(['BETWEEN', new \yii\db\Expression('current_date()'),  new \yii\db\Expression('start'),  new \yii\db\Expression('end')]);

How is it supposed to be defined when using the array syntax?