Yii Framework Forum: Obtaining Data From Another Table Cgridview Widget - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

Obtaining Data From Another Table Cgridview Widget Have data from a column from another table show up in CGridView widget Rate Topic: ***** 1 Votes

#1 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 15 March 2013 - 01:53 PM

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
0

#2 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 15 March 2013 - 02:39 PM

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',
		),
	),
));

0

#3 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 15 March 2013 - 07:50 PM

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

#4 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 16 March 2013 - 05:02 AM

Thank you for your reply to my question.
I am trying to show all drugs and their respective targets (but wouldn`t want 5 rows to show up for a drug that has 5 targets if I could bypass that). For example, if I could `bunch` the targets into one entry that would be fantastic)

View Postwaterloomatt, on 15 March 2013 - 07:50 PM, said:

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

0

#5 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 16 March 2013 - 05:04 AM

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 ...


View Postseenivasan, on 15 March 2013 - 02:39 PM, said:

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',
		),
	),
));


0

#6 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 17 March 2013 - 04:45 AM

View PostTanya_new_2_yii, on 16 March 2013 - 05:02 AM, said:

Thank you for your reply to my question.
I am trying to show all drugs and their respective targets (but wouldn`t want 5 rows to show up for a drug that has 5 targets if I could bypass that). For example, if I could `bunch` the targets into one entry that would be fantastic)

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
0

#7 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 17 March 2013 - 06:24 AM

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

View Postwaterloomatt, on 17 March 2013 - 04:45 AM, said:

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

0

#8 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 17 March 2013 - 06:57 AM

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',
		),
	),
));


0

#9 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 18 March 2013 - 08:47 AM

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

View Postwaterloomatt, on 17 March 2013 - 06:57 AM, said:

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',
		),
	),
));

Attached File(s)


0

#10 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 10:36 AM

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
0

#11 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 10:40 AM

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
0

#12 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 386
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 18 March 2013 - 10:40 AM

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
0

#13 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 11:00 AM

View Postle_top, on 18 March 2013 - 10:40 AM, said:

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
0

#14 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 386
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 18 March 2013 - 11:53 AM

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.
0

#15 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 12:48 PM

Quote

Your demo shows multiple rows for a single invoice.

Sorry, single invoice Id.

Quote

A post above seems to indicate the CListView does this better?

//_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.
0

#16 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 18 March 2013 - 01:04 PM

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

Tanya

View Postwaterloomatt, on 18 March 2013 - 10:40 AM, said:

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

0

#17 User is offline   Tanya_new_2_yii 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 44
  • Joined: 10-March 13

Posted 18 March 2013 - 01:17 PM

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

Attached File  error.png (19.51K)
Number of downloads: 39

View Postwaterloomatt, on 18 March 2013 - 10:40 AM, said:

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

0

#18 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 01:34 PM

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.

Quote

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
0

#19 User is offline   waterloomatt 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 545
  • Joined: 09-April 10

Posted 18 March 2013 - 01:43 PM

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

#20 User is offline   le_top 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 386
  • Joined: 08-June 10
  • Location:France (Ile-de-France/Val d'Oise)

Posted 18 March 2013 - 01:45 PM

View Postwaterloomatt, on 18 March 2013 - 12:48 PM, said:

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.

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


0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • 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