Yii: sort and format dynamic columns

Question

I am showing data in a CGridView from a dynamic SQL query using CSqlDataProvider. There is some static and some dynamic column. Now I want to make special formatting, such as currency in dynamic columns. But how to do this when I do not know the number / name of the columns before executing the query.

I also want to be able to sort dynamic columns, and again I have the same problem that I do not have all the column names.

Anyone who has worked with dynamic queries and gridview. Could you please point me in the right direction or give some ideas on how to do this.

In short, I can successfully show the data in gridview (also dynamic rows) and sort all the static columns. You just need to sort the dynamic rows and format the dynamic and static columns

Code for GridView:

$tdata=$dataProvider->getData(); //Calculation to get column names $grid_columns = array_keys($tdata[0]); foreach($grid_columns as $i=>$ii) { //Applying Formula to get Total Row $grid_final[$i] = array('name'=>$ii,'class'=>'bootstrap.widgets.TbTotalSumColumn'); } //Grid View $this->widget('bootstrap.widgets.TbExtendedGridView', array( 'sortableRows'=>true, 'afterSortableUpdate' => 'js:function(id, position){ console.log("id: "+id+", position:"+position);}', 'dataProvider'=>$dataProvider, 'type'=>'striped bordered', 'template' => "{items}\n{extendedSummary}", 'columns'=> $grid_final, )); 

Controller Code:

 public function actionIndex() { if(isset($_GET['month'])) { $month=$_GET['month']; } else { $month= 7; } //SQL Query with Dynamic Columns $sql = "SELECt ABC,X,Y,Z, @Column_Names FROM some_table WHERE [month] = :month"; $connection=Yii::app()->db; $command=$connection->createCommand($sql); $command->bindParam(':month',$month,PDO::PARAM_STR); $dataProvider=new CSqlDataProvider($sql,array('keyField' => 'ABC','params' => array( ':month' => $month, ),'sort' => array( //Here how do i put column names which i don't know yet for sorting 'attributes' => array( 'ABC','X','Y','Z' )),'pagination'=>false)); $this->render('index',array('dataProvider' => $dataProvider, 'month' => $month)); } 

Answer 1

I create dynamic columns in Yii as follows:

• In the some_table model, let's call it SomeTable, I declare the maximum number of column names as follows:

  public $ column1, $ column2, $ column3, $ column4;

• I am creating a function in this model, I will call it "search ()", which builds the dataProvider, like your logical states, but I'm sure @Column_Names looks something like this:

  var_column1 as column1, var_column2 as column2, etc.

• in validation (), declare all of these columns as safe in 'search'
• build an array of columns formed from merging the attributes of model-> and all declared columns with their parameters, and assign it to the look, just like you do with the $ grid_final variable

The only drawback here is that you need to know the maximum number of columns and, of course, the big problem is declaring the allocation of variables if you have tables with selected columns.

Answer 2

If you can get the columns before rendering the grid, you can also change the sorting conditions for data-data. Something like that:

 $tdata=$dataProvider->getData(); //Calculation to get column names $grid_columns = array_keys($tdata[0]); $dataProvider->setSort(array('attributes'=> $grid_columns)); 

Or you can, of course, prepare your own array of attributes with specific settings or specific formatting in accordance with any logic you have. The thing is, after you have the columns in $grid_columns , you can change the sorting of the dataProvider or the gridColumn parameter as needed.