I have a Yii Application that has to run on postgres and mysql. So I can’t use this kind of conditions:
$criteria_is_running->condition = '
(startDate IS NULL OR startDate <= :today)
AND
(stopDate IS NULL OR stopDate >= :today)
');
I have camel-case column names so they have to be escaped for Postgres to work. But if I add " " around the columns, it fails on MySQL because MySQL expects
So I am now trying to rewrite my condition using CdbCriterias.
I tried it this way:
$criteria_is_running->addInCondition('startDate', array(null));
$criteria_is_running->addBetweenCondition('startDate', $nowDate->format('Y-m-d'), '1970-01-01', 'OR');
$criteria_is_running->addInCondition('stopDate', array(null), 'AND');
$criteria_is_running->addBetweenCondition('stopDate', '1970-01-01', $nowDate->format('Y-m-d'), 'OR');
But this nests my conditions in a wrong way. How can I get this to work?
(((startDate IS NULL) OR (startDate BETWEEN :ycp0 AND :ycp1)) AND (stopDate
IS NULL)) OR (stopDate BETWEEN :ycp2 AND :ycp3))
Anyone can help?
yugenekr
(Yugenekr)
June 28, 2012, 3:04am
2
Hi,
An interesting question.
I found two solutions so far:
Use mergeWith()
$criteria2 = new CDbCriteria();
$criteria2->addInCondition('startDate', array(null));
$criteria2->addBetweenCondition('startDate', date('Y-m-d'), '1970-01-01', 'OR');
$criteria3 = new CDbCriteria();
$criteria3->addInCondition('stopDate', array(null), 'AND');
$criteria3->addBetweenCondition('stopDate', '1970-01-01', date('Y-m-d'), 'OR');
$criteria3->mergeWith($criteria2);
Write my own method.
Great, that was what I was looking for. I think it was far too late yesterday for me to recognize it while looking at.
The condition is now created as expected.
One drawback on it: It does no quoting of the column names which is sad. I guess I have to write my method for it.
Ok, I think I found out that criteria can’t achieve that one as it has no knowledge about the table or database my models are using.
I implemented a helper in my model parent class:
public function quotedCol($col_name) {
return $this->getTableSchema()->getColumn($col_name)->rawName;
}
And use this one for my criterias
$adventure_model = Adventure::model();
$criteria_is_running_start->addInCondition($adventure_model->quotedCol('startDate'), array(null));
I will think about a more generic solution for my project in the future