Greetings, i started studying Yii few days ago. I didn’t face many major difficulties until i tried to use CGridView. Probably questions that i`ll mention were asked many times before me, but even after searching and trying numerous solutions i couldn’t make working code.
My situation: There is MSSQL database with 4 tables. Main table - ‘table1’ which is related to ‘table2’ which is also relater to 2 other tables ‘table3’ and ‘table4’.
With usage of GII i have created 4 models of those tables and added CRUD for ‘table1’.
With CGridView i need to display data from all 4 table (table1.table1id, table2.table2id, table3.table3name, table4.table4name).
How can i force first way to work? How can i add search filters to second?
How can i bind parameters to both queries?Which way is more preferable to use?
1) way With usage of models and eager loading(with) i couldn’t even achieve displaying data from table2. Error text - “Undefined property table1.table2id”. Here is code sample that i have tried:
Model of table1
public function relations()
{return array(
'table2s' => array(self::HAS_MANY, 'table2', 'table1id'), );
public function search()
{
$criteria=new CDbCriteria;
$criteria->compare('table1id',$this->table1id);
$criteria->with=array('table2s');
$criteria->compare('table2s.table2id', $this->table2s->table2id, true);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'pagination' => array(
'pageSize' => 10000000,
),
));
}
cGridView
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'table1-grid',
'dataProvider'=>$model->search(),
'enablePagination' => false,
'filter'=>$model,
'columns'=>array(
'table1id',
array(
'name'=>'table2id',
'value'=>'$data->table2s->table2id',
'sortable'=>true,
),
),
));
2) way Second way was with usage of CSqlDataProvider. This time i managed to display all needed data, but couldn’t display search filters above CGridView table.
Model of table1
public function searchTableDisc()
{
$sql = 'SELECT table1.table1id AS t1id,
table2.table2id as t2id,
table3.Name as t3name,
table4.Name as t4name
FROM table1
INNER JOIN table2 ON
table1.table1id=table2.table1id
INNER JOIN table3 ON
table2.table3id=table3.table3id
INNER JOIN table4 ON
table2.table4id=table4.table4id';
$rawData = Yii::app()->db->createCommand($sql);
$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM (' . $sql . ') as count_alias')->queryScalar(); //the count
return $ProperData = new CSqlDataProvider($rawData, array(
'keyField' => 't1id',
'totalItemCount' => $count,
//if the command above use PDO parameters
//'params'=>array(
//':param'=>$param,
//),
'sort' => array(
'attributes' => array(
't1id','t2id', 't3name','t4name',
),
'defaultOrder' => array(
't1id' => CSort::SORT_ASC, //default sort value
),
),
'pagination' => array(
'pageSize' => 100000,
),
));
}
cGridView
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'table1-grid',
'dataProvider'=>$model->searchTableDisc(),
'ajaxUpdate' => true,
'enablePagination' => false,
//'filter'=>$model,
'columns'=>array(
't1id',
't2id',
't3name',
't4name',
),
));