get AND OR Criteria to work

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?

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);



  1. 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