Yii：排序和格式化动态列 [英] Yii: Sorting and formatting dynamic columns

问题描述

我使用CSqlDataProvider从动态SQL查询中显示CGridView中的数据。有一些静态和一些动态列。现在我想在动态列中进行一些特殊的格式设置，如货币。但我怎么做，当我不知道列的数量/名称，直到执行查询。

另外我想能够排序动态列和我有同样的问题，我没有所有的列名称。

以前任何人与动态查询和gridview的工作。可以请点我指向正确的方向或给出一些想法如何做到这一点。

总之，我能够成功地显示数据在gridview（也是动态行）和对所有静态列进行排序。只需要对动态行进行排序并格式化动态&静态列

GridView代码：

  $ tdata = $ dataProvider - >的getData（）; 
 //计算获取列名
 $ grid_columns = array_keys（$ tdata [0]）; 
 
 foreach（$ grid_columns as $ i => $ ii）
 {
 //应用公式获得总行
 $ grid_final [$ i] = array （ '姓名'=> $ II， '类'=> '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'=>'镶边镶边'，
'template'=>{items} \\\
 {extendedSummary}，
'columns'=> $ grid_final ，
））; 
  

控制器代码：

  public function actionIndex（）
 {
 
 if（isset（$ _ GET ['month']））
 {
 
 $月= $ _ GET [ '月']; 
} 
 else 
 {
 $ month = 7; 
} 
 //使用动态列的SQL查询
 $ sql =SELECt ABC，X，Y，Z，@Column_Names 
 FROM some_table 
 WHERE [月] = ：月; 
 
 $ 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（
 //这里我怎么把列名我不知道如何排序
'attributes'=> array（
' ABC'，'X'，'Y'，'Z'）），'pagination'=> false））; 
 $ this-> render（'index'，array（'dataProvider'=> $ dataProvider，'month'=> $ month））; 
 
  

解决方案

如果您能够获得网格之前的列被渲染，您还可以更改数据提供者的排序条件。
类似于这样：

  $ tdata = $ dataProvider-> getData（）; 
 //计算获取列名
 $ grid_columns = array_keys（$ tdata [0]）; 
 
 $ dataProvider-> setSort（array（'attributes'=> $ grid_columns））; 
  

或者，你当然可以准备好自己的一系列属性，包括特定的设置，或根据任何特定的格式你有逻辑。问题是 - 一旦你在 $ grid_columns 中有列，你可以根据需要改变dataProvider排序或gridColumn设置。

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));
}

解决方案

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.

这篇关于Yii：排序和格式化动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！