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'.

score 1 · Answer 1 · answered Apr 02 '14 at 05:59

1

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',                           
       ),

answered Apr 02 '14 at 05:59

Mihai P.

9,307
3
38
49

I have edited my question with a possible change in criteria which resulted in correct display but then sorting is not working. – Mihir Patel Apr 02 '14 at 06:13
I never used CSort sorry. What happens if you do not use csort and if you just leave the criteria for the CActiveDataProvider? – Mihai P. Apr 03 '14 at 00:26
Please go through my code for suggestions to help me resolve this issue...thank you....http://pastebin.com/q7D2k7XG – Mihir Patel Apr 03 '14 at 07:22
Ok, your error is that t.lastactivity does not exist, and that is correct. t is an alias for the current table, t.lastactivity does not exist as it is a value you calculate. Did you try to not use CSort? is that working? – Mihai P. Apr 03 '14 at 12:11
instead of using 'lastactivity'=>'lastactivity' in sort attributes using the array as in my answer worked for me. Thanks for your time Mihai P. :) – Mihir Patel Apr 08 '14 at 06:53
1

well, guess that should teach me to answer things where I do not even get a up for trying to give you an answer especially since I pointed you in the exact direction you needed. – Mihai P. Apr 08 '14 at 14:41

score 0 · Accepted Answer · answered Apr 08 '14 at 06:47

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),
        ),
    ));

}

}

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

2 Answers2