CGridView filter & sort on STAT relation

i try to sort and filter on a STAT relation inside the CGridView. But i’m stuck with this:

[font="Courier New"]CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘total.total’ in ‘order clause’[/font]

model:




    public $total;


    public function relations() {

        return array(

            'rDaytrips' => array(self::MANY_MANY, 'Daytrip', 'daytrip_tag(tag_id, daytrip_id)'),

            'tagCount' => array(self::STAT, 'Daytrip', 'daytrip_tag(tag_id, daytrip_id)'),

        );

    }


    public function search() {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria = new CDbCriteria;


        $criteria->compare('id_tag', $this->id_tag);

        $criteria->compare('tag', $this->tag, true);

        $criteria->compare('tag', $this->total);


        $criteria->with = array('tagCount');


        return new CActiveDataProvider(get_class($this), array(

            'criteria' => $criteria,

            'pagination' => array(

                'pageSize' => Yii::app()->params['itemsPerPage'],

            ),

            'sort' => array(

                'defaultOrder' => 'tag',

                'attributes' => array(

                    '*',

                    'total' => array(

                        'asc' => 'total.total',

                        'desc' => 'total.total DESC',

                    )

                )

            ),

        ));

    }



view:




<?php

    $this->widget('zii.widgets.grid.CGridView', array(

        'id' => 'tag-grid',

        'dataProvider' => $model->search(),

        'filter' => $model,

        'columns' => array(

            'id_tag',

            'tag',

            array(

                'name' => 'total',

                'value' => '$data->tagCount',

            ),

            array(

                'class' => 'CButtonColumn',

                'template' => '{update} {delete}',

            ),

        ),

    ));

?>



I have a similar problem and would love to know about it! :)

maybe if you post your code someone can help you…

in the above code… the error is clear… it says that it does not know for a column total.total…

My bad!




public $last_contacted;


public function relations()

{

  return array(

    '_last_contacted' => array(self::STAT, 'Contact', 'client_id', 'select'=>'MAX(contact_date)'),

  );

}


public function search()

{

  $criteria=new CDbCriteria(array('with'=>'_last_contacted'));

  $criteria->compare('status',$this->status);

  $criteria->compare('name',$this->name,true);


  // What should I add here to enable it to search by last contacted date?

  // $criteria->compare('_last_contacted.<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />',$this->last_contacted);

   

  return new CActiveDataProvider(get_class($this), array(

    'criteria'=>$criteria,

  ));

}



And my CGridView would have something like that:




$this->widget('zii.widgets.grid.CGridView', array(

  'id'=>'cliente-grid',

    'dataProvider'=>$model->search(),

    'filter'=>$model,

    'columns'=>array(

      array(

        'name'=>'status',

        'value'=>'Lookup::item("status_client",$data->status)',

        'filter'=>Lookup::items('status_client'),

      ),

      array(

        'name'=>'name',

        'type'=>'raw',

        'value'=>'CHtml::link($data->name, $data->url)',

      ),

      array(

	'name'=>'last_contacted',

	'type'=>'raw',

	'value'=>'$data->_last_contacted ? Yii::app()->format->dateTime($data->_last_contacted, false, true) : null',

      ),

      array(

	'class'=>'CButtonColumn'

      ),

  ),

));



Bump! I have the same problem. Basically I’m not sure how to reference the STAT field in the conditional clause so obviously can’t filter on it without getting an “unknown colunn” error. In my CGridView definition I reference the STAT field by the CActiveRecord relation name. However, this doesn’t work when referencing the STAT field in the addSearchCondition() method. Any thoughts? Thanks!

Ah, so after a bit more digging, it appears that STAT fields are generated by a separate query. Unless I’m missing something (which I really hope!), I don’t think that there is a way to sort or filter when using a STAT relation. The way to solve the problem would be to write the query yourself (using a CDbCriteria object or something) and then sort on the alias you give to the STAT field. In my case, this is a huge PITA since I’m using the STAT relation in quite a few places. If anyone has a better way I’d love to hear it!

BUMP. Anybody got a solution for this?

I have the same problem. Could anybody find a solution?

For Reference: Sorting CListView in yii

hxxp://stackoverflow.com/questions/5492977/sorting-clistview-in-yii

Thanks so much! :rolleyes:

But, how to display the filter?