[Solar-talk] new model & a many to many question

[Paul M Jones]

On Mar 14, 2007, at 8:04 PM, Rodrigo Moraes wrote:

> Hi,
> I finally finished what I was doing to try the new model (had to hurry
> a bit :) and got already a set of models working with it.

Nice!  Glad to hear it's working for you already.  :-)


> I have basic
> question about how to set up a many to many relationship:
>
> I have three tables:
> - platform - game platforms
> - game - game titles
> - game2platform - many to many mapping of the two tables above.
>
> So, when I need a list of games in a given platform, I started with
> this in the Platform model:
>
>         $this->_addRelated('has_many', 'games',
>             array(
>                 'foreign_model' => 'Tipos_Model_Game_Game2Platform',
>                 'foreign_col'   => 'platform_id',
>             )
>         );
>
> And then the Game2Platform model has two 'has_one' definitions: for a
> platform and a game.
>
> I'm not sure however about how to setup the relationship to be able to
> order the related games from a platform (by title, creation, etc), or
> in sql words have something like this as result:
>
>     SELECT *
>     FROM game2platform
>     JOIN game ON game2platform.game_id = game.id
>     WHERE game2platform.platform_id =1
>     ORDER BY game.title
>     LIMIT 0, 30;
>
> Any advice?


The key here is a "has_many through" relationship.

In short, you want to set up something like this:

game
	has_many games2platforms
		foreign_model game2platform
  		foreign_key game_id
	has_many platforms
		foreign_model platform
		through games2platforms

game2platform
	belongs_to game
		foreign_model game
		foreign_key game_id
	belongs_to platform
		foreign_model platform
		foreign_key platform_id

platform
	has_many games2platforms
		foreign_model game2platform
		foreign_key platform_id
	has_many games
		foreign_model game
		through games2platforms

The idea is that you need to specify the mapping model  
(game2platform) as a has_many relationship, and then specify the many- 
to-many as a "has_many through" the mapping relationship.  The model  
will look at the "through" relationship and generate the correct LEFT  
JOIN for you.

Does that help at all?


--

Paul M. Jones  <http://paul-m-jones.com>

Solar: Simple Object Library and Application Repository
for PHP5.   <http://solarphp.com>

Savant: The simple, elegant, and powerful solution for
templates in PHP.   <http://phpsavant.com>