special cakephp model relation

Question

hi,

I'm making my first CakePHP project (apart from all the tutorials I did...).

I'm making a site for trainers and athletes in the athletics sport.

now in my database I have a user table with fields: id, username, firstname, lastname, email, password, ...

then I have 2 other tables with the fields:

trainers: id, type_id(sprint, distance, throwing, ...), user_id

athletes: id, type_id(sprint, distance, throwing, ...), user_id, trainer_id

my trainer model looks like this

<?php
class Trainer extends AppModel {
    var $name = 'Trainer';
    var $hasOne = array('User');
    var $hasMany = array(
        'Schema'=>array(
            'className'=>'Schema',
            'foreighnKey'=>'trainer_id'
        ),
        'Athlete'=>array(
            'className'=>'Athlete',
            'foreighnKey'=>'trainer_id'
        )
    );
}
?>

my Athlete model looks like this

<?php
class Athlete extends AppModel {
    var $name = 'Athlete';
    var $hasOne = array('User');
    var $belongsTo = array(
        'Trainer'=>array(
            'className'=>'Trainer',
            'foreighnKey'=>'trainer_id'
        ),
        'Schema'=>array(
            'className'=>'Schema',
            'foreighnKey'=>'schema_id'
        )
    );
}
?>

my user model is currently like this

<?php
class User extends AppModel {
    var $name = 'User'; 
    var $belongsTo = array('Usertype');
}
?>

but that should obviously change, because when I go to domain/app/trainers or domain/app/athletes I get the error:

Warning (512): SQL Error: 1054: Unknown column 'User.trainer_id' or 'User.athlete_id' in 'on clause' [CORE/cake/libs/model/datasources/dbo_source.php, line 681]

so short recapitulation: a user is either a trainer or an athlete, and I have a users table, an athletes table and a trainers table.

an athlete has one trainer and one schema a trainer has many athletes and many schemas...

the problem lies with a user being a trainer or an athlete..., what do I have to put in my models to make this work? I hope I made this clear enough :s

Answer 1

You have to define the $hasOne associations in the User model because your athletes and trainers tables contain the foreign keys. See also http://book.cakephp.org/view/1039/Associations-Linking-Models-Together#hasOne-1041

Answer 2

I don't understand the use you do of 'schema' in your models. And I think your foreign keys are wrong. So I'd say the following:

<?php

// /models/trainer.php
class Trainer extends AppModel {
    var $name = 'Trainer';
    var $hasOne = array('User');
    var $hasMany = array(
        'Athlete'=>array(
            'className'=>'Athlete',
            'foreignKey'=>'user_id'
        )
    );
}

// /models/trainer.php
class Athlete extends AppModel {
    var $name = 'Athlete';
    var $hasOne = array('User');
    var $belongsTo = array(
        'Trainer'=>array(
            'className'=>'Trainer',
            'foreignKey'=>'user_id'
        )
    );
}
?>

The problem of having a user either Athlete or Trainer is a different one. But I don't think you have to enforce it in your model. Just have a field type in your user table set to "athlete" OR "trainer" and you'll be fine.