Relational Query with Multiple Foreign Keys

Archives Yii 1.0.x Discussions
1

I'm trying to work with relational queries and having a bit of trouble.

My tables are set up as follows:

Games:

id (pk)

Grades:

id (pk)

SiteGames:

site_id (pk),

game_id (pk),

game_id references Games.id

GradeGames:

grade_id (pk),

site_id (pk),

game_id (pk),

grade_id references Grades.id,

(site_id, game_id) references (SiteGames.site_id, SiteGames.game_id),

game_id references Games.id,

The basic idea is that there are a group of games and some subset of that group is available to a certain site. The games available to the site can be customized even further for each grade level. Grades cannot have a game if the game is not available to their site.

In my Grades model, I have the following relation:

'availableGames' => array(self::MANY_MANY, 'Games', 'GradeGames(grade_id,game_id)'),

When running the following line of code, I get an error:

$gradeGames = Grades::model()->with('availableGames')->together()->findByPk($student->grade_id);



The relation "availableGames" in active record class "Grades" is specified with an invalid foreign key "game_id".


The foreign key does not point to either joining table.

I ended up removing the foreign key '(site_id, game_id) references (SiteGames.site_id, SiteGames.game_id)' from GradeGames and this fixed the problem.

Anybody have any idea what the problem is? I have several situations like this and don't want to have to re-order/remove my foreign keys. Plus I'd like to know what the issue is anyway since I'm trying to understand Yii better. Thanks.

2

The problem is because in your GradeGames table definition, game_id is not referencing either Games or Grades. Note that you are relating Grades and Games.

When you remove that FK constraint in your table, Yii will try to guess FK constraints based on your relation declaration in your model.

3

Qiang,

Thanks for the reply…

In response, I do actually have a FK from GradeGames to both Games and SiteGames. GradeGames.game_id references Games.id and SiteGames.game_id. The main FK I need is from GradeGames to SiteGames to ensure that Grades only receive the games available to the appropriate site. Looking at it, I don't really need the FK of GradeGames.game_id to Games.id except for doing the join in Yii. But this is a good example because I do have other tables that require multiple foreign keys on the same column.

I spent some time looking through the Yii code base and it looks like my problem is occurring in db/schema/mysql/CMysqlSchema.php in the function findConstraints($table).

On line 180, $table is using an associative array named $foreignKeys.



foreach($matches as $match)


{


	$keys=array_map('trim',explode(',',str_replace('`','',$match[1])));


	$fks=array_map('trim',explode(',',str_replace('`','',$match[3])));


	foreach($keys as $k=>$name)


	{


		$table->foreignKeys[$name]=array(str_replace('`','',$match[2]),$fks[$k]);//line 180


		if(isset($table->columns[$name]))


			$table->columns[$name]->isForeignKey=true;


	}


}

What's happening is that the first foreign key is being stored, e.g. foreignKeys['game_id'], and then being overwritten by the second foreign key on the same column.

So depending on which foreign key I create first, the relevant query works or breaks. Any thoughts or suggestions?

4

Sorry I overlooked the other FK constraints. Yeah, you are using some uncommon FK constraints: a column is referencing to two tables at the same time. This is not supported by Yii AR. I would recommend you remove one of them.

5

After getting some much needed sleep, I had come to the same conclusion about removing the FKs.

I'm guessing I'll have to join GradeGames to SiteGames to Games, which isn't really a big deal. Thanks for your help.