Yii: Sorting and formatting dynamic columns

Question

I am showing data in CGridView from a dynamic SQL Query using CSqlDataProvider. There are some static and some dynamic column. Now I want to do some special formatting like currency in the dynamic columns. But how do I do that when I don't know the number/name of the columns till the query is executed.

Also i want to be able to sort the dynamic columns and again I have the same problem that I don't have all the column names.

Anyone before who worked with dynamic queries and gridview. Could please point me to the right direction or give some ideas how to do it.

In short I am able to successfully show the data in gridview(also dynamic rows) and sort all the static columns. Just need to sort dynamic rows and format dynamic & 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));
}

Ordinate

Could you please post some more code? – Gladysglagolitic

@user3265427 Controller code added.. Also view code extended.. I have shown calculation for variable $grid_final for columns in grid – Ordinate

@user3265427 you need some more information? – Ordinate

Sorry, i think i didn't found a solution. i thought there was an alias to access the columns by number, e.g: c1, c2, c3 but could not find this again. – Gladysglagolitic

is it possible to query a table and just get column names.. so i run this query before to get column names.. and then execute the main query.. – Ordinate

That should be possibel. After $columnList=$command->queryRow() you can check the result and get the column Names by the Key Names in your Array. eg. array_keys($columnList[0]). Hope this helps! – Gladysglagolitic

Answer 1

I create dynamic columns in Yii like this:

1. In some_table model, let's name it SomeTable, I declare a maximum number of column names like this:

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

2. I create a function in that model, let's name it 'search()' that builds the dataProvider, like your logic states, but I make sure that @Column_Names looks like something like this:

   var_column1 as column1, var_column2 as column2, etc.

3. in validation(), declare all those columns as safe on 'search'
4. construct a columns array formed from merging model->attributes and all declared columns with their options and assign it to the view, exactly like you do with $grid_final variable

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

Raceway

i dont know the maximum number of columns but i do know the name of the columns next to the dynamic columns if it helps.. I means its like known_columns, unknown_dynamic_columns, known_columns – Ordinate

Answer 2

If you are able to get the columns before the grid is rendered, you can also alter the sorting conditions of the dataprovider. Something like this:

$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 you own array of attributes with specific settings, or specific formatting according to any logic you have. The thing is - once you have the columns in $grid_columns you can alter the dataProvider sorting, or the gridColumn setting as you need.

Hissing