Obtaining Data From Another Table Cgridview Widget

Hello,

I am currently trying to use the CGridView widget. I am posting as I am unsure as to how to get a column from another table (target_name from the target table) and have it displayed in the table used in CGridView. I have 3 tables; drug_has_target is my intermediate table, which is linked to drug and target. Currently, CGridView is using the drug_has_target (intermediate!) table. I would like to;

  1. add an extra column in this widget

  2. have the content of the extra column to be target_name (from table 2, an FK of intermediate table points to PK of this table)

I would like to know how to go about this …how can I add a column and “fetch” the content from a different table which is linked. I’ve tried different options which I saw online, but they didn’t work … would say that drug_has_target.target.target_id is not defined (or something along these lines). Curious to know if I need to create relations +attribute label in the drug_has_target model script in order to add this to the CGridView widget.

Drug (table 1):

drug_id (PK)

drug_name

Target (table 2):

target_id (PK)

target_name

drug_has_target (table 3, intermediate table):

drug_id (FK1)

target_id (FK2)

Current relations:




//drug_has_target.php (model)

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(

			'drug' => array(self::BELONGS_TO, 'Drug', 'drug_id'),

			'target' => array(self::BELONGS_TO, 'Target', 'target_id'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'drug_id' => 'Drug',

			'target_id' => 'Target',

		);

	}






I don’t really know what I am doing (newbie), so if you can manage to be explicit with your solution, that would be VERY much appreciated!

Thank you!

Tanya

Dear Friend

In views/drug_has_target/admin.php

We can declare the columns in the following way.




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

	'id'=>'drug_has_target-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'id',

		'drug_id',

                'drug.drug_name',

		'target_id',

		'target.target_name',

		array(

			'class'=>'CButtonColumn',

		),

	),

));



Are you trying to show all drugs and all their targets or a single drug and it’s targets?

Thank you for your reply to my question.

I am trying to show all drugs and their respective targets (but wouldnt want 5 rows to show up for a drug that has 5 targets if I could bypass that). For example, if I couldbunch` the targets into one entry that would be fantastic)

Hello,

Thank you for the reply.

This is one of the things I had tried, and I had gotten an error along the lines of drug_has_target.target.target_name is not defined …

You can look at Yii-Booster’s excellent grid extension - TbRelationalColumn.

As for your first problem, I wouldn’t use the intermediate model, drug_has_target. You can simply use Drug’s search method.

Matt

Thank you Matt, I will look into this.

I am just unsure about using the Drug`s search method. Could you explain this a bit more please?

Thank you,

Tanya

Sure. You do not need to create a model for the link table. Look at Drug model; the last method in there is search(). This method creates and returns a CActiveDataProvider. In your current code, you’re calling this method in your grid: ‘dataProvider’=>$model->search()

What I’m suggesting is to pass a Drug model to your views/grid, instead of a Drug_Has_Target model. Just make sure Drug and Target models have their relations setup correctly.

As for the "drug_has_target.target.target_name is not defined method", I’ll guess that the column name in the target table is not target_name, but rather name. If so, try this code.





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

	'id'=>'drug_has_target-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'drug.id',

		'drug.name',

		'target.id',

		'target.name',

		array(

			'class'=>'CButtonColumn',

		),

	),

));



Thank you for the clarification. I am now working with the admin.php file for Drug (as per your suggestion). When I view the page, it works for drug_id and drug_name, however the drug_target column is empty. I am thinking that I haven’t set up my relationships appropriately …

In the admin file I have:




//Admin.php

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

        'id'=>'drug_has_target-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'drug_id',

                'drug_name',

                'target.target_name',

                

                array(

                        'class'=>'CButtonColumn',

                ),

        ),

)); ?>



As relationships, I have the following set up:




//Drug.php

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(

			'drug2products' => array(self::HAS_MANY, 'Drug_has_product', 'drug_id'),

			'drug2targets' => array(self::HAS_MANY, 'Drug_has_target', 'drug_id'),

		);

	}




//Target.php


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(

			'drug2targets' => array(self::HAS_MANY, 'Drug_has_target', 'target_id'),

			'target2genes' => array(self::HAS_MANY, 'Target_has_gene', 'target_id'),

		);

	}




public function attributeLabels()

	{

		return array(

			'target_id' => 'Target',

			'target_name' => 'Target Name',

		);

	}



I’m not sure what is wrong with my relationships though … a drug DOES have many targets (for which the table has target_id and target_name as columns)? Would you happen to see what I am doing wrong?

Thank you again for the help,

Tanya

K- glad you got it working. Remember that a single row is displayed for a single drug - even through it has many targets. You cannot display many targets in a single row.

In order to achieve this you can use either the grid extension (post #6) or a CListView. Listviews are recommended if you need to show complex layouts that a grid can’t handle. Hope this makes sense.

Matt

One more thing - your many-many relationships should be configured like so.




// Drug model

'targets' => array(self::MANY_MANY, 'Target', 'Drug_has_target(drug_id, target_id)')


// Target model

'drugs' => array(self::MANY_MANY, 'Drug', 'Drug_has_target(target_id, drug_id)')



Matt

Hi

Unfortunately my extension is not yet very well known because it makes this taks quite easy to do.

Have a look at RelatedSearchBehavior !

Mario

Great extension for searching relations. Correct me if I’m wrong, but this extension isn’t designed to show one (drug) - many (targets) in a single row, right? Your demo shows multiple rows for a single invoice.

Matt

Hi

The gridview shows one record for each row. A post above seems to indicate the CListView does this better?

If you want to show a has_many relation in a column, youl’ld have to create a virtual attribute that reads the ‘many’ relation in order to display it.

There may be a way to extend the extension further to detect HAS_MANY relations and adapt the search/retrieve method for that kind of fields.

The question is: how should this field be rendered: add a separator like ‘<br />’ between each field, put each field in its on ‘div’ tag, … . It could start with a default one and then grow into more options.

Sorry, single invoice Id.




//_view

<div class="view">

	DRUG_NAME

	

 	GridView of Targets

	===================

	===================

	===================

	===================

	===================

	===================

</div>



I think your extension and KeenActiveDataProvider are awesome - used the latter in my last project. But the issue isn’t relations; the original poster is looking for a way to display a list of drugs and their respective targets.

Yes, a GridView can display some relational data, but it’s limited and not designed to do so - that’s why TbRelationalColumn was created.

Thank you for the help - I did not notice my error in the relations.

Tanya

After changing the relations, I am still experiencing the same problem (see attached image) - my drug target_id’s and target_names’s won’t show up (at least there’s no error messages). Could there be another source for this error?

Thank you!

Tanya

3964

error.png

No, you’ve done everything correctly but you’ve run into the problem le_top and I were discussing. To display the names of the targets add this to your Drug model





public function targetsToString()

{

    $targets = $this->targets;

    if ($targets) 

	{

        $string = '';

        foreach($targets as $target) {

            $string .= $targets->name . ', ';

        }

        return substr($string,0,strlen($string)-1);

    }

    return null;

}



and modify your grid to this:





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

	'id'=>'drug-targets-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		array(

			'header' => 'Targets',

			'value' => '$data->targetsToString()'

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>



Please see my earlier post about how to cleanly get around this limitaion.

Matt

Another option is to render a partial view in your Targets column.

[/size]

You are right, but in the end this is still summing up to a GridView. The extension is perfectly useable and a virtual attribute can/should be used to group the multiple columns (or one should resort to the Tb column that you mention).

Kind regards

Mario