Yii - Sort Error while using Join query in CDbCriteria with CActiveDataProvider

Question

Model Search Method

  $criteria->alias = 'c';
  $criteria->select = 'c.*,max(ca.date) AS lastactivity';
  $criteria->join = 'LEFT JOIN tbl_contact_action AS ca ON (ca.contact_id=c.contact_id)';
  $criteria->condition = 'c.status<>"Deleted"';
  $criteria->group = 'c.contact_id';
  $criteria->order = 'lastactivity DESC';

$sort = new CSort;
        $sort->defaultOrder = array('lastactivity' => CSort::SORT_DESC); //'name ASC';        
        $sort->attributes = array(
            'name' => 'name',
            'email' => 'email',
            'status' => 'status',
            'createdon' => 'createdon',
            'lastactivity' => 'lastactivity',
        );
        $sort->applyOrder($criteria);
        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'sort' => $sort,            
        ));

Basically, I have a 1:n relationship where in I need only latest record from child table. The parent table data will be displayed based on the comment that is done latest in child table. How to make this field sortable ?

Error

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.lastactivity' in 'order clause'.

Answer 1

Below is the Model which will allow to have a custom/computational field that will be SORTABLE. Instead of simply writing 'lastactivity'=>'lastactivity' in sort array, passing whole array did the trick for me. Hope it helps someone :)

class Contact extends CActiveRecord {

public $verifyCode;
public $lastactivity;

public static function model($className = __CLASS__) {
    return parent::model($className);
}

public function tableName() {
    return '{{contact}}';
}

public function rules() {
    return array(
        array('name, email, subject, message', 'required', 'message' => Yii::t('app', 'MSG_ATTRIBUTE_BLANK')),
        array('email', 'email'),
        array('verifyCode', 'CaptchaExtendedValidator', 'allowEmpty' => !CCaptcha::checkRequirements(), 'on' => 'fContact'),
        array('name, email,subject,message,lastactivity', 'safe', 'on' => 'search'),
        array('name, subject, email, message, status,createdon,updatedon,verifyCode,lastactivity', 'safe'),
    );
}

public function relations() {
    return array(
        'ca' => array(self::HAS_MANY, 'ContactAction', 'contact_id'),               
    );
}

public function search() {

    $criteria = new CDbCriteria;
    $criteria->compare('name', CommonFunctions::escapeOperator($this->name), true, 'OR');
    $criteria->compare('subject', CommonFunctions::escapeOperator($this->subject), true, 'OR');
    $criteria->compare('email', CommonFunctions::escapeOperator($this->email), true, 'OR');
    $criteria->compare('status', CommonFunctions::escapeOperator($this->status), true,'OR');

    $lastactivity_sql = '(select max(date) from tbl_contact_action ca where ca.contact_id=t.contact_id)';
    $criteria->select = array('*', $lastactivity_sql . ' as lastactivity');
    $criteria->addCondition('status<>"Deleted"');
    $criteria->compare($lastactivity_sql, $this->lastactivity);
    $sort = new CSort;
    $sort->defaultOrder = array('lastactivity' => CSort::SORT_DESC); //'title ASC';
    $sort->attributes = array(
        'name' => 'name',
        'email' => 'email',
        'status' => 'status',
        'createdon' => 'createdon',
        'lastactivity' => array(
            'asc' => 'lastactivity ASC',
            'desc' => 'lastactivity DESC',
        ),
    );
    $sort->applyOrder($criteria);
    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'sort' => $sort,
        'pagination' => array(
            'pageSize' => Yii::app()->user->getState('contactPageSize', Yii::app()->params['RECORDPERPAGE_ADMIN']),
            'currentPage' => Yii::app()->user->getState('Contact_page', 0),
        ),
    ));

}

}

Answer 2

Just a theory: because you have a join with together sql, all the data will be together in 1 result. I am not sure if you can still use $data->ca->date because your data is not a known active record type.

Try putting

$criteria->select = 'maintable.*,ca.date as ca_date'; 

then you should be able to use

       array(
          'header' => 'Last Activity',
          'class' => 'gridDataColumn',
          'value' => '$data->ca_date',                           
       ),