Display data from other model by some key

Hi,

It must be obvious but I have’nt found that in cookbook. I managed how to fill dropdown list from database. I have models A and B and in the view of model A I am displaying data from model B in dropdown list.

Now the standard yii application after creating models and CRUDs displays the details of every record in database of model A just by displaying raw values from model/database. But some values in this record are the keys to external table/model. And I would like to display the value/text from model B more than key (which most often is ID) from model A.

How should I implement this? Just in A view call some yii method to get corresponding value from model B? Or should I put it in the model or controller an pass then to view?

Any suggestions appreciated.

Tom

In your form just use something like

<?php echo CHtml::activeDropDownList($modelA, 'relation_fk', CHtml::listData($modelA->relationName, 'id', 'attributeYouWantToShowOtherthanID'))

Maybe I put it wrong… I know how to display data from model B in dropdownlist in the view of model A. And this is not the issue of my post. I have figured it out - thanks to yii forums.

Let’s assume that in the view.php of model A which displays details of record of this model I have:

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

	'data'=>$model,

	'attributes'=>array(

		'id',

		'name',

		'entered_by',

                 .......................

                 .......................

                 .......................

                 .......................

                 .......................

		'comments',

	),

)); 

?>

And the view displays all the details correclty, but ‘entered_by’ column contains ID to external column in another table (let’s assume from model B). And instead of ID of ‘entered_by’ I want to dispay the text value from model B where ID = ID of ‘entered by’ in model A.

Hope this clears thins a bit…

Try with:

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

        'data'=>$model,

        'attributes'=>array(

                'id',

                'name',

                'nameOfRelationWithModelB.fieldOfB', // Hope you understand this!!

                 .......................

                 .......................

                 .......................

                 .......................

                 .......................

                'comments',

        ),

)); 

?>

'nameOfRelationWithModelB.fieldOfB', // Hope you understand this!!

Unfortunately… I’m not sure. Is it something like relations method in model.php file? After it (model) has been created using gii, when dabatase/table/columns relations are defined?

I will look and try…

Is it possible to quickly get some value from Model_B to which points FK in Model_A without defined relations() in model A?

Well I started messing with relations which were not initially created by Gii, because I had not defined them at database level. However I tried to update model A and create relation to model B in it.

In Model_A.php I have:

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(

'some_fk_to_Model_B' => array(self::BELONGS_TO, 'Model_B','column_in_model_B'),

);

}

where some_fk_to_Model_B is column in model_A which is FK to Model_B and I want to get some value from Model_B…

select text_value from Model_B where id_in_Model_B = some_fk_to_Model_B (from Model_A)

I tried the approach:

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

        'data'=>$model,

        'attributes'=>array(

                'id',

                'nameOfRelationWithModelB.fieldOfB', // Hope you understand this!!

                 .......................

                'comments',

        ),

)); 

?>

but it displays ‘Not set’ value… so maybe I have incorrectly defined the relation itself?

code in Model_A.php:

'relationName' => array(self::BELONGS_TO, 'Model_B','column_in_Model_B_that_coressponds_to_Model_A'),

relationNane could be anything? Or it should be column name in Model_A which is FK to model_B?

Hope to get some help from you,

Tom

relationName should be a name by which you will reference the relation… not the column name that is a FK…

So I made relationName any namedifferent than FK column in Model_A:

'someRelationName' => array(self::BELONGS_TO, 'Model_B','related_column_in_Model_B'),

Then in the view of Model_A I have:

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

        'data'=>$model,

        'attributes'=>array(

                'id',

                'someRelationName.some_Text_Column_From_Model_B', 

                 .......................

                'comments',

        ),

)); 

?>

And I get error"

CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘someRelationName’ in where clause

///////////////

Sorry for dummy question but… Do I have to define also the relation in Model_B to Model_A?

Nobody can help me with this?

It should work…

I don’t understant from your post if you used exactly the word “someRelationName” for the relation or something else… and the same name you have to use in CGridView.

Example:

Model post

id

name

user_id <-- this is the FK for user

Model user

id

name

In model Post you create a relation

'user'=array(self::BELONGS_TO,'User',user_id);

In CGridView:

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

	'id'=>'post-grid',

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

	'filter'=>$model,

	'columns'=>array(    	

        	'id',

        	'name',

        	'user.name',

	),

));

Edit: I used CGridView becasue I had this code already and just copy/pasted… but the same idea is for CDetailView…

@mdomba

OK so I got relation defined OK

'user' => array(self::BELONGS_TO,'User','user_id');

but I’m messing with your view code… Let’s assume that we have:

Model post

id

name

user_id <-- this is the FK for user

Model user

id

name

And I want to display in the view of POST

POST_ID

POST_NAME

USER_NAME (of this post, which is user(table).name(column))

how exactly would the code look like in the Post.php?

I tried:

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

	'data'=>$model,

	'attributes'=>array(

		'id',

                'name',

	        'user.name',


	),

)); 

?>

I get an error:

CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘user.’ in where clause

and the user corresponds to relation_name used in relations() method in model - if I name reation BLABLABLABLABLA the error will be:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘BLABLABLABLABLA.’ in where clause

So what’s wrong with my view code? (or anything else…)

//////////////

One more thing - do I need back relation in User model? How the relation ‘user’ in Post.php model knows knows which column in User.php model will be referenced to?

What version of Yii are you using, I tryed with Yii 1.1.4 and it works

1.1.4

So is my view code ok?

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

        'data'=>$model,

        'attributes'=>array(

                'id',

                'name',

                'user.name',


        ),

)); 

?>

and how about back relation in User.php model? Is it needed? I have not defined relations on the database level so Gii hasn’t created any relations, I am creating them by hand…

code is OK, back relation is not needed, I’m using postgreSQL with defined FK constraints but that should not be the case…

I am using MySQL 5.1 (or some newer…) without defined FK, but if you say that it should not be the case…

More on my error:

01652: * “$model[‘author’][‘firstName’]”.

01653: * @param mixed the model. This can be either an object or an array.

01654: * @param string the attribute name (use dot to concatenate multiple attributes)

01655: * @param mixed the default value to return when the attribute does not exist

01656: * @return mixed the attribute value

01657: * @since 1.0.5

01658: */

01659: public static function value($model,$attribute,$defaultValue=null)

01660: {

01661: foreach(explode(’.’,$attribute) as $name)

01662: {

01663: if(is_object($model))

01664: $model=$model->$name;

01665: else if(is_array($model) && isset($model[$name]))

01666: $model=$model[$name];

01667: else

01668: return $defaultValue;

01669: }

01670: return $model;

01671: }

01672:

01673: /**

01674: * Generates a valid HTML ID based the name.

01675: * @return string the ID generated based on name.

01676: */

Stack trace:

#0 yii/framework/db/CDbCommand.php(272): CDbCommand->queryInternal(‘fetchAll’, 2, Array)

#1 yii/framework/db/ar/CActiveFinder.php(683): CDbCommand->queryAll()

#2 yii/framework/db/ar/CActiveFinder.php(417): CJoinElement->runQuery(Object(CJoinQuery))

#3 yii/framework/db/ar/CActiveFinder.php(142): CJoinElement->lazyFind(Object(Post))

#4 yii/framework/db/ar/CActiveRecord.php(240): CActiveFinder->lazyFind(Object(Post))

#5 yii/framework/db/ar/CActiveRecord.php(108): CActiveRecord->getRelated(‘user’)

#6 yii/framework/web/helpers/CHtml.php(1664): CActiveRecord->__get(‘user’)

#7 yii/framework/zii/widgets/CDetailView.php(201): CHtml::value(Object(Post), ‘user…’)

#8 yii/framework/web/CBaseController.php(166): CDetailView->run()

#9 yii/hrmonic/protected/views/Post/view.php(41): CBaseController->widget(‘zii.widgets.CDe…’, Array)

#10 yii/framework/web/CBaseController.php(119): require(’/home/pc131/dom…’)

#11 yii/framework/web/CBaseController.php(88): CBaseController->renderInternal(’/home/pc131/dom…’, Array, true)

#12 yii/framework/web/CController.php(798): CBaseController->renderFile(’/home/pc131/dom…’, Array, true)

#13 yii/framework/web/CController.php(739): CController->renderPartial(‘view’, Array, true)

#14 yii/hrmonic/protected/controllers/PostController.php(43): CController->render(‘view’, Array)

#15 [internal function]: PostController->actionView(‘1000001’)

#16 yii/framework/web/actions/CInlineAction.php(47): ReflectionMethod->invokeArgs(Object(PostController), Array)

#17 yii/framework/web/CController.php(300): CInlineAction->run()

#18 yii/framework/web/filters/CFilterChain.php(133): CController->runAction(Object(CInlineAction))

#19 yii/framework/web/filters/CFilter.php(41): CFilterChain->run()

#20 yii/framework/web/CController.php(1049): CFilter->filter(Object(CFilterChain))

#21 yii/framework/web/filters/CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))

#22 yii/framework/web/filters/CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))

#23 yii/framework/web/CController.php(283): CFilterChain->run()

#24 yii/framework/web/CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#25 yii/framework/web/CWebApplication.php(324): CController->run(‘view’)

#26 yii/framework/web/CWebApplication.php(121): CWebApplication->runController(‘Post/view’)

#27 yii/framework/base/CApplication.php(135): CWebApplication->processRequest()

#28 yii/hrmonic/index.php(13): CApplication->run()

#29 {main}

Sorry for replying to myself but I wanted to move up this post. Thanks to mdomba I got this working. Simply all my code was ok.

Relation in database:

Posts.user_id=Users.id

Relation in model Post.php:

'user'=array(self::BELONGS_TO,'User',user_id);

View of model Post.php:

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

        'data'=>$model,

        'attributes'=>array(

                'id',

                'name',

                'user.name',


        ),

)); 

?>

I spent hours to get this working without success, tried to mess with displaying it in view, but this was not the problem.

The problem was that ID column on Users table was not primary key. I am creating my app with my own database schema and didn’t notice that.

I have found it now in official Yii docs - In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined.

First… I didn’t pay much attention to it, I often just look at the code examples. Secondly… it says “it is recommended” not “it is a must”.

So remember all newbies to Yii - when you define relation from Model_A to Model_B make sure that at the database level table of Model_B has UNIQUE/PRIMARY KEY on column to which Model_A is pointing with its FK. Otherwise you will get error like in this post.

What’s more, I dropped FOREIGN KEY of user_id from table Posts which pointed to ID of table Users and it still works. So or Yii no FOREIGN KEY on database level is needed in table\model_A, just PRIMARY/UNIQUE key on model_B for the relation Model_A -> Model_B to work.

Hope this helps someone.

I am new tyo Yii. I am using Yii 1.1.8 and mysql 5. I have table and attributes like this

CREATE TABLE `authors` (

  `authorid` int(11) unsigned NOT NULL auto_increment,

  `biblioid` int(11) unsigned NOT NULL,

  `author` varchar(50) collate utf8_unicode_ci default NULL,

  PRIMARY KEY  (`authorid`),

  KEY `biblio_author_bibid_1` (`biblioid`),

  CONSTRAINT `biblio_author_bibid_1` FOREIGN KEY (`biblioid`) REFERENCES `biblio` (`biblioid`) ON DELETE CASCADE ON UPDATE CASCADE

);




CREATE TABLE `biblio` (

  `biblioid` int(11) unsigned NOT NULL auto_increment,

  `itemtype` varchar(10) collate utf8_unicode_ci default NULL,

  `title` varchar(200) collate utf8_unicode_ci default NULL COMMENT 'Book Title',

  `subject` varchar(30) collate utf8_unicode_ci default NULL COMMENT 'Subject',

  ............

  PRIMARY KEY  (`biblioid`)

);


CREATE TABLE `details` (

  `detailsid` int(11) unsigned NOT NULL auto_increment,

  `biblioid` int(11) unsigned default NULL,

  `type` varchar(20) collate utf8_unicode_ci default NULL,

  `detail` text collate utf8_unicode_ci,

  `filename` varchar(50) collate utf8_unicode_ci default NULL,

  PRIMARY KEY  (`detailsid`),

  KEY `biblio_details_bibid_1` (`biblioid`),

  CONSTRAINT `biblio_details_bibid_1` FOREIGN KEY (`biblioid`) REFERENCES `biblio` (`biblioid`) ON DELETE CASCADE ON UPDATE CASCADE

);


CREATE TABLE `keywords` (

  `keyid` int(11) unsigned NOT NULL auto_increment,

  `biblioid` int(11) unsigned default NULL,

  `keyword` varchar(50) collate utf8_unicode_ci default NULL,

  PRIMARY KEY  (`keyid`),

  KEY `biblio_key_bibid_1` (`biblioid`),

  CONSTRAINT `biblio_key_bibid_1` FOREIGN KEY (`biblioid`) REFERENCES `biblio` (`biblioid`) ON DELETE CASCADE ON UPDATE CASCADE

);

relation defined in biblio model is like this:

'authors' => array(self::HAS_MANY, 'Authors', 'biblioid'),

			'collegebiblios' => array(self::HAS_MANY, 'Collegebiblio', 'biblioid'),

			'details' => array(self::HAS_ONE, 'Details', 'biblioid'),

			'keywords' => array(self::HAS_MANY, 'Keywords', 'biblioid'),

in biblio/view code, i have code like the one you suggested

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

		'data'=>$model,

		'attributes'=>array(

			'biblioid',

			'itemtype',

			'title',

			'authors.author',

			'subject',

			'keywords.keyword',

but this does not return data (not set). What can I do now? I would alos like to display multiple fields in the view of biblio. Could anybody suggest what has happened?