[behdad]

Hello,

I have defined a virtual attribute in my Model which is combination of two other attributes (something like FullName = FirstName . LastName).. now, the virtual attribute column in my Grid isn't sortable (the title is a text rather than a Hyperlink).

I think, I cannot find the relation between CSort and a Model.

Thanks.

[Haensel]

I think the problem is that CSort is used with CActiveRecord which itself only returns attributes defined in the DATABASE when calling $model->getAttributes(), but no virtual attributes (defined in the model class as variabels).
But have you tried to Sort by a combination of two attributes that actually exist in the database rather then trying to Sort by the virtual attribute?

Something like this maybe?

'user'=>array(     
'asc'=>'first_name, last_name',
'desc'=>'first_name DESC, last_name DESC',
'label'=>'fullname' )

[behdad]

Thanks Haensel,

Yes it did work, I just didn't know where these sort attributes should be defined, which was under my DataProvider.

[MtlMike]

Hi,

Solved, for me anyway....

Note: I am not an experienced PHP person and I am a newbie to yii as well so please forgive my lack of efficient or proper coding techniques in both.

Step 1) Add Virtual Attribute for model attributes in same model - I will get to related model sortable later when I have a chance and if it is possible.

In my model:

private $_full_name = NULL;

public function getfull_name()
{
if(!isset($this->_full_name))
{
if (!isset($this->first_name) && !isset($this->last_name) && !isset($this->salutation))
$this->_full_name = NULL;
else
{
if(isset($this->last_name))
{
$this->_full_name = $this->last_name;
}
if (isset($this->first_name) || isset($this->last_name) )
{
$this->_full_name .= ', ';
}
if(isset($this->first_name))
{
$this->_full_name .= $this->first_name;
}
if(isset($this->salutation))
{
$this->_full_name .= ' ' . $this->salutation;
}
}

}
return $this->_full_name;
}

Step 2) In my model rules

public function rules()
{
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
return array(
array('full_name', 'safe', 'on'=>'edit, post, search'),


Step 3) In my model attributes

public function attributeLabels()
{
return array(
'full_name'=>'Full Name',

Step 4) In my model search function

... other criteria
...
$criteria->compare('first_name',$this->_full_name, true, 'OR');
$criteria->compare('last_name',$this->_full_name, true, 'OR');
$criteria->compare('salutation',$this->_full_name, true, 'OR');

$sort = new CSort();
$sort->attributes = array(
'full_name'=>array(
'asc'=>'last_name ASC, first_name ASC, salutation ASC',
'desc'=>'last_name DESC, first_name DESC, salutation DESC',
),
'id', ... other fields....,
);

return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria, 'sort'=>$sort,
));


Step 5) In my view/admin.php

'columns'=>array(
'id',
'full_name',
'...'
'other columns',

Step 6) In my view/_search.php

I removed the first_name, last_name, and salutation entries as they are not needed

<div class="row">
<?php echo $form->label($model,'first_name'); ?>
<?php echo $form->textField($model,'first_name',array('size'=>60,'maxlength'=>100)); ?>
</div>-->


Summary:

These are the only changes I made I think - if I notice something else I will add to this.

Again, I am not that experienced (a couple weeks) so please take this with a grain of salt. Hope it helps.


...

A Newbie so sorry if this is not ok...

I am having a problem figuring out how to accomplish the sorting of the virtual attribute.

Currently I have the virtual Attribute working as well with correct filtering; however, the CGridView has no link on the header for sorting.

I have been searching but no definitive answer so far. If you could pass on the how to I would be grateful.

note: I tried placing the noted 'user'=>array(..), portion into the attributes but no dice...

Thanks in advance.

[Mindphyre]

Quote

Yes it did work, I just didn't know where these sort attributes should be defined, which was under my DataProvider.

Would you mind giving an example of how you put the in DataProvider?

Thanks
Oliver

[Mindphyre]

No reply?


Oliver

[softark]

Hi Oliver,

I think MtlMike's post is a good example. Where do you get stuck?

[MtlMike]

Mindphyre, on 12 June 2012 - 04:23 PM, said:

No reply?


Oliver

Hello there,

Sorry but I have been out of the Yii loop for a while now and I was just searching for something and by chance noticed this.. I know its late but I will be able to look at this in the upcoming weekend if you can wait for a couple days more; I need a bit of time to review but if my memory serves me correctly I made this post very carefully and thought I included everything. Of course I must have missed something and will definitely follow up.

Again, sorry for the lack of responce, nothing like being left in the wind.

MtlMike.


Following up...

Again, its been a while but I think this answers your question:

The private variable of the model below stores the string concatenation which is constructed by the public function which removes spaces or can be any formatting/arrangement for sorting. So your model, not only represents the table, but has this virtual property/column as well.

This search method in the model located near the bottom, which is called by the controller, returns a CActiveDataProvider containing a collection of objects, and which is then passed to the view. Hence MVC Pattern .

The view gets the CActiveDataProvider with this new property and which can now be included in a grid and sorted via the Sort attribute of the CActiveDataProvider (see below in bold).

private $_full_name = NULL;
public function getfull_name() { // how ever you want to manip table fields }


[b] I use a string and CSqlDataProvider In the search method here is the entire search method [/b]

  public function search()
        {

            // calls a method to make temp table list : myClientList_1 and 2
             $this->prepClientList();
            
            // using first index temp table for count - myClientList_1
            $sql = "";
            $sqlTotal = "select count(*) from myClientList_2 where 1=1 ";
            $sqlSelect = "select  id, company_name, last_name, first_name, salutation, full_name,
                                title, phone_1, phone_2, fax, email, address, address_2, 
                                city_town, region, country, postal_code,
                                canvasser_year, status_code_id, status_description, extinct 
                    from myClientList_2 where 1=1 ";
            
            If (isset($_GET['Client']) || isset(Yii::app()->session['GetClient']))
            {                
                $sql .= ($this->id > 0 ? " AND id = ".$this->id : "");
                $sql .= (strlen($this->company_name) > 0 ? " AND company_name LIKE '%{$this->company_name}%'" : "");
                $sql .= (strlen($this->title) > 0 ? " AND title LIKE %{$this->title}%'" : "");
                $sql .= (strlen($this->phone_1) > 0 ? " AND phone_1 LIKE '%{$this->phone_1}%'" : "");
                $sql .= (strlen($this->phone_2) > 0 ? " AND phone_2 LIKE '%{$this->phone_2}%'" : "");
                $sql .= (strlen($this->fax) > 0 ? " AND fax LIKE '%{$this->phone_2}%'" : "");
                $sql .= (strlen($this->email) > 0 ? " AND email LIKE '%{$this->email}%'" : "");
                $sql .= (strlen($this->address) > 0 ? " AND address LIKE '%{$this->address}%'" : "");
                $sql .= (strlen($this->address_2) > 0 ? " AND address_2 LIKE '%{$this->address_2}%'" : "");
                $sql .= (strlen($this->city_town) > 0 ? " AND city_town LIKE '%{$this->city_town}%'" : "");
                $sql .= (strlen($this->region) > 0 ? " AND region LIKE '%{$this->region}%'" : '');
                [b]
                if (strlen($this->full_name)> 0)
                {
                    $sql .= " AND (salutation LIKE '%{$this->full_name}%'";
                    $sql .= " OR last_name LIKE '%{$this->full_name}%'";
                    $sql .= " OR first_name LIKE '%{$this->full_name}%')";
                }
                [/b]
               
            }
            
            // have to get total count using the where condition
            $count=Yii::app()->db->createCommand($sqlTotal . $sql)->queryScalar();
            
            $sortColumn = 'company_name, last_name, first_name, salutation';
            $Sort = new CSort();
            //$sort->modelClass = 'Client';
            $sort->defaultOrder = $sortColumn;
            $sort->attributes=array('id', 'full_name','company_name', 'title', 'phone_1', 'canvasser_year', 
                        'status_code_id', 'extinct',
            );
          
            
           $clientArray = array(
                    'totalItemCount'=>$count,
                    'pagination'=>array('pageSize'=>250),
                    // 'pagination'=>false,
                    'sort'=>$sort,
           );
           
           $dataProvider= new CSqlDataProvider($sqlSelect . $sql, 
                    $clientArray);
            
           $dataProvider->getData();
           return $dataProvider;
                    
        }
      

And here is the grid widget using this data

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'client-grid',
	'dataProvider'=>$model->search(),
        'filter'=>$model,
	'columns'=>array(
		array('name'=>'id',
                     'visible'=>false,
                    'htmlOptions'=>array('class'=>'client_grid_col0'),
                ),
                array('name'=>'company_name',
                     'header'=>'Company Name',
                    'htmlOptions'=>array('class'=>'client_grid_col1'),
                ),
                array('name'=>'full_name',
                     'header'=>'Name',
                    'htmlOptions'=>array('class'=>'client_grid_col2'),
                ),
		
                ...

                array(
                        'class'=>'CButtonColumn',
                            'template'=>'{update}{delete}',
                            //'viewButtonUrl'=>'Yii::app()->createUrl("/client/view", array("id" => $data["id"]))',
                            'deleteButtonUrl'=>'Yii::app()->createUrl("/client/delete", array("id" =>  $data["id"]))',
                            'updateButtonUrl'=>'Yii::app()->createUrl("/client/update", array("id" =>  $data["id"]))',
                ),                
	),
    
));             

Hope this gives you some ideas and helps.

MtlMike.