related tables in gridview

I have what i know is a simple problem, but it has got me beat.

I have two tables, strains and toxin, where one bacterial strain has many different toxins.

My problem is that I can populate a gridview table with data from strains, but not the related data from a field called ‘type’ in table toxins.

The relationship that table strains has with table toxin is:

public function relations()//(note that rid_strain is the FK in table toxin)

{


	return array(


		'toxins' => array(self::HAS_MANY, 'Toxin', 'rid_strain'),


	);


}

The controller is:

public function actionIndex()

{


	$model=new Strains('search');


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


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


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





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


		'model'=>$model,


	));


}

In the Model I also have:

public function search() //note that i have not put any of the filtering stuff in yet since i can’t populate the field ‘type’ from associated table ‘toxin’

{





	$criteria=new CDbCriteria;





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





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


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


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


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


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


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


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


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

return new CActiveDataProvider($this,array(

		'criteria'=>$criteria,


	


	));


}

and the view is:

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

'id'=>'strains-grid',


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


'filter'=>$model,


'columns'=>array(


	// removing one of these attributes will remove the column from the search form 


	'strain',


	'gene',


	'summary',


	'date',


	'environment',


	'toxins.type', //I added this on top of gii-generated code.


	'rid',





	array(


                'class'=>'CButtonColumn',


                    'template'=>'{update}{view}{delete}',


                    'buttons'=>array(


                                    'update'=>array(


                                                    'visible'=>'false',


                                            ),


                                    'view'=>array(


                                                    'visible'=>'true',


                                            ),


                                    'delete'=>array(


                                                    'visible'=>'false',


                                            ),


                    ),


           ),


),

)); ?>

"toxins.type" cannot be displayed directly because "toxins" is an array of Toxin model objects and CGridView has no idea how to display them in a table cell. Tell it what to do:

'columns'=>array(

  'strain',

  'gene',

  'summary',

  'date',

  'environment',

  array(

	'header' => 'Toxin types',

 	// Display a comma separated list of toxin types

 	// $data: Strain model instance, $row: number of current table row, $toxin: Toxin model instance

 	'value' => function ($data, $row) { return implode(', ',  array_map(function ($toxin) { return $toxin->type;},  $data->toxins)) ;},

  ),

  'rid',

  //...

),

When you think about it, you would probably get an error because toxins would be an array (HAS_MANY) and going down the relations like strain.toxins.type wouldn’t work. Your code would probably work if it was a HAS_ONE relationship from strain to toxin.

How do you like the data to display in the grid? Comma separated values for multiple toxin types?

Thanks for the help. From what you and phtamas said, it seems that gridview handles one to one relationships quite well, but that one to many is quite a bit more involved. Since i am just starting out, I think that i need to read up a bit more on yii syntax before i tackle this…if i am still having trouble i’ll be back.

Thanks again!

OK, read up on it and I understand what you did, and it works perfect-thanks.

Now, I would like to filter that column. I assume that I have to add a criteria for this in the model’s search() method.

I would like to do an inner join between tables strains and toxin so that I only show stains that have certain toxins. Do you know how to do this?

thanks

Are you paging your strains table or not, because I think the solution can be quite different depending on how you are creating the query to build out the table (paging might make things more difficult because of how mysql LIMITs complicate things with HAS MANY relations).

At this point I am just learning how to work with Yii. Although I eventually would want to do paging with Strains, perhaps it would be best for me to just understand the simplest way to filter a gridview column that contains concatenated Toxin data derived via the map_array/implode methods.

thanks!

Hi

Check out this demo which fetches data from many related tables from a database involving a "store":

http://relatedsearchbehavior.ynamics.com/

It is based on this extension:

www.yiiframework.com/extension/relatedsearchbehavior/

I haven’t got a “great” tutorial on the extension, so a little bit of effort to start using it is required, but it is an enormous timesaver.

Note that in many cases a HAS_MANY relation becomes a HAS_ONE relation if you revert it. So maybe your grid should start with the toxins as the base table and strains as a related table.

Also a MANY_MANY relation often has an link table where each line has two HAS_ONE relations. So using the link table as the starting point may be the solution.

In the abovementionned demo, the invoice line is the starting point. Hence only products that appear in an invoice are shown.