Help resolving relationship inconsistencies

Archives Yii 1.0.x Discussions
1

Hi all.  I'm working on a simple game to cut my teeth on Yii.  However, when playing with foreign key relationships, various relationships that appear to be declared and used identically don't behave the same as other relationships.

I'm hoping someone can spot the problem, or give me some suggestions on how to determine what's going wrong.

At the Yii shell, I can do this:



>> $player = Player::model()->find();





>> echo isset($player->stats);


1


>> echo $player->stats->str;


9

All well and good.  But stats seems to be the only related table that I can access this way.



>> echo isset($player->location);





>> echo isset($player->user);





>> echo isset($player->entity);

Here's where I'm stumped: each of these properties are modeled consistently in the database and declared in their respective models' relations array.  I can't see any reason why the stats relationship should be automatically represented, but not the location, user, or entity objects. 

SQL and relations declarations for the tables in question are:



CREATE  TABLE IF NOT EXISTS `yii_re4`.`Player` (


  `idPlayer` SMALLINT NOT NULL AUTO_INCREMENT ,


  `User_idUser` SMALLINT NULL ,


  `Stats_idStats` SMALLINT NULL ,


  `Location_idLocation` SMALLINT NULL ,


  `Entity_idEntity` INT NULL ,


  PRIMARY KEY (`idPlayer`) ,


  INDEX `fk_Player_Stats` (`Stats_idStats` ASC) ,


  INDEX `fk_Player_Location` (`Location_idLocation` ASC) ,


  INDEX `fk_Player_Entity` (`Entity_idEntity` ASC) ,


  INDEX `fk_Player_User` (`User_idUser` ASC) ,


  CONSTRAINT `fk_Player_Stats`


    FOREIGN KEY (`Stats_idStats` )


    REFERENCES `yii_re4`.`Stats` (`idStats` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION,


  CONSTRAINT `fk_Player_Location`


    FOREIGN KEY (`Location_idLocation` )


    REFERENCES `yii_re4`.`Location` (`idLocation` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION,


  CONSTRAINT `fk_Player_Entity`


    FOREIGN KEY (`Entity_idEntity` )


    REFERENCES `yii_re4`.`Entity` (`idEntity` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION,


  CONSTRAINT `fk_Player_User`


    FOREIGN KEY (`User_idUser` )


    REFERENCES `yii_re4`.`User` (`idUser` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION)


ENGINE = InnoDB;





return array(


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


	'stats'=>array(self::HAS_ONE,'Stats', 'idStats' ),


	'location'=>array(self::HAS_ONE, 'Location', 'idLocation'),


	'entity'=>array(self::HAS_ONE, 'Entity', 'idEntity'),





	'skills'=>array(self::MANY_MANY, 'Skill', 'Player_has_Skill(Skill_idSkill, Player_idPlayer)'),


	'items'=>array(self::MANY_MANY, 'Items', 'Player_has_Items(Item_idItems, Player_idPlayer)'),


	'effects'=>array(self::MANY_MANY, 'Effect', 'Player_has_Effect(Effect_idEffect,Player_idPlayer)'),


);





CREATE  TABLE IF NOT EXISTS `yii_re4`.`Stats` (


  `idStats` SMALLINT NOT NULL AUTO_INCREMENT ,


  `str` INT NOT NULL DEFAULT 0 ,


  PRIMARY KEY (`idStats`) )


ENGINE = InnoDB;





return array();





CREATE  TABLE IF NOT EXISTS `yii_re4`.`User` (


  `idUser` SMALLINT NOT NULL AUTO_INCREMENT ,


  `Contact_idContact` SMALLINT NOT NULL ,


  `username` VARCHAR(45) NULL ,


  `password` VARCHAR(45) NOT NULL DEFAULT '00000' ,


  PRIMARY KEY (`idUser`) ,


  INDEX `fk_User_Contact` (`Contact_idContact` ASC) ,


  INDEX `unique_username` (`username` ASC) ,


  CONSTRAINT `fk_User_Contact`


    FOREIGN KEY (`Contact_idContact` )


    REFERENCES `yii_re4`.`Contact` (`idContact` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION)


ENGINE = InnoDB;





return array(


	'contact'=>array(self::HAS_ONE, 'Contact', 'idContact'),


);





CREATE  TABLE IF NOT EXISTS `yii_re4`.`Location` (


  `idLocation` SMALLINT NOT NULL AUTO_INCREMENT ,


  `name` VARCHAR(64) NOT NULL ,


  `desc` TEXT NOT NULL ,


  `imageURL` VARCHAR(45) NULL , 


  `Entity_idEntity` INT NOT NULL ,


  PRIMARY KEY (`idLocation`) ,


  INDEX `fk_Location_Entity` (`Entity_idEntity` ASC) ,


  CONSTRAINT `fk_Location_Entity`


    FOREIGN KEY (`Entity_idEntity` )


    REFERENCES `yii_re4`.`Entity` (`idEntity` )


    ON DELETE NO ACTION


    ON UPDATE NO ACTION)


ENGINE = InnoDB;





return array(


	'entity'=>array(self::HAS_ONE, 'Entity', 'idEntity'),


);

All of the objects are correctly set and the IDs are valid.  I can work around this by doing:



>> $player = Player::model()->find();





>> $location = Location::model()->findByPk($player->Location_idLocation);





>> echo $location->name;


The Happy Place

Suggestions on how to go about finding out what I did wrong would be appreciated. 

Thanks,

-Umo

2

Could you try these statements in Web-based scripts (e.g. SiteController/actionIndex)? Also, turn on logging and see what are the SQL statements being executed.

3

Thank you for the pointers.  Logging is definitely giving me some ideas as to what I might be doing wrong.  (Right now, my suspicion is that HAS_ONE is inappropriate on the Location object, although I can't prove it or articulate why yet).

Here's the query for the lazy load of Location through Player:

SELECT  `Player`.`idPlayer` AS t0_c0, 


  t1.`idLocation` AS t1_c0, 


  t1.`name` AS t1_c1, 


  t1.`desc` AS t1_c2, 


  t1.`imageURL` AS t1_c3, 


  t1.`route` AS t1_c4, 


  t1.`Entity_idEntity` AS t1_c5


FROM  `Player` 


LEFT OUTER JOIN  `Location` t1 ON ( t1.`idLocation` =  `Player`.`idPlayer` ) 


WHERE (


`Player`.`idPlayer` =9


)


LIMIT 0 , 30

And the result:



t0_c0	t1_c0	t1_c1	t1_c2	t1_c3	t1_c4	t1_c5


9	NULL	NULL	NULL	NULL	NULL	NULL

The join is definitely not what it needs to be, though I don't see why it's generated this way yet.

Thanks for taking a look.

Note: After playing with some of the surrounding objects, the User query began working as expected.  Location and Entity are still broken.

4

Based on your table spec, 'status', 'location' and 'entity' should be BELONGS_TO, not HAS_ONE.

5

Ok, then I've definitely mis-modeled something somewhere.  Location should be BELONGS_TO (As multiple players may share a location), but the Stats and Entity are unique per Player record and therefore need to end up as HAS_ONE.

Back to the drawing board.  Thanks a lot for the response, this has opened up a few new avenues of investigation for me, which is what I needed to get unstuck.

6

It is true that HAS_ONE and BELONGS_TO sometimes look very similar. Below is how you differentiate them:

Given table A and table B, where table A has a column (say fkB) that refers to B, then:

A belongs to B, while B may have one or many A.