Hello,
I have a problem for implimentation cgridview.
I use pdo mssql driver to connect with sql server (sql server 2008r2). I had tried to implement cgridview with relation table function on mysql it’s work.
But I use the code on mssql just edit part of connection with database the data not update when change page on pagination.
but the sort column does available but it error if click another page number on pagination after click sort column.
many thanks.
I think this is the same bug as http://code.google.com/p/yii/issues/detail?id=997 ,because I try it with mysql with no error.
Thanks you very much.
I use this code from link https://code.google.com/p/yii/source/browse/trunk/framework/db/schema/mssql/CMssqlCommandBuilder.php?spec=svn2821&r=2821
and replace function applyLimit
public function applyLimit($sql, $limit, $offset)
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
{
$sql = preg_replace(
'/^SELECT\s+(DISTINCT\s)?/i',
'SELECT $1TOP ' . ($limit+$offset) . ' ',
$sql
);
$orderBy = stristr($sql, 'ORDER BY');
$over = substr($orderBy, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />;
$newSql = substr($sql, 0, strripos($sql, 'ORDER BY'));
//echo $newSql;
$from = strrpos($sql, 'FROM');
//$first=substr($newSql,0);
$newSql = substr($newSql, 0, $from) . " , ROW_NUMBER() OVER (ORDER BY $over) as yiinumber " . substr($newSql, $from);
//echo $newSql = substr_replace($newSql," , ROW_NUMBER() OVER (ORDER BY $over) as yiinumber ",substr($newSql, 0, $from),0);
$sql = "SELECT * FROM (
$newSql ) AS __innertable__ WHERE yiinumber > {$offset}";
//echo $qsql;
}
return $sql;
}
it work when click sort column first but it’s not work when click pagination first
Here is display error when click pagination first when the page load success.
the code in CMssqlCommandBuilder.php I get from the link above which I try it the code is below.
<?php
/**
CMsCommandBuilder class file.
@author Qiang Xue <qiang.xue@gmail.com>
@author Christophe Boulain <Christophe.Boulain@gmail.com>
@author Wei Zhuo <weizhuo[at]gmail[dot]com>
@copyright Copyright © 2008-2011 Yii Software LLC
@license http://www.yiiframework.com/license/
*/
/**
CMssqlCommandBuilder provides basic methods to create query commands for tables for Mssql Servers.
@author Qiang Xue <qiang.xue@gmail.com>
@author Christophe Boulain <Christophe.Boulain@gmail.com>
@author Wei Zhuo <weizhuo[at]gmail[dot]com>
@version $Id$
@package system.db.schema.mssql
@since 1.0.4
*/
class CMssqlCommandBuilder extends CDbCommandBuilder
{
/**
* Creates a COUNT(*) command for a single table.
* Override parent implementation to remove the order clause of criteria if it exists
* @param CDbTableSchema $table the table metadata
* @param CDbCriteria $criteria the query criteria
* @param string $alias the alias name of the primary table. Defaults to 't'.
* @return CDbCommand query command.
*/
public function createCountCommand($table,$criteria,$alias='t')
{
$criteria->order='';
return parent::createCountCommand($table, $criteria,$alias);
}
/**
* Creates a SELECT command for a single table.
* Override parent implementation to check if an orderby clause if specified when querying with an offset
* @param CDbTableSchema $table the table metadata
* @param CDbCriteria $criteria the query criteria
* @param string $alias the alias name of the primary table. Defaults to 't'.
* @return CDbCommand query command.
*/
public function createFindCommand($table,$criteria,$alias='t')
{
$criteria=$this->checkCriteria($table,$criteria);
return parent::createFindCommand($table,$criteria,$alias);
}
/**
* Creates an UPDATE command.
* Override parent implementation because mssql don't want to update an identity column
* @param CDbTableSchema $table the table metadata
* @param array $data list of columns to be updated (name=>value)
* @param CDbCriteria $criteria the query criteria
* @return CDbCommand update command.
*/
public function createUpdateCommand($table,$data,$criteria)
{
$criteria=$this->checkCriteria($table,$criteria);
$fields=array();
$values=array();
$bindByPosition=isset($criteria->params[0]);
$i=0;
foreach($data as $name=>$value)
{
if(($column=$table->getColumn($name))!==null)
{
if ($table->sequenceName !== null && $column->isPrimaryKey === true) continue;
if ($column->dbType === 'timestamp') continue;
if($value instanceof CDbExpression)
$fields[]=$column->rawName.'='.$value->expression;
else if($bindByPosition)
{
$fields[]=$column->rawName.'=?';
$values[]=$column->typecast($value);
}
else
{
$fields[]=$column->rawName.'='.self::PARAM_PREFIX.$i;
$values[self::PARAM_PREFIX.$i]=$column->typecast($value);
$i++;
}
}
}
if($fields===array())
throw new CDbException(Yii::t('yii','No columns are being updated for table "{table}".',
array('{table}'=>$table->name)));
$sql="UPDATE {$table->rawName} SET ".implode(', ',$fields);
$sql=$this->applyJoin($sql,$criteria->join);
$sql=$this->applyCondition($sql,$criteria->condition);
$sql=$this->applyOrder($sql,$criteria->order);
$sql=$this->applyLimit($sql,$criteria->limit,$criteria->offset);
$command=$this->getDbConnection()->createCommand($sql);
$this->bindValues($command,array_merge($values,$criteria->params));
return $command;
}
/**
* Creates a DELETE command.
* Override parent implementation to check if an orderby clause if specified when querying with an offset
* @param CDbTableSchema $table the table metadata
* @param CDbCriteria $criteria the query criteria
* @return CDbCommand delete command.
*/
public function createDeleteCommand($table,$criteria)
{
$criteria=$this->checkCriteria($table, $criteria);
return parent::createDeleteCommand($table, $criteria);
}
/**
* Creates an UPDATE command that increments/decrements certain columns.
* Override parent implementation to check if an orderby clause if specified when querying with an offset
* @param CDbTableSchema $table the table metadata
* @param CDbCriteria $counters the query criteria
* @param array $criteria counters to be updated (counter increments/decrements indexed by column names.)
* @return CDbCommand the created command
* @throws CException if no counter is specified
*/
public function createUpdateCounterCommand($table,$counters,$criteria)
{
$criteria=$this->checkCriteria($table, $criteria);
return parent::createUpdateCounterCommand($table, $counters, $criteria);
}
/**
* This is a port from Prado Framework.
*
* Overrides parent implementation. Alters the sql to apply $limit and $offset.
* The idea for limit with offset is done by modifying the sql on the fly
* with numerous assumptions on the structure of the sql string.
* The modification is done with reference to the notes from
* http://troels.arvin.dk/db/rdbms/#select-limit-offset
*
* <code>
* SELECT * FROM (
* SELECT TOP n * FROM (
* SELECT TOP z columns -- (z=n+skip)
* FROM tablename
* ORDER BY key ASC
* ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
* ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
* </code>
*
* <b>Regular expressions are used to alter the SQL query. The resulting SQL query
* may be malformed for complex queries.</b> The following restrictions apply
*
* <ul>
* <li>
* In particular, <b>commas</b> should <b>NOT</b>
* be used as part of the ordering expression or identifier. Commas must only be
* used for separating the ordering clauses.
* </li>
* <li>
* In the ORDER BY clause, the column name should NOT be be qualified
* with a table name or view name. Alias the column names or use column index.
* </li>
* <li>
* No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
* </li>
*
* @param string $sql SQL query string.
* @param integer $limit maximum number of rows, -1 to ignore limit.
* @param integer $offset row offset, -1 to ignore offset.
* @return string SQL with limit and offset.
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
*/
/* public function applyLimit($sql, $limit, $offset)
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
return $sql;
} */
public function applyLimit($sql, $limit, $offset)
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
{
$sql = preg_replace(
'/^SELECT\s+(DISTINCT\s)?/i',
'SELECT $1TOP ' . ($limit+$offset) . ' ',
$sql
);
$orderBy = stristr($sql, 'ORDER BY');
$over = substr($orderBy, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />;
$newSql = substr($sql, 0, strripos($sql, 'ORDER BY'));
//echo $newSql;
$from = strrpos($sql, 'FROM');
//$first=substr($newSql,0);
$newSql = substr($newSql, 0, $from) . " , ROW_NUMBER() OVER (ORDER BY $over) as yiinumber " . substr($newSql, $from);
//echo $newSql = substr_replace($newSql," , ROW_NUMBER() OVER (ORDER BY $over) as yiinumber ",substr($newSql, 0, $from),0);
$sql = "SELECT * FROM (
$newSql ) AS __innertable__ WHERE yiinumber > {$offset}";
//echo $qsql;
}
return $sql;
}
/**
* Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
* See http://troels.arvin.dk/db/rdbms/#select-limit-offset
* @param string $sql sql query
* @param integer $limit $limit > 0
* @param integer $offset $offset > 0
* @return sql modified sql query applied with limit and offset.
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
*/
protected function rewriteLimitOffsetSql($sql, $limit, $offset)
{
$fetch = $limit+$offset;
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
$ordering = $this->findOrdering($sql);
$orginalOrdering = $this->joinOrdering($ordering, '[__outer__]');
$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering), '[__inner__]');
$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner__] {$reverseOrdering}) as [__outer__] {$orginalOrdering}";
return $sql;
}
/**
* Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
*
* @param string $sql $sql
* @return array ordering expression as key and ordering direction as value
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
*/
protected function findOrdering($sql)
{
if(!preg_match('/ORDER BY/i', $sql))
return array();
$matches=array();
$ordering=array();
preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
if(count($matches)>1 && count($matches[2]) > 0)
{
$parts = explode(',', $matches[2][0]);
foreach($parts as $part)
{
$subs=array();
if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
{
if(count($subs) > 1 && count($subs[2]) > 0)
{
$name='';
foreach(explode('.', $subs[1][0]) as $p)
{
if($name!=='')
$name.='.';
$name.='[' . trim($p, '[]') . ']';
}
$ordering[$name] = $subs[2][0];
}
//else what?
}
else
$ordering[trim($part)] = 'ASC';
}
}
// replacing column names with their alias names
foreach($ordering as $name => $direction)
{
$matches = array();
$pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i';
preg_match($pattern, $sql, $matches);
if(isset($matches[1]))
{
$ordering[$matches[1]] = $ordering[$name];
unset($ordering[$name]);
}
}
return $ordering;
}
/**
* @param array $orders ordering obtained from findOrdering()
* @param string $newPrefix new table prefix to the ordering columns
* @return string concat the orderings
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
*/
protected function joinOrdering($orders, $newPrefix)
{
if(count($orders)>0)
{
$str=array();
foreach($orders as $column => $direction)
$str[] = $column.' '.$direction;
$orderBy = 'ORDER BY '.implode(', ', $str);
return preg_replace('/\s+\[[^\]]+\]\.(\[[^\]]+\])/i', ' '.$newPrefix.'.\1', $orderBy);
}
}
/**
* @param array $orders original ordering
* @return array ordering with reversed direction.
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
*/
protected function reverseDirection($orders)
{
foreach($orders as $column => $direction)
$orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
return $orders;
}
/**
* Checks if the criteria has an order by clause when using offset/limit.
* Override parent implementation to check if an orderby clause if specified when querying with an offset
* If not, order it by pk.
* @param CMssqlTableSchema $table table schema
* @param CDbCriteria $criteria criteria
* @return CDbCrireria the modified criteria
*/
protected function checkCriteria($table, $criteria)
{
if ($criteria->offset > 0 && $criteria->order==='')
{
$criteria->order=is_array($table->primaryKey)?implode(',',$table->primaryKey):$table->primaryKey;
}
return $criteria;
}
/**
* Generates the expression for selecting rows with specified composite key values.
* @param CDbTableSchema $table the table schema
* @param array $values list of primary key values to be selected within
* @param string $prefix column prefix (ended with dot)
* @return string the expression for selection
* @since 1.0.4
*/
protected function createCompositeInCondition($table,$values,$prefix)
{
$vs=array();
foreach($values as $value)
{
$c=array();
foreach($value as $k=>$v)
$c[]=$prefix.$table->columns[$k]->rawName.'='.$v;
$vs[]='('.implode(' AND ',$c).')';
}
return '('.implode(' OR ',$vs).')';
}
}
and I have error when I filter by datetime.
I don’t know if this solution is up to date or not,but it works for me.
1.in mssqlcommandbuilder use the patch from http://code.google.com/p/yii/issues/detail?id=2923
2.in cpagination ,the function applyLimit should look like this(read more about this at https://code.google.com/p/yii/issues/detail?id=3211)
/**
* Applies LIMIT and OFFSET to the specified query criteria.
* @param CDbCriteria $criteria the query criteria that should be applied with the limit
*/
public function applyLimit($criteria)
{
$limit=$this->getLimit();
$offset=$this->getOffset();
$itemcount=$this->getItemCount();
if($itemcount!=0 && ($limit+$offset)>$itemcount) {
$limit=max(0,$itemcount-$offset);
}
$criteria->limit=$limit;
$criteria->offset=$offset;
}
3.set default order for csort
$sort->defaultOrder = array('field_to_sort' => CSort::SORT_ASC);
Thank a lot Mr. Nakarin,
I try it but it still error.
I don’t find out the source of best solution with mssql on cgridview with relation table.
Now I use a trick. I take criteria->order
$criteria->order = "field_to_criteria sort"
begin declare $var = new CSort();
It does work but can not take the field_to_criteria to be sorting column.
in model
<?php
/**
* This is the model class for table "tblObjective".
*
* The followings are the available columns in table 'tblObjective':
* @property integer $ObjectiveId
* @property string $ObjectNumber
* @property string $Description
* @property integer $DivisionId
*/
class Objective extends CActiveRecord {
/**
* @return string the associated database table name
*/
public function tableName() {
return 'tblObjective';
}
/**
* @return array validation rules for model attributes.
*/
public function rules() {
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
return array(
array('DivisionId', 'numerical', 'integerOnly' => true),
array('ObjectNumber', 'length', 'max' => 20),
array('Description', 'safe'),
// The following rule is used by search().
// @todo Please remove those attributes that should not be searched.
array('ObjectiveId, ObjectNumber, Description, DivisionId, division', 'safe', 'on' => 'search'),
);
}
/**
* @return array relational rules.
*/
public function relations() {
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'division' => array(self::BELONGS_TO, 'Division', 'DivisionId'),
);
}
/**
* @return array customized attribute labels (name=>label)
*/
public function attributeLabels() {
return array(
'ObjectiveId' => 'Objective',
'ObjectNumber' => 'Object Number',
'Description' => 'Description',
'DivisionId' => 'Division',
);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
*
* Typical usecase:
* - Initialize the model fields with values from filter form.
* - Execute this method to get CActiveDataProvider instance which will filter
* models according to data in model fields.
* - Pass data provider to CGridView, CListView or any similar widget.
*
* @return CActiveDataProvider the data provider that can return the models
* based on the search/filter conditions.
*/
public function search() {
// @todo Please modify the following code to remove attributes that should not be searched.
$criteria = new CDbCriteria;
$criteria->with = array('division' => array('alias' => 'x'));
$criteria->together = true;
$criteria->compare('ObjectiveId', $this->ObjectiveId);
$criteria->compare('ObjectNumber', $this->ObjectNumber, true);
$criteria->compare('t.Description', $this->Description, true);
$criteria->compare('t.DivisionId', $this->DivisionId);
$criteria->compare('x.DivisionNumber', $this->division, true);
$criteria->compare('x.Description', $this->division, true, "OR");
$sort = new CSort;
$sort->attributes = array(
'division' => array(
'asc' => 'x.DivisionNumber',
'desc' => 'x.DivisionNumber DESC',
),
'*',
);
$sort->defaultOrder = array('ObjectiveId' => CSort::SORT_ASC,);
return new CActiveDataProvider($this, array(
'sort' => $sort,
'criteria' => $criteria,
));
}
/**
* Returns the static model of the specified AR class.
* Please note that you should have this exact method in all your CActiveRecord descendants!
* @param string $className active record class name.
* @return Objective the static model class
*/
public static function model($className = __CLASS__) {
return parent::model($className);
}
}
in view
<?php
$this->breadcrumbs = array(
'Objectives' => array('index'),
'Manage',
);
$this->menu = array(
array('label' => 'List Objective', 'url' => array('index')),
array('label' => 'Create Objective', 'url' => array('create')),
);
Yii::app()->clientScript->registerScript('search', "
$('.search-button').click(function(){
$('.search-form').toggle();
return false;
});
$('.search-form form').submit(function(){
$.fn.yiiGridView.update('objective-grid', {
data: $(this).serialize()
});
return false;
});
");
?>
<h1>Manage Objectives</h1>
<p>
You may optionally enter a comparison operator (<b><</b>, <b><=</b>, <b>></b>, <b>>=</b>, <b>
<></b>
or <b>=</b>) at the beginning of each of your search values to specify how the comparison should be done.
</p>
<?php echo CHtml::link('Advanced Search', '#', array('class' => 'search-button btn')); ?>
<div class="search-form" style="display:none">
<?php
$this->renderPartial('_search', array(
'model' => $model,
));
?>
</div><!-- search-form -->
<?php
$this->widget('bootstrap.widgets.TbGridView', array(
'id' => 'objective-grid',
'dataProvider' => $model->search(),
'filter' => $model,
'columns' => array(
'ObjectiveId',
'ObjectNumber',
'Description',
// 'DivisionId',
array(
'name' => 'division',
'value' => '$data->division->DivisionNumber." ".$data->division->Description',
'htmlOptions' => array('width' => '200'),
),
array(
'class' => 'bootstrap.widgets.TbButtonColumn',
'htmlOptions' => array('width' => '100'),
),
),
));
?>
Check how sorting works with my RelatedSearchBehavior extension (my fix for pagination is already mentionned, see also http://www.yiiframework.com/forum/index.php/topic/40054-mssql-sorting-problem/page__view__findpost__p__193772 ).