如何使用CDbCriteria Yii在CGridView中的DATE / TIME列上实现排序

Question

I am struck in a Problem from last three day but didn't find any useful solution

I want to implement a custom CDbCriteria on CGridView to sort the LD_DATE Column in Desending order. But each time it sort the Column as String.

I want to sort on the base on Data and Time. Please any body can figure out the issue. What the wrong with my code

LogDetailController.php

public function actionAdmin($id){       
        $criteria=new CDbCriteria;
        $criteria->select=" LD_ID, LM_ID, LD_TITLE, LD_DESC, LD_CONTROLLER, LD_ACTION, LD_ACTION_ID, LD_DATE ";
        $criteria->order = "LD_DATE DESC";
        $criteria->limit = "25";
        $criteria->addCondition("LM_ID=:LM_ID");
        $criteria->params=array(':LM_ID'=>$id);
        $dataProvider = LogDetail::model()->findAll($criteria);
        $this->render('admin',array(
                'model'=>$dataProvider,
        ));
}

admin.php

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'log-detail-grid',
    'dataProvider'=>$model,
    'columns'=>array(
        'LD_TITLE',
        'LD_DESC',
        'LD_CONTROLLER',
        'LD_ACTION',
        'LD_ACTION_ID',
        'LD_DATE',
    ),
)); 

Database Script

CREATE TABLE `log_detail` (
  `LD_ID` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `LM_ID` int(10) unsigned NOT NULL,
  `LD_TITLE` varchar(100) DEFAULT NULL,
  `LD_DESC` varchar(200) DEFAULT NULL,
  `LD_CONTROLLER` varchar(50) DEFAULT NULL,
  `LD_ACTION` varchar(50) DEFAULT NULL,
  `LD_ACTION_ID` int(11) DEFAULT NULL,
  `LD_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`LD_ID`),
  KEY `LM_ID` (`LM_ID`),
  CONSTRAINT `log_detail_ibfk_1` FOREIGN KEY (`LM_ID`) REFERENCES `log_master` (`LM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

I found the solution and Its working fine.

LogDetail.php

class LogDetail extends CActiveRecord {
    public function viewLog($id) {
        $criteria=new CDbCriteria;
        $criteria->alias="t";
        $criteria->select="t.LD_ID, t.LD_TITLE, t.LD_DESC, t.LD_DATE, t.LD_CONTROLLER, t.LD_ACTION, t.LD_ACTION_ID";
        $criteria->limit = "50";
        $criteria->addCondition("LM_ID=:LM_ID");
        $criteria->params=array(':LM_ID'=>$id);
        $criteria->order = " LD_DATE DESC ";
        return new CActiveDataProvider($this,array('criteria'=>$criteria,'pagination' => array('pageSize' => 50)));
    }
}

LogDetailController.php

class LogDetailController extends Controller {
    public function actionHistory($id) {
        $model = new LogDetail('viewLog');
        $model = $model->viewLog($id);

        $this->render('logdetail',array(
                'dataProvider'=>$model, 
        ));
    }
}

logdetail.php

$this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'log-detail-grid',
    'dataProvider'=>$dataProvider,
    'columns'=>array(
        array(
            'name' => 'LD_TITLE',
            'type' => 'raw',
            'value' => '$data->LD_TITLE'
        ),
        array(
            'name' => 'LD_DESC',
            'type' => 'html',
            'value' => '$data->LD_DESC."<br />Action: <strong><u>".Yii::app()->request->baseUrl."/index.php/".$data->LD_CONTROLLER."/".$data->LD_ACTION.(($data->LD_ACTION_ID != null) ? ("/".$data->LD_ACTION_ID) : ("") )."</u></strong><br />"'
        ),
        array(
            'name' => 'LD_DATE',
            'type' => 'html',
            'value' => '"<center>Date:&nbsp;".explode(" ", $data->LD_DATE)[0]."<br />Time:&nbsp;".explode(" ", $data->LD_DATE)[1]."</center>"'
            ),
        ),
    )
);