Hi. Does anyone know how to deal with multi level relations in CGridView? I have 3 tables as follows:
tbl_country(
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
countryName VARCHAR(128) NOT NULL,
countryCode SMALLINT UNSIGNED NOT NULL,
isoCode VARCHAR(2) NOT NULL,
PRIMARY KEY(id),
UNIQUE(countryName),
UNIQUE(countryCode),
UNIQUE(isoCode)
);
tbl_province(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
countryId SMALLINT UNSIGNED NOT NULL,
provinceName VARCHAR(128) NOT NULL,
shortCode VARCHAR(32),
PRIMARY KEY(id),
FOREIGN KEY(countryId) REFERENCES tbl_country(id),
UNIQUE(countryId, provinceName),
UNIQUE(countryId, shortCode)
);
tbl_city(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
provinceId INTEGER UNSIGNED NOT NULL,
cityName VARCHAR(128) NOT NULL,
shortCode VARCHAR(32),
cityType ENUM('Kota', 'Kabupaten') DEFAULT 'Kota',
PRIMARY KEY(id),
FOREIGN KEY(provinceId) REFERENCES tbl_province(id),
UNIQUE(provinceId, cityName),
UNIQUE(provinceId, shortCode)
);
I want to show city list along with province & country for the appropriate city inside CGridView. The problem is i can’t either sort or search on country column. Here is what i do so far:
I add province & country attribute into City model:
public $provinceName;
public $countryName;
I change the City model rules:
return array(
array('provinceId, cityName', 'required'),
array('provinceId', 'length', 'max'=>10),
array('cityName', 'length', 'max'=>128),
array('shortCode', 'length', 'max'=>32),
array('cityType', 'length', 'max'=>9),
// The following rule is used by search().
// Please remove those attributes that should not be searched.
array('id, provinceId, cityName, shortCode, cityType, provinceName, countryName', 'safe', 'on'=>'search'),
);
The relations function is as follows:
return array(
'province' => array(self::BELONGS_TO, 'Province', 'provinceId'),
);
The search function is as follows:
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->with = array('province');
$criteria->compare('cityName',$this->cityName,true);
$criteria->compare('shortCode',$this->shortCode,true);
$criteria->compare('cityType',$this->cityType,true);
$criteria->compare('province.provinceName', $this->provinceName, true);
$criteria->compare('province.countryName', $this->countryName, true);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'sort' => array(
'defaultOrder' => 'cityName',
'attributes' => array(
'provinceName' => array(
'asc' => 'province.provinceName',
'desc' => 'province.provinceName DESC',
),
'countryName' => array(
'asc' => 'province.countryName',
'desc' => 'province.countryName DESC',
),
'*',
),
)
));
}
And last one the CGridView configuration:
<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'city-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
array(
'class'=>'CButtonColumn',
'template' => '{update}{delete}',
),
'cityName',
'shortCode',
'cityType',
array(
'name' => 'provinceName',
'value' => '$data->province->provinceName',
),
array(
'name' => 'countryName',
'value' => '$data->province->country->countryName',
),
),
)); ?>
Whenever i search or click on country name column, it shows error like this:
Error 500: <h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]:
Column not found: 1054 Unknown column 'province.countryName' in 'order clause'.
The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`provinceId` AS `t0_c1`,
`t`.`cityName` AS `t0_c2`, `t`.`shortCode` AS `t0_c3`,
`t`.`cityType` AS `t0_c4`,
`province`.`id` AS `t1_c0`, `province`.`countryId` AS `t1_c1`,
`province`.`provinceName` AS `t1_c2`,
`province`.`shortCode` AS `t1_c3`
FROM `tbl_city` `t`
LEFT OUTER JOIN `tbl_province` `province`
ON (`t`.`provinceId`=`province`.`id`)
ORDER BY province.countryName LIMIT 10
I know it happens because country is not included in SQL. But i don’t know how to tackle that problem. Please help me