Two Tables Relations

Yii 1.1 General Discussion
1

Here,two tables

news (_id,subject.content,sender_id) _id is primary key

users(_id,username,email) _id is primary key

In two tables,news.sender_id = users._id

One user can have many news.

What I want to do is : how to connect two tables that I could select e.g.username in the NewsController?

My code is:

model News.php




class News extends CActiveRecord

{....

   public function relations()

  {

      return array(

	'user' => array(self::BELONGS_TO,'Users','','on' => '_id=sender_id','joinType' => 'INNER JOIN','alias' => 'Users'));

	}

....

}

model Users.php




class Users extends CActiveRecord

{.....

  public function relations()

	{

            return array(

		'news' => array(self::HAS_MANY,'News','','on' =>'sender_id=_id','joinType' => 'INNER JOIN','alias' => 'News')

		);

	}

.....

}

controller NewsController.php




class NewsController extends Controller

{....

     public function actionAdmin()

    {

.....

    $nrows = News::model()->with('Users')->findAll("Users.username = :username",

array(':username' => "zzz"));

    foreach($nrows as $rows){

	 echo $row->Users->_id;

	 echo $row->content;

	}

    }

...

}

Then I run r=news/admin returns error:

Relation "Users" is not defined in active record class "News".

Why?Help!!!

2

Should be


with('user')

See docs on "with".

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#with-detail

"Alias" is just a table alias, used for disambiguating.

3

Thank you , but it doesn’t work. Return error:




CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column '_id' in on clause is ambiguous. The SQL statement executed was: SELECT `t`.`_id` AS `t0_c0`, `t`.`rank` AS `t0_c1`, `t`.`sender_id` AS `t0_c2`, `t`.`coins` AS `t0_c3`, `t`.`display_until` AS `t0_c4`, `t`.`created_at` AS `t0_c5`, `t`.`updated_at` AS `t0_c6`, `t`.`content` AS `t0_c7`, `Users`.`_id` AS `t1_c0`, `Users`.`capital_id` AS `t1_c1`, `Users`.`score` AS `t1_c2`, `Users`.`day_bef_score` AS `t1_c3`, `Users`.`coins` AS `t1_c4`, `Users`.`stone` AS `t1_c5`, `Users`.`login_at` AS `t1_c6`, `Users`.`active_at` AS `t1_c7`, `Users`.`created_at` AS `t1_c8`, `Users`.`updated_at` AS `t1_c9`, `Users`.`mute_until` AS `t1_c10`, `Users`.`referral` AS `t1_c11`, `Users`.`cur_quest` AS `t1_c12`, `Users`.`username` AS `t1_c13`, `Users`.`password` AS `t1_c14`, `Users`.`email` AS `t1_c15`, `Users`.`icon_url` AS `t1_c16`, `Users`.`device_token` AS `t1_c17`, `Users`.`r_energy` AS `t1_c18`, `Users`.`r_laser` AS `t1_c19`, `Users`.`r_ion` AS `t1_c20`, `Users`.`r_hyperspace` AS `t1_c21`, `Users`.`r_plasma` AS `t1_c22`, `Users`.`r_combustion_drive` AS `t1_c23`, `Users`.`r_impulse_drive` AS `t1_c24`, `Users`.`r_hyperspace_drive` AS `t1_c25`, `Users`.`r_espionage` AS `t1_c26`, `Users`.`r_computer` AS `t1_c27`, `Users`.`r_astrophysics` AS `t1_c28`, `Users`.`r_star_network` AS `t1_c29`, `Users`.`r_graviton` AS `t1_c30`, `Users`.`r_weapons` AS `t1_c31`, `Users`.`r_shielding` AS `t1_c32`, `Users`.`r_armor` AS `t1_c33`, `Users`.`udid` AS `t1_c34`, `Users`.`locale` AS `t1_c35`, `Users`.`publisher` AS `t1_c36`, `Users`.`gift_checked_at` AS `t1_c37`, `Users`.`goo_user_id` AS `t1_c38`, `Users`.`sex` AS `t1_c39` FROM `news` `t` INNER JOIN `users` `Users` ON (_id=sender_id) WHERE (Users.username = :username)
4

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘_id’ in on clause is ambiguous. The SQL statement executed was: SELECT t._id AS t0_c0, t.rank AS t0_c1, t.sender_id AS t0_c2, t.coins AS t0_c3, t.display_until AS t0_c4, t.created_at AS t0_c5, t.updated_at AS t0_c6, t.content AS t0_c7, Users._id AS t1_c0, Users.capital_id AS t1_c1, Users.score AS t1_c2, Users.day_bef_score AS t1_c3, Users.coins AS t1_c4, Users.stone AS t1_c5, Users.login_at AS t1_c6, Users.active_at AS t1_c7, Users.created_at AS t1_c8, Users.updated_at AS t1_c9, Users.mute_until AS t1_c10, Users.referral AS t1_c11, Users.cur_quest AS t1_c12, Users.username AS t1_c13, Users.password AS t1_c14, Users.email AS t1_c15, Users.icon_url AS t1_c16, Users.device_token AS t1_c17, Users.r_energy AS t1_c18, Users.r_laser AS t1_c19, Users.r_ion AS t1_c20, Users.r_hyperspace AS t1_c21, Users.r_plasma AS t1_c22, Users.r_combustion_drive AS t1_c23, Users.r_impulse_drive AS t1_c24, Users.r_hyperspace_drive AS t1_c25, Users.r_espionage AS t1_c26, Users.r_computer AS t1_c27, Users.r_astrophysics AS t1_c28, Users.r_star_network AS t1_c29, Users.r_graviton AS t1_c30, Users.r_weapons AS t1_c31, Users.r_shielding AS t1_c32, Users.r_armor AS t1_c33, Users.udid AS t1_c34, Users.locale AS t1_c35, Users.publisher AS t1_c36, Users.gift_checked_at AS t1_c37, Users.goo_user_id AS t1_c38, Users.sex AS t1_c39 FROM news t INNER JOIN users Users ON (_id=sender_id) WHERE (Users.username = :username)

5

Don’t explicit the on, just show the foreign key field:


 'news' => array(self::HAS_MANY,'News','sender_id','joinType' => 'INNER JOIN')

Also the alias is not required, by default it will be ‘news’, the relation type

So, for the news has user the relation will be:


 'user' => array(self::HAS_ONE,'User','sender_id','joinType' => 'INNER JOIN')
6

ps: welcome to the forum!

7

You need to disambiguate the _id column in your ‘user’ BELONGS_TO relationship.

Edit: Didn’t see zaccaria’s post. If they suit you, his suggestions are better.

8

Thank you , bennouna and zaccaria.I have already solved the problem.That is:

I change it in the model news.php,sender_id is FK,and _id is PK. We don’t need to have the real FK,just use array(‘sender_id’=>’_id’) instead in the relations of the model.They are defined in Yii class.You can see the Yii reference.




'user' => array(self::BELONGS_TO,'Users',array('sender_id'=>'_id'),'joinType'=>'INNER JOIN','alias' => 'Users')

Finally it runs.

Thank you again.