Yii Framework Forum: Using additional fields in MANY_TO_MANY link table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Using additional fields in MANY_TO_MANY link table Best practice on link tables Rate Topic: -----

#1 User is offline   DerekC 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 09-December 09
  • Location:Europe

Posted 16 December 2009 - 02:17 PM

In my scenario I have Users that can belong to Domains:

User MANY_TO_MANY Domain


In each of these relationships they need additional attributes, so the link table looks like:

CREATE TABLE "user_domain" (
  "user_id"	VARCHAR NOT NULL
		CONSTRAINT fk_user REFERENCES user(id),
  "domain_id"	VARCHAR NOT NULL
		CONSTRAINT fk_domain REFERENCES domain(id),
  "role_id"	INTEGER NOT NULL
		CONSTRAINT fk_role REFERENCES role(id),
  "email"	VARCHAR NOT NULL,
  PRIMARY KEY ("user_id", "domain_id")


Examples:
user1 is Admin in domain1.com
user1 is User in domain2.com

So you can see I need to know the Role of the user in that Domain, and also what email address to use if I need to contact them.

My question is:
How should I structure the relations in the model in order to have access to the additional fields in the link table. Options are:
  • Special notation in the User.php and Domain.php to indicate there are additional fields
  • Generate a UserDomain.php model, and then have a "User ONE_TO_MANY UserDomain"
  • Other...


Any guidance would be appreciated...!
0

#2 User is offline   DerekC 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 09-December 09
  • Location:Europe

Posted 16 December 2009 - 05:15 PM

I just had a thought... I can add the multi-level relationship into the model like this:

Create a model for UserDomain.php

relations for User:
  'userDomainList' => array( self::HAS_MANY, 'UserDomain', 'domain_id', 'with'=>array('domain','role') ),


In this manner we should be able to get the UserDomainList, Domain and Role whenever we retrieve the User but I can't seem to get past the problem in this post...

I have to resort to
<?php echo Role::model()->findByPk( $userDomain->role_id )->name ; ?>
which is just plain nasty!
0

Share this topic:


Page 1 of 1
  • 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