Relational data in datagrid

thion007 · October 19, 2011, 6:01pm

Hi all,

I’m going crazy right now…

I have to retrieve data from 3 tables and I’m reading through solutions all over the internet, but still there is this error: ‘Relation “mgb_bewerber_berufe_zt” is not defined in ActiveRecord-Class “mgb_bewerber”’…

Here’s my code:

Model mgb_bewerber => Relations




public $mgb_berufsbez;


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(

        	'bewerber'=>array(self::HAS_MANY,'mgb_bewerber_berufe_zt','mgb_bewerber_id'),

			'berufe'=>array(self::HAS_MANY,'mgb_berufe','mgb_berufe_id','through'=>'mgb_bewerber_berufe_zt'),          

        );

	}

Controller


public function actionAdmin()

	{

$model=new mgb_bewerber('search');

		$model->unsetAttributes();  // clear any default values

		

		if(isset($_GET['mgb_bewerber']))

			$model->attributes=$_GET['mgb_bewerber'];


		$this->render('bewerberSuche',array(

			'model'=>$model,

		));

	}

View


<div class="search-form" style="display:none">

<?php $this->renderPartial('_search',array(

	'model'=>$model,

)); ?>

</div><!-- search-form -->


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

	'id'=>'mgb-bewerber-grid',

	'filter'=>$model->with('bewerber','berufe')->together()->findAll(),

	'columns'=>array(

		'mgb_bewerber_id',

		'mgb_vorname',

		'mgb_nachname',

		'mgb_strasse_hnr',

		'mgb_plz',

		'mgb_ort',

		'mgb_berufsbez',

		'mgb_angelegt',

		'mgb_angelegt_von',

		

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>

I’m really grateful for every clue I can get…

Thanks in advance,

thion007

sidewinder · October 19, 2011, 6:12pm

Thio007:




public $mgb_berufsbez;


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(

        	'bewerber'=>array(self::HAS_MANY,'mgb_bewerber_berufe_zt','mgb_bewerber_id'),

			'berufe'=>array(self::HAS_MANY,'mgb_berufe','mgb_berufe_id','through'=>'mgb_bewerber_berufe_zt'),          

        );

	}

You have only ‘bewerber’ and ‘berufe’ relations defined, yet you want to select through ‘mgb_bewerber_berufe_zt’ relation. Hence the error. Check this out Relational Query with through

thion007 · October 19, 2011, 6:31pm

Hi sidewinder,

thanks for the fast reply!!!

Ok, I’m getting further on:

Fatal error: Call to a member function getData() on a non-object in C:\Devel\yii\framework\zii\widgets\CBaseListView.php on line 105

Maybe you have another tip?

Regards,

Thio007

sidewinder · October 19, 2011, 6:45pm

I need more info. If you can, paste relevant bits from your view file. Also check this thread: http://www.yiiframew…333#entry120333 as I suspect you may have fallen in a similar "empty relational query result = null" trap as MikeT did.

thion007 · October 20, 2011, 7:49am

Hi sidewinder,

Here’s my view file:




<?php

$this->breadcrumbs=array(

	'Bewerbersuche',

);


Yii::app()->clientScript->registerScript('search', "

$('.search-button').click(function(){

	$('.search-form').toggle();

	return false;

});

$('.search-form form').submit(function(){

	$.fn.yiiGridView.update('mgb-bewerber-grid', {

		data: $(this).serialize()

	});

	return false;

});

");

?>


<h1>Bewerbersuche</h1>


<div class="search-form" style="display:none">

<?php $this->renderPartial('_search',array(

	'model'=>$model,

)); ?>

</div><!-- search-form -->


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

	'id'=>'mgb-bewerber-grid',

	'dataProvider'=>$model->with('bewerber','berufe')->together()->findAll(),

	'filter'=>$model,

	'columns'=>array(

		'mgb_bewerber_id',

		'mgb_vorname',

		'mgb_nachname',

		'mgb_strasse_hnr',

		'mgb_plz',

		'mgb_ort',

		'mgb_berufsbez',

		'mgb_angelegt',

		'mgb_angelegt_von',

		

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>

The column ‘mgb_berufsbez’ is from table ‘mgb_berufe’.

Thanks again,

Thio007

sidewinder · October 20, 2011, 8:36am

That’s source of your problem. dataProvder has to be an instance of… CDataProvider (or derived class) you are passing instance of class derived from CActiveRecord.

When you generate CRUD from gii it prepares a search() function in your model class. That function returns a proper CDataProvider so line quoted above should be:


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

However, you want some relational data to be pulled form db as well, so you will have to rework that function a bit. Namely you will want to add with and together options to CDbCriteria. Below is an example of such function from one of my applications:




public function search($size = 10) {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria = new CDbCriteria;

        $criteria->with = array('adres');

        $criteria->together = true;

        $criteria->compare('telefon', $this->telefon, true);

        $criteria->compare('email', $this->email, true);

        $criteria->compare('fax', $this->fax, true);

        $criteria->compare('nip', $this->nip, true);

        $criteria->compare('typ', $this->typ, true);

        $criteria->compare('nazwa_wyswietlana', $this->nazwa_wyswietlana, true);

        $criteria->compare('adres.miasto', $this->Adres_id,true);

        return new CActiveDataProvider($this, array(

                    'criteria' => $criteria,

                    'pagination' => array(

                        'pageSize' => $size,

                    ),

                ));

    }

Notice how last compare function is constructed to allow filtering in CDataGrid by using values from related table instead of id.

I hope that helps

thion007 · October 20, 2011, 9:03am

Hi sidewinder,

I’m getting to it

Error message disappeared, but the data field in grid is empty. Application log is not showing anything…

Here’s my search-function according to your hints:




public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->with = array('bewerber','berufe');

		

		$criteria->together = true;


		$criteria->compare('mgb_bewerber_id',$this->mgb_bewerber_id);


		$criteria->compare('mgb_vorname',$this->mgb_vorname,true);


		$criteria->compare('mgb_nachname',$this->mgb_nachname,true);


		$criteria->compare('mgb_strasse_hnr',$this->mgb_strasse_hnr,true);


		$criteria->compare('mgb_plz',$this->mgb_plz,true);


		$criteria->compare('mgb_ort',$this->mgb_ort,true);


		$criteria->compare('mgb_telefon',$this->mgb_telefon,true);


		$criteria->compare('mgb_fax',$this->mgb_fax,true);


		$criteria->compare('mgb_handy',$this->mgb_handy,true);


		$criteria->compare('mgb_email',$this->mgb_email,true);


		$criteria->compare('mgb_schule_id',$this->mgb_schule_id);


		$criteria->compare('mgb_status',$this->mgb_status);

		

		$criteria->compare('mgb_geburtsname',$this->mgb_geburtsname);

		

		$criteria->compare('mgb_personalausweis_nr',$this->mgb_personalausweis_nr);

		

		$criteria->compare('mgb_geburtstag',$this->mgb_geburtstag);


		$criteria->compare('mgb_geburtsort',$this->mgb_geburtsort);

		

		$criteria->compare('mgb_sv_nummer',$this->mgb_sv_nummer);

		

		$criteria->compare('mgb_religion_id',$this->mgb_staat_id);

		

		$criteria->compare('mgb_religion_id',$this->mgb_krankenkasse_id);

		

		$criteria->compare('mgb_religion_id',$this->mgb_religion_id);

		

		$criteria->compare('mgb_steuerklasse',$this->mgb_steuerklasse);

		

		$criteria->compare('mgb_familienstand',$this->mgb_familienstand);

		

		$criteria->compare('mgb_kinder',$this->mgb_kinder);

		

		$criteria->compare('mgb_kinder_anzahl',$this->mgb_kinder_anzahl);

		

		$criteria->compare('mgb_bank',$this->mgb_bank);

		

		$criteria->compare('mgb_blz',$this->mgb_blz);

		

		$criteria->compare('mgb_konto',$this->mgb_konto);

		

		$criteria->compare('mgb_konto_inhaber',$this->mgb_konto_inhaber);

		

		$criteria->compare('mgb_vgs',$this->mgb_vgs);

		

		$criteria->compare('mgb_vgs_hoehe',$this->mgb_vgs_hoehe);

		

		$criteria->compare('mgb_adresspruefung',$this->mgb_adresspruefung);

		

		$criteria->compare('mgb_notiz',$this->mgb_notiz);

		

		$criteria->compare('berufe.mgb_berufsbez',$this->mgb_berufsbez);


		$criteria->compare('mgb_angelegt',$this->mgb_angelegt,true);


		$criteria->compare('mgb_angelegt_von',$this->mgb_angelegt_von,true);


		return new CActiveDataProvider('mgb_bewerber', array(

			'criteria'=>$criteria,

		));

	}

Thanks again!!!

Thio007

sidewinder · October 20, 2011, 9:28am

Hard to say Thio,

try using debugger,

try displaying data step by step - first only id field, then related table and then table which is related to the related table (through).

try setting enableParamLogging to true so you can debug sql queries with real data instead of placeholders.

thion007 · October 20, 2011, 6:16pm

Hi sidewinder.

sorry to bother you again…

I did as you said, I pasted the sql query from the application log directly into the mysql console and voila: the results appear as I wanted.

The problem has to be in the view file or in the controller. Do you need more info?

Thanks for all your help!

Thio007