Query Improvement


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.

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.


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'";

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.

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

How about:





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


By the way: ‘addInCondition’ also exists.