Join Three Tables Into Cgridview

Yii 1.1 General Discussion
1

Hi community!

I want to join four tables (hierarchic tree structure) into a CGridView (in admin view). (Table names are bold.)

Level1(id, descr) <1-n> Level2(id, descr) <1-n> Level3 <1-n> Item(id, descr)

Basically what I wanna do is show all Items and which Levels they belong to. (eg. Item "testarossa" is in "cars"->"supercars"->"ferrari")

I declared my sql-statement in the model and it works.

My Grid displays valid data in every row and column, but when it comes to level2 and 1, the relations aren’t displayed correctly. I think I have a problem with the column data and the table hierarchy.

Here is my grid:


$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'frage-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'lvl3.lvl2.lvl1.descr', 

		'lvl3.lvl2.descr', 

		'lvl3.descr', 

		'descr',

		//'lvl3.id',

		array(

			'class'=>'CButtonColumn',

		),

	),

));

Please let me know if you need anymore information on this issue.

Thanks in advance for helping!

2

Did you try create four models and declare relations inside them?

You can create these models and declare for each BELONG_TO relation, finally you’ll get expressions in your grid look like that ‘level1.level2.level3.descr’

3

Thank you for your response! Inside each model file there is a relations() containing the BELONGS_TO. This was generated by GiiModel Creator. Anyway… how do I change my GRID, so that the column value for Level2 and Level1 is displayed correctly?

It seems that the linking of the data (with keys) wont work correctly…

Here are my two (SAME) SQL-Statements:


$criteria->join .='  JOIN ebene3 as e3 ON t.e3id = e3.e3id  JOIN ebene2 as e2 ON e2.e2id = e3.e2id  JOIN ebene1 as e1 ON e1.e1id = e2.e1id';

		//$criteria->join .=' INNER JOIN ebene2 as e2 ON e2.e2id = e3.e2id';

		//$criteria->join .=' INNER JOIN ebene1 as e1 ON e1.e1id = e2.e1id';

		$criteria->together = true;

return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

and


$sql = 'SELECT e1.e1bez, e2.e2bez, e3.e3bez, f.fbez FROM Frage f

				INNER JOIN ebene3 e3 on e3.e3id=f.e3id

				INNER JOIN ebene2 e2 on e2.e2id=e3.e2id

				INNER JOIN ebene1 e1 on e1.e1id=e2.e1id;';

		return new CSqlDataProvider($sql, array(

    'keyField' => 'fbez'

));

Best regards!