Multiple INNER JOIN

Hello,

I have a situation where I need to make a query with multiple INNER
JOIN. I'm developping my sport club website and here is the situation:
users belong to teams, teams belong to seasons, teams belong to
teamcategories, and teams belong to leagues.

On the users details page, I'd like to display all the teams the user
belonged to, with the whole history. It should look like this:

Season 2009
Championship
League 1
Team n°1,
Cup
League 2
Team n°4

Season 2008
Championship
League 2
Team n°3
...

Getting only the needed data for a specific user is easy with a custom
SQL query with multiple INNER JOIN like this:

$this->set('teams', $this->User->query('SELECT * FROM ctt_teams as
Team
INNER JOIN ctt_users_teams as UserTeam ON Team.id = UserTeam.team_id
NNER JOIN ctt_teamcategories as Teamcategory ON Teamcategory.id =
Team.teamcategory_id
INNER JOIN ctt_saisons as Saison ON Saison.id = Team.saison_id
INNER JOIN ctt_ligues as Ligue ON Team.ligue_id = Ligue.id
WHERE UserTeam.user_id = ' . $id . '
ORDER BY Saison.dateDebut DESC, Ligue.order, Team.numero'));

But I'd like to know what is the best Cake way to achieve this.

I tried to use Model->unbindModel() and Model->bindModel() methods.
Even if I think I succeeded to get the needed data (and only the
needed data), I'm unable to understand how to sort these data.
And I'm wondering if what I do is correct, as playing with models
relations seems to ask to write more code that using the query()
method with my own SQL query.

Here the code I wrote with model methods:

$this->User->unbindModel(array('hasAndBelongsToMany' => array('Role',
'Team', 'Saison')));
$this->User->Team->unbindModel(array('hasMany' => array('Message'),
'hasAndBelongsToMany'=>array('User'), 'belongsTo' => array('Saison',
'Ligue')));
$this->User->Team->Saison->unbindModel(array('hasMany' => array
('Team'), 'hasAndBelongsToMany' => array('User')));
$this->User->Team->Teamcategory->unbindModel(array('hasMany' => array
('Team')));

$this->User->bindModel(array('hasAndBelongsToMany' => array('Team' =>
array('joinTable' => 'users_teams'))));
$this->User->Team->bindModel(array('belongsTo' => array('Saison' =>
array('foreignKey' => 'saison_id'))));

$this->User->recursive = 2;
$this->set('user', $this->User->read(null, $id));

Could you please tell me how you would do this ?

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---