cakePHP- retrieving from database. Models Associations & database

Question

I feel pretty darn dumb for asking this question but I'm baffled, probably because I'm slightly intimidated with cakePHP(new to it, and not very comfortable),but its merits are practically forcing me to use it. So please if you could help, I appreciate it :

My problem is with understanding how Model association works. After going through all the section about associations here I am still struggling and actually am not quite sure whether either my database design is flawed, or the associations between the models, are wrong.

Here it is : I have 3 Models, which are : Donor, BloodGroup, DonorType

To start with, I am not completely sure if I should create a model for every table in the db. My tables look like this :

+-------donors-------+
| donor_id  (pk)     |
| name               |
| surname            |
| donor_type_id (fk) |    
| blood_group_id (fk)|
|                    |
+--------------------+
+----donor_types-----+
| type_id (pk)       |
| type               |
|                    |
+--------------------+

+----blood_groups----+
| blood_group_id(pk) |
| group              |
|                    |
+--------------------+

Donor Model

class Donor extends AppModel{
    public $hasOne = array(
        'BloodGroup'=> array(
            'className' => 'BloodGroup'

        ),
        'DonorType' => array(
            'className' => 'DonorType'
        )
    );

According to the tables in the database, above is how the relationships can be described. I am already using the association to register a new donor with values retrieved from the blood group table/model, using this code in my controller:

$this->set('bloodGroups', $this->Donor->BloodGroup->find('list', array('fields' => array( 'BloodGroup.blood_group_id','BloodGroup.blood_group' ) ) ));

Therefore I think the association is correct. However, now I want to display the stored users with the relevant details, but an sql error is being generated :

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BloodGroup.donor_id' in 'on clause'

This is ofcourse because there is no 'donor_id' as a fk inside the BloodGroup table, by actually there is a blood_group_id as a fk inside the donor table. So what is the solution to this? Is the db design flawed or is there a way I can redefine the association within cake ?

Answer 1

There appear to be 2 things wrong with your setup.

1. You are not following Cake database conventions. Unless your hands are tied (e.g. external datasource you have no control over), your primary key should always be called id, not table_name_id. Cake always looks for id keys by default. If you have no choice, you can set a custom primary key by setting the primaryKey property in your model.
2. By looking at your tables, you seem to be mixing up hasOne with belongsTo. Even though in plain English it sounds logical that a donor has one blood group, from a database standpoint, the donor belongs to a certain blood group. The manual has some notes that help you pick the right one:

hasOne: the other model contains the foreign key.
belongsTo: the current model contains the foreign key.

This is also illustrated with some examples in the documentation (see links above). The latter one is true in your case, so your model should eventually look like this:

class Donor extends AppModel {

    public $belongsTo = array(
        'BloodGroup',
        'DonorType'
    );

}