Query Improvement

Yii 1.1 General Discussion
1

Hi,

I have this query but it is taking a long time to run. I’m new to Yii. Does anyone can help me with a faster query?

I need to selected everything on my table that has some status value. Please see query below:




$criteria=new CDbCriteria;

$criteria->condition = "t.current_status = 'Driver Assigned' OR t.current_status = 'Order Allocated' OR t.current_status = 'Order Accepted' OR t.current_status = 'Relay' OR t.current_status = 'Waiting' OR t.current_status = 'In Transit' OR t.current_status = 'Loading' OR t.current_status = 'Unloading'";


$criteria->order = "t.order_eta";

$jobmodel = Job::model()->findAll($criteria);

Thanks for your help.

2

What value does the field have, when no status is set? If it’s a NULL value, you can do this:




$criteria->condition = "t.current_status IS NOT NULL";

I assume that would be faster than checking for each possible value.

3

Bluesun,

In this case my current status is never null, I have other status, so instead of being null that status or either are Cancelled or Returned. Should I do this?




$criteria->condition = "t.current_status IS NOT 'Cancelled' || 'Returned'";
4

I believe the correct syntax would be like this:




$criteria->condition = "t.current_status != 'Cancelled' AND t.current_status != 'Returned'";

Be sure to use AND here, not OR, because u want the status to be not equal to Cancelled and also not equal to Returned.

5

Awesome, thank you so much!!! It is much faster now.

6

How about:




$criteria->addNotInCondition('t.current_status',array('Cancelled','Returned'));

Better:





        $table=Job::model()->getTableAlias(false,false);

	$ds=Job::model()->getDbConnection()->getSchema();

        $current_status =   $ds->quoteColumnName("$table.current_status");


	$criteria->addNotInCondition($current_status,array('Cancelled','Returned'));

By the way: ‘addInCondition’ also exists.