Yii Framework Forum: Multi Level Relations In Cgridview - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Multi Level Relations In Cgridview Rate Topic: *---- 1 Votes

#1 User is offline   Kurniawan Junaidy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 04-December 11

Posted 19 February 2013 - 02:35 AM

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
0

#2 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 281
  • Joined: 08-June 10
  • Location:France

Posted 19 February 2013 - 03:02 AM

Check out my extension, Related Search Behavior .

The core of your issue is that you need ro have a 'with' condition to make sure that the tables you need are joined in the query.
My extension takes care of that.
0

#3 User is offline   Kurniawan Junaidy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 04-December 11

Posted 19 February 2013 - 03:13 AM

View Postle_top, on 19 February 2013 - 03:02 AM, said:

Check out my extension, Related Search Behavior .

The core of your issue is that you need ro have a 'with' condition to make sure that the tables you need are joined in the query.
My extension takes care of that.


I have already add 'with' condition and still not working.
0

#4 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 281
  • Joined: 08-June 10
  • Location:France

Posted 19 February 2013 - 03:26 AM

Ok, I overlooked that. countryName is a field of the country table, not of the province table (in the order clause).
0

#5 User is offline   Kurniawan Junaidy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 04-December 11

Posted 19 February 2013 - 03:34 AM

View Postle_top, on 19 February 2013 - 03:26 AM, said:

Ok, I overlooked that. countryName is a field of the country table, not of the province table (in the order clause).


Yes absolutely. But i don't know how to correct that. City is not directly related to country table, so i think it is impossible to include country in 'with' condition along with province. BTW, thank you for the reply.
0

#6 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 281
  • Joined: 08-June 10
  • Location:France

Posted 19 February 2013 - 08:20 AM

It is possible to do so; check out http://www.yiiframew...vedataprovider/ or just use my extension.
0

#7 User is offline   rizal13 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 05-February 13
  • Location:Indonesia

Posted 19 February 2013 - 09:15 AM

I think this link may help you a little bit:
www.mrsoundless.com/php/yii/searching-and-sorting-a-column-from-a-related-table-in-a-cgridview/
0

#8 User is offline   Kurniawan Junaidy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 04-December 11

Posted 19 February 2013 - 08:42 PM

View Postle_top, on 19 February 2013 - 08:20 AM, said:

It is possible to do so; check out http://www.yiiframew...vedataprovider/ or just use my extension.


Finally, i got it working. I add Nested Eager Loading as shown in your link in simpler form.

$criteria->with = array('province', 'province.country');


Thanks a lot le_top.
0

#9 User is offline   Kurniawan Junaidy 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 04-December 11

Posted 19 February 2013 - 08:46 PM

View Postrizal13, on 19 February 2013 - 09:15 AM, said:

I think this link may help you a little bit:
www.mrsoundless.com/php/yii/searching-and-sorting-a-column-from-a-related-table-in-a-cgridview/


I have already checked that link. But in my case is multi level relations (relation through relation). Anyway thank you for your response.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users