When we use crud command to generate MVC, we get grid table with sort function in header column.
But in controller, there is no example code to define this sorting columns. So we’ll got error when try to sort.
How to solve this problem?
Thanks…
[size="1"]sorry for bad english[/size]
We used to have sorting work in the old Yii 1.0… but we haven’t tried fixing our sorting for 1.1 yet. Though this might help get you started. You need to define sort fields and then add them to your dataProvider:
$sort = new CSort('Person');
$sort->attributes=(array(
'Name'=>array(
'asc'=>'LastName, FirstName, MiddleName',
'desc'=>'LastName DESC, FirstName DESC, MiddleName DESC',
'label'=>'Name'
),
'Sex',
));
$dataProvider=new CActiveDataProvider('Person', array(
'pagination'=>array(
'pageSize'=>self::PAGE_SIZE,
),
'sort'=>$sort,
));
This sorting will work on the first page… but if you have paging implemented then you’ll find that going to the next page won’t keep the sort. In the old Yii we had to add the sort to the paging parameters like so:
// Where $pages is a CPagination object and sort is the sort parameters currently in effect
if (isset($_GET['sort'])) $pages->params["sort"]=$_GET['sort'];
You’ll have to do something similar in Yii 1.1 where you pass the sort parameters along with the paging parameters when going to another page.
Thanks you lgoss007 
I try like this:
$sort = new CSort('content');
$sort->attributes=(array(
'id'=>array(
'asc'=>'id',
'desc'=>'id DESC',
'label'=>'id',
),
));
$dataProvider=new CActiveDataProvider('content', array(
'criteria'=>array(
'with'=>array('categories','sections'),
),
'pagination'=>array(
'pageSize'=>self::PAGE_SIZE,
),
'sort'=>$sort,
));
In view:
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
'enableSorting'=>true,
'columns'=>array(
array(
'header'=>'Id',
'name'=>'id',
'htmlOptions'=>array('style'=>'text-align:right;'),
'sortable'=>true,
),
'title',
'sections.name',
'categories.name',
array(
'name' => 'published',
'value' => '$data->published == 0 ? "Draft" : "Publish"',
'sortable'=>true,
),
array(
'class'=>'CButtonColumn',
),
),
)); ?>
The error show like this:
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>
CDbException</title>
<style type="text/css">
/*<![CDATA[*/
body {font-family:"Verdana";font-weight:normal;color:black;background-color:white;}
h1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }
h2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }
h3 {font-family:"Verdana";font-weight:bold;font-size:11pt}
p {font-family:"Verdana";font-size:9pt;}
pre {font-family:"Lucida Console";font-size:10pt;}
.version {color: gray;font-size:8pt;border-top:1px solid #aaaaaa;}
.message {color: maroon;}
.source {font-family:"Lucida Console";font-weight:normal;background-color:#ffffee;}
.error {background-color: #ffeeee;}
/*]]>*/
</style>
</head>
<body>
<h1>CDbException</h1>
<h3>Description</h3>
<p class="message">
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous</p>
<h3>Source File</h3>
<p>
C:\www\yii111\framework\db\CDbCommand.php(375)</p>
<div class="source">
<pre>
00363: }
00364:
00365: if($this->_connection->enableProfiling)
00366: Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
00367:
00368: return $result;
00369: }
00370: catch(Exception $e)
00371: {
00372: if($this->_connection->enableProfiling)
00373: Yii::endProfile('system.db.CDbCommand.query('.$this->getText().')','system.db.CDbCommand.query');
00374: Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,'system.db.CDbCommand');
<div class="error">00375: throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
</div>00376: array('{error}'=>$e->getMessage())));
00377: }
00378: }
00379: }
</pre>
</div><!-- end of source -->
<h3>Stack Trace</h3>
<div class="callstack">
<pre>
#0 C:\www\yii111\framework\db\CDbCommand.php(265): CDbCommand->queryInternal('fetchAll', 2, Array)
#1 C:\www\yii111\framework\db\ar\CActiveFinder.php(739): CDbCommand->queryAll()
#2 C:\www\yii111\framework\db\ar\CActiveFinder.php(413): CJoinElement->runQuery(Object(CJoinQuery))
#3 C:\www\yii111\framework\db\ar\CActiveFinder.php(85): CJoinElement->find(Object(CDbCriteria))
#4 C:\www\yii111\framework\db\ar\CActiveRecord.php(1154): CActiveFinder->query(Object(CDbCriteria), true)
#5 C:\www\yii111\framework\db\ar\CActiveRecord.php(1220): CActiveRecord->query(Object(CDbCriteria), true)
#6 C:\www\yii111\framework\web\CActiveDataProvider.php(103): CActiveRecord->findAll(Object(CDbCriteria))
#7 C:\www\yii111\framework\web\CDataProvider.php(122): CActiveDataProvider->fetchData()
#8 C:\www\yii111\framework\zii\widgets\CBaseListView.php(99): CDataProvider->getData()
#9 C:\www\yii111\framework\zii\widgets\grid\CGridView.php(220): CBaseListView->init()
#10 C:\www\yii111\framework\web\CBaseController.php(148): CGridView->init()
#11 C:\www\yii111\framework\web\CBaseController.php(160): CBaseController->createWidget('zii.widgets.gri...', Array)
#12 C:\www\back\protected\views\content\admin.php(98): CBaseController->widget('zii.widgets.gri...', Array)
#13 C:\www\yii111\framework\web\CBaseController.php(119): require('C:\www\back\pro...')
#14 C:\www\yii111\framework\web\CBaseController.php(88): CBaseController->renderInternal('C:\www\back\pro...', Array, true)
#15 C:\www\yii111\framework\web\CController.php(742): CBaseController->renderFile('C:\www\back\pro...', Array, true)
#16 C:\www\yii111\framework\web\CController.php(681): CController->renderPartial('admin', Array, true)
#17 C:\www\back\protected\controllers\ContentController.php(212): CController->render('admin', Array)
#18 C:\www\yii111\framework\web\actions\CInlineAction.php(32): ContentController->actionAdmin()
#19 C:\www\yii111\framework\web\CController.php(300): CInlineAction->run()
#20 C:\www\yii111\framework\web\filters\CFilterChain.php(129): CController->runAction(Object(CInlineAction))
#21 C:\www\yii111\framework\web\filters\CFilter.php(41): CFilterChain->run()
#22 C:\www\yii111\framework\web\CController.php(993): CFilter->filter(Object(CFilterChain))
#23 C:\www\yii111\framework\web\filters\CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))
#24 C:\www\yii111\framework\web\filters\CFilterChain.php(126): CInlineFilter->filter(Object(CFilterChain))
#25 C:\www\yii111\framework\web\CController.php(283): CFilterChain->run()
#26 C:\www\yii111\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)
#27 C:\www\yii111\framework\web\CWebApplication.php(320): CController->run('admin')
#28 C:\www\yii111\framework\web\CWebApplication.php(120): CWebApplication->runController('content/admin')
#29 C:\www\yii111\framework\base\CApplication.php(135): CWebApplication->processRequest()
#30 C:\www\back\index.php(12): CApplication->run()
#31 {main}</pre>
</div><!-- end of callstack -->
<div class="version">
2010-03-16 21:18:04 Apache/2.2.11 (Win32) PHP/5.2.9 <a href="http://www.yiiframework.com/">Yii Framework</a>/1.1.1</div>
</body>
</html>
This is telling you that there is an error in the SQL statement (ambiguous ‘id’) meaning that you need to qualify it. Since you’re joining with 2 other tables it’s telling you it can’t figure out what ‘id’ you are talking about. I think you can do this in your sort:
$sort = new CSort('content');
$sort->attributes=(array(
'id'=>array(
'asc'=>'t.id',
'desc'=>'t.id DESC',
'label'=>'id',
),
));
Haven’t tested it but I think that’s it… I think yii names the main table in the query as t… you just need to make sure that in the asc and desc query they use that table prefix (whatever it is).
Hei, it’s work! Thanks you a lot lgoss007
hi,
Can you also explain which file to edit ?
OR
Which file I should add this code ?
protected/controller/TableNameController.php
or
protected/models/TableName.php
or
protected/views/TableName/_form.php
protected/views/TableName/_search.php
protected/views/TableName/_view.php
protected/views/TableName/admin.php
protected/views/TableName/create.php
protected/views/TableName/index.php
protected/views/TableName/update.php
protected/views/TableName/view.php