[firegate666]

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?

[yugene]

Hi,
An interesting question.
I found two solutions so far:
1. 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);

2. Write my own method.

[firegate666]

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.

[firegate666]

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