Say, I have table named tbl_order on MySQL with structure like this (just illustration):
---------------------------------------------------------------------------------------------------------
| id | grade | type | order_date | order_confim_time | vol_in | vol_out | vol_dev | status |
| AI | VARCH | VARCH | TIMESTAMP | TIMESTAMP | DOUBLE | DOUBLE | DOUBLE | VARCH |
---------------------------------------------------------------------------------------------------------
| 81 | AAA | IN | 2014-09-10 09:00:00 | 2014-09-10 13:00:00 | 498000 | 0 | -2000 | CONFIRM |
| 83 | AAA | IN | 2014-09-10 10:01:00 | 2014-09-10 14:00:00 | 998000 | 0 | -2000 | CONFIRM |
| 85 | AAA | OUT | 2014-09-11 09:02:00 | 2014-09-11 13:00:00 | 0 | 99000 | -1000 | CONFIRM |
| 87 | AAA | OUT | 2014-09-11 10:03:00 | 2014-09-11 14:00:00 | 0 | 145000 | -5000 | CONFIRM |
| 89 | AAA | OUT | 2014-09-12 09:04:00 | 2014-09-12 13:00:00 | 0 | 120000 | -5000 | CONFIRM |
| 91 | AAA | OUT | 2014-09-12 09:06:00 | NULL | 0 | 130000 | -2000 | NOTCONF |
| 93 | AAA | OUT | 2014-09-13 10:05:00 | 2014-09-12 14:00:00 | 0 | 115000 | -5000 | CONFIRM |
---------------------------------------------------------------------------------------------------------
Then I did the following query:
select * from `tbl_order`
where `tbl_order`.`grade` = 'AAA' AND order_confim_time < NOW()
order by status DESC, order_confim_time ASC
So that in we could get following result:
---------------------------------------------------------------------------------------------------------
| id | grade | type | order_date | order_confim_time | vol_in | vol_out | vol_dev | status |
| AI | VARCH | VARCH | TIMESTAMP | TIMESTAMP | DOUBLE | DOUBLE | DOUBLE | VARCH |
---------------------------------------------------------------------------------------------------------
| 91 | AAA | OUT | 2014-09-12 09:06:00 | NULL | 0 | 130000 | -2000 | NOTCONF |
| 93 | AAA | OUT | 2014-09-13 10:05:00 | 2014-09-12 14:00:00 | 0 | 115000 | -5000 | CONFIRM |
| 89 | AAA | OUT | 2014-09-12 09:04:00 | 2014-09-12 13:00:00 | 0 | 120000 | -5000 | CONFIRM |
| 87 | AAA | OUT | 2014-09-11 10:03:00 | 2014-09-11 14:00:00 | 0 | 145000 | -5000 | CONFIRM |
| 85 | AAA | OUT | 2014-09-11 09:02:00 | 2014-09-11 13:00:00 | 0 | 99000 | -1000 | CONFIRM |
| 83 | AAA | IN | 2014-09-10 10:01:00 | 2014-09-10 14:00:00 | 998000 | 0 | -2000 | CONFIRM |
| 81 | AAA | IN | 2014-09-10 09:00:00 | 2014-09-10 13:00:00 | 498000 | 0 | -2000 | CONFIRM |
---------------------------------------------------------------------------------------------------------
I did write following function in my model Order in order to get previous row using recursive:
[size=2]public function getPrevRowHelper()[/size]
{
// currently I am using id, but this is not what I am expected since `order_confim_time` is NULL
$data = self::model()->find(array(
'condition' => '`t`.`id`<:current_id AND `t`.`order_grade`=:type_grade',
'order' => '`t`.`id` DESC',
'limit' => 1,
'params' => array(':current_id' => $this->id, 'type_grade' => $this->order_grade),
));
if (is_null($data)) return 0;
else {
return floatval($data->PrevRowHelper) +
floatval($data->vol_in) +
floatval($data->vol_dev) -
floatval($data->vol_out);
}
}
public function getPrevRow()
{
// since `order_confim_time` is NULL, i make exception here
if (is_null($this->order_confim_time))
{
$data = self::model()->find(array(
'condition' => '`t`.`id`=:current_id AND `t`.`grade`=:type_grade',
'order' => '`t`.`status` DESC, `t`.`id` DESC',
'limit' => 1,
'params' => array(':current_id' => $this->id, 'type_grade' => $this->grade),
));
if (is_null($data)) return 0;
else return floatval($data->PrevRowHelper);
} else {
$data = self::model()->find(array(
'condition' => '`t`.`order_confim_time`<:confim_time AND `t`.`grade`=:type_grade',
'order' => '`t`.`status` DESC, `t`.`id` DESC',
'limit' => 1,
'params' => array(':confim_time' => $this->order_confim_time, 'grade' => $this->grade),
));
if (is_null($data)) return 0;
else
return floatval($data->PrevRow) +
floatval($data->vol_in) +
floatval($data->vol_dev) -
floatval($data->vol_out);
}
}
The result quite good and only respects order_confirm_time with NOT NULL values.
How can I get row with id = 93 without needed depends on ORDER BY id DESC even though this row have NULL value in order_confirm_time?
I am using YII 1.1.16-branch