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.
bluesun
(Edwin)
April 2, 2015, 4:38pm
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.
bluesun:
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.
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'";
bluesun
(Edwin)
April 2, 2015, 6:03pm
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.
bluesun:
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.
le_top
(Ext Yiiframework Com)
April 3, 2015, 8:39pm
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.