Help on models and relations

Hi Guys.

I need some help solving one issue with the models and their relations, which I assume is causing some problems when Yii needs to join two tables.

Database model:

The main problem is with the table users_achievements. The correct way to do this, is to have a unique achievementID in the achievements table, but what identifies a single achievement is a (gameId, achievementID), examples: (achivement nr 1 of game A), (achievemente nr 2 of game . Thus, the table users_achievement will get this composite primary key as part of its own key.

The problem is that I don’t know how to correctly place the relations on the Models so that when joining the join is made on the correct keys.

Can anybody help me on this ?

Do I need to provide more information ? Examples ?

Thanks in advance.

Nuno

Anyone willing to help me out here ?

Thanks

IMHO I would create a model with user_achievements, and that model to be related to the three - user, game, achievement. Then from user, relate to user_achievements to get all achievements, or only those related to a model. It is much easier…

Hi Antonio

That’s what I tried to do, the problem is that the resulting join is not joining on the right keys

The current relations related to achievements I have are the following:

user has many user_achievements

user has many trophies through user_achievements

user_achievements belongs to user

user_achievements has many achievements

achievements belongs to user_achievements

achievements has many users through user_achievements

The output join when getting the user_achievements + achievements is only using one of the keys, gameId and not trophyId + gameId …

Any ideas ?

Thanks for replying…

Nuno

Maybe this helps you to clear it: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-with-through

I’ve already read that page several times and I can’t get Yii to join on the right keys

I usually get something like “on achievements.gameId = user_achievements.userId” on the SQL statement which is wrong and I don’t understand why i happens.

If I can’t make it work, I’ll create a sample page with logging so that it is easier to understand what I mean.

Can we see the relations that you wrote on your User model?

Ok

Here it goes.

User.php

	public function relations()

	{

		return array(

			'games_users' => array(self::HAS_MANY, 'UserGames','userId'),

			'games' => array(self::HAS_MANY, 'Games', 'gameId', 'through' => 'games_users'),

			'ach_users' => array(self::HAS_MANY, 'UserAchievements', 'userId'),

			'achievements' => array(self::HAS_MANY, 'Achievements', 'achievementId,gameId', 'through' => 'ach_users'),

		);

	}

UserAchievements.php

	public function relations()

	{

		return array(

			'achievements' => array(self::HAS_MANY, 'Achievements', 'achievementId,gameId'),

			'game' => array(self::BELONGS_TO, 'Games','gameId'),

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

		);

	}

Trophies.php

	public function relations()

	{

		return array(

			'ach_users' => array(self::BELONGS_TO, 'UserAchievements', 'achievementId,gameId'),

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

			'game'=> array(self::BELONGS_TO, 'Games', 'gameId'),

		);

	}

This is what I have currently, and I’m not sure if everything is as it should, because I’ve tested a lot of things…

What do you think ?

What are the primary keys of UserGames and UserAchievements? I do not see any of them on the DB model you show above. There is a big mess on the relations also…

I would include a primary in two tables, UserGames and Achievements, use the UserAchievements as a link table… Use HAS_MANY relation from user to UserGames, and use UserGames to link to Games, and MANY_MANY with Achievements through the user_achievements table -you could even use this table to relate to Games model.

       public function relations()

        {

                return array(

                        // in the model you have users_userId... what is exactly the foreign key to users?

                        // please add a primary key to the model UserGames!!!

                        // also if you have a relation from UserGames to Games models you can easily

                        // access the game, and even specify one of them by using scopes() on UserGames

                        'games_users' => array(self::HAS_MANY, 'UserGames','userId'), 

                        // here is the same, if you wish to select only those achievements for certain game, you

                        // can use scopes() to modify the condition of this relation to select only those of specific gameId

                        'achievements' => array(self::MANY_MANY, 'Achievements', 'user_achievements(userId,achievementId)'),

                );

        }

I would consider reviewing the DB tables. I do not see too hard to relate one to another if the tables are properly set.

Cheers

Hi Antonio

You are right, this is a mess, I’m sorry about that…

The reason I’m not using MANY_MANY is because I have extra fields on the relations table which a MANY_MANY relation while fecthing the model doesn’t allow (or I didn’t find how) to get those values. If I find out how to get relation fields values with a MANY_MANY relation, then I’m done Don’t need to go any deep on this.

The database model I posted, is just an example, I’m not modeling that exact database, but the relations are the same.

I’m going to do the following, I’ll create a simple example with this database model and I’ll post it here. Nevertheless, I’m able to solve the problem by using direct SQL, but I didn’t want to, and I want to take advantage of the models and their relationships.

I’ll post the above database model classes and will explain in more detail the problem.

Thanks

Nuno