Yii Framework Forum: get AND OR Criteria to work - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

get AND OR Criteria to work Rate Topic: -----

#1 User is offline   firegate666 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 23-February 12

  Posted 27 June 2012 - 05:20 PM

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?
0

#2 User is online   yugene 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 513
  • Joined: 08-August 09

Posted 27 June 2012 - 10:04 PM

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

#3 User is offline   firegate666 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 23-February 12

Posted 28 June 2012 - 02:42 AM

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

#4 User is offline   firegate666 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 23-February 12

Posted 28 June 2012 - 02:52 AM

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
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users