Additional columns for CGridView with data provider

Hello everyone. I’m using a CGridView with a data provider (CArrayDataProvider) and it’s working fine. However, now I’d like to show a couple of additional related columns in the table (CGridView). Due to the complexity of the SQL instruction that populates the data provider initially, I’m afraid I will have to use another SQL instruction to get the data I need for the additional columns.

To make a simple example, let’s say I’m displaying a table with a list of users, and now I’d like to show an additional value for each of them.

Now:

Users

Id | Name | Address

What I’d like to show:

Users

Id | Name | Address | Earnings | Current level

What do you guys think would be the best approach to do this? Please remember that I need to use another SQL instruction, in other words, I would have a second data provider but, as far as I know, the CGridView would only accept one as a parameter.

I guess this wouldn’t be a problem at all if I were to construct the “html table” by myself using a foreach statement, because on each iteration I could just grab the additional data from the second data provider variable, like this:


foreach row in dataProvider1

   dataProvider2 = get data needed using dataProvider1 data as a parameter for the SQL query here. (Example: the earnings for each user)

end foreach



but using the CGridView, I have to specify the fields to show in a "non-foreach" style.

I hope that I could explain myself correctly. I will appreciate any ideas to solve this issue. I will be glad to clarify anything in this post.

Thank you in advance!

You probably want to look into using with in the original query to get the array you pass in to your CArrayDataProvider. Something like


Users::model()->with('earnings', 'currentLevel')->findAll($criteria);

Thank you for your answer Joshua. I will look into that and I will post the results later.

Take a look at the wiki article CGridView: Render customized/complex datacolumns.

There is explained how to use extra sql queries for additional columns in the cgridview.

Hello Joblo, thank you very much for your answer. It has been very helpful.

I was able to succesfully display the additional column that I wanted following your wiki article. However, a second additional column that I’d like to display, depends on the value of the first additional column. How can I use it? This is what I did:


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

    'dataProvider'=>$dataProvider,

    'columns'=>array(

        ...

        array(            

            'name'=>'column0',

            // this is a simple column defined by the data contained in $dataProvider

        ),

        array(            

            'name'=>'column1',

            // call the method 'calculate1' from the model. It's defined and working fine.

            'value'=>array($model, 'calculate1'), 

        ),

        array(            

            'name'=>'column2',

            // call the method 'calculate2' from the controller

            'value'=>array($this, 'calculate2'), 

        ),

    ),

));

The problem is that, in the controller, I can’t do this:


class MyController extends Controller 

{

     //called on rendering the column for each row 

     protected function calculate2($data, $row)

     {

         return $data['column0'] - $data['column1']; // Just a simple substraction

     }       


}

$data[‘column0’] is accesed fine, but $data[‘column1’] is not, because this column is not defined in the $dataProvider provided to the CGridView. How could I achieve what I want? Is there any way to get the value from the “calculated” additional column?

Thanks in advance to anyone who can help.

Edit:

Also, would anyone please tell me how can I pass a parameter in the function call? I’d like to have an aditional parameter, other than $data and $row, here:


        array(            

            'name'=>'column1',

            'value'=>array($model, 'calculate1', $customParameter), // How can I send this $customParameter to the "calculate1" function in the model?

        ),

I also tried the following, with no luck:


        array(            

            'name'=>'column1',

            'value'=>array($model, 'calculate1($customParameter)'),

        ),


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

    'dataProvider'=>$dataProvider,

    'columns'=>array(

        ...

        array(            

            'name'=>'column0',

            // this is a simple column defined by the data contained in $dataProvider

        ),

        array(            

            'name'=>'column1',

            // call the method 'calculate1' from the model. It's defined and working fine.

            'value'=>array($model, 'calculate1'), 

        ),

        array(            

            'name'=>'column2',

            // call the method 'calculate2' from the controller

            //'value'=>array($this, 'calculate2'), #instead of this use the following line:

            'value' => '$data->column0 - $data->column1', //or this would also work if you want to use the method in the controller:

            // 'value' => '$this->calculate2($data->column0, $data->column1)'

        ),

    ),

));

Hi Gesti, thank you for your reply, but it didn’t work for me. Did it work for you?

‘$data->column0’ doesn’t return any value. Only ‘$data[“column0”]’ does, but the value from “column1” won’t appear either way.

Any additional ideas?

just working in a CGridView myself as well and in one of the columns I have this:


		array(

			'header' => 'No. of Events',

			'value' => 'count($data->gasparListEvents)',  //and it works for me. It's important to put the ''s out

		),

if it still doesn’t work can you tell me if there is any error message?

are the columns in your database actually called column0, column1, column2?

CArrayDataProvider will return an array of arrays instead of an array of objects.

/Tommy

Thank you Gesti and tri for replying.

Gesti, what tri said must be the reason why I have to use the notation ‘$data[“column0”]’ instead of ‘$data->column0’ . I’m using a CArrayDataProvider as athe CGridView data provider and it’s probable that you’re using a different type of data provider. However, this is not the problem. The problem is that I’d like to access the value of a calculated column. This is, the value that I want was calculated in the CGridView column and it is not in the data provider. In my example, ‘column0’ is the actual name of the column in the database, but ‘column1’ is just the name of the calculated column. I’d like to know how to get the calculated column values.

Thank you.

my bad, didn’t fully know what’s going on. :)

how about this:


class MyController extends Controller {

     public $column_1;


     protected function calculate1($data, $row)

     {

         //your calculations

         $this->column_1 = /* what you calculated */;

     }       

     //called on rendering the column for each row 

     protected function calculate2($data, $row)

     {

         return $data['column0'] - $this->column_1; // Just a simple substraction

     }       


}

don’t laugh if it’s silly :)