cakePHP - data retrieval - Model Association & database design

Question

I feel pretty darn dumb posting this question, but im completely baffled (probably because im quite new to cake and a bit intimidated)..

                      hasOne               hasOne

       donors         |      blood_groups     |       donor_types

Att ------------------+-----------------------+---------------------+           
    DonorID (pk)      |   blood_group_id (pk) |     type_id (pk)    |
    Name              |   group               |     type            |
    Surname           |                       |                     |
    type_id(fk)       |                       |                     |
    blood_group_id(fk)|                       |                     |

The Donor Model

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

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

I am already using the assoctiated models to populate a an FormHelper input in the donor registration view, all is good.

However when I try to retrieve a donor record this error occurs

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

This ofcours means that cakePHP is looking for the fk donor_id inside blood_groups table. However the relationship is the other way around and the fk is stored within donors table.

I do not know whether the db design is flawed or if the association needs to be redefined within cake. please help, as I am quite new to cakePHP and I a practically forced to use it because of its merits. I have read all the section about Association between models in the cake doc, but I am still struggling. How can I go about this?

score 2 · Accepted Answer · answered Jan 24 '14 at 00:19

2

To get this database setup, which is correct working the assocs would be:

Donor belongsTo BloodType
Donor belongsTo DonorType
BloodType hasMany Donor
DonorType hasMany Donor

But your foreign keys are wrong: Follow the CakePHP conventions!

Model fields are supposed to be lower cased and underscored, PKs are expected to be just id and FKs are the model name of the assoc, singular, underscored with suffix _id. The PKs in the donors table are right.

Why are you even changing your own convention? DonorId vs blood_group_id as PKs? However, if you want to cause a mess name them like you want but you'll have to declare them explicitly then everywhere. See linking models.

I recommend you to do the blog tutorial before messing with the framework to get a real project done.

answered Jan 24 '14 at 00:19

floriank

25,546
9
42
66

sorry about DonorID, that was a pasted earlier version, one which I changed before implementing. This question was posted twice yesterday, but it did not seem to have been posted and therefore I reposted it. This was the original post : http://stackoverflow.com/questions/21321001/cakephp-retrieving-from-database-models-associations-database/21321330?noredirect=1#comment32139276_21321330 – LogixMaster Jan 24 '14 at 08:50
Anyway, I did get the blog tutorial and actually found it pretty straight forward, as well as I have the OO background needed to understand whats happening (or so I thought, at least). In both posts, you all gave a good answer, and now I've realised the conventions are a huge factor! And also you re the only one that gave me the relationships needed i.e I was missing the hasMany rel. So Kudos for that!! Which is why I'm accepting yours as the answer Should I leave the post or delete duplicate ones ? – LogixMaster Jan 24 '14 at 08:50

score 1 · Answer 2 · answered Jan 24 '14 at 04:17

Change your Donor model as shown below:

I have added foreignKey in BloodGroup and conditions in DonorType:

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

            ),
            'DonorType' => array(
                'className' => 'DonorType',
                'conditions' => array('Donor.type_id' => 'DonorType.type_id')
            )  
        );

Reason for above error: If foreignKey is not defined in association array then CakePHP assumes that tablename_id is foreignKey.

thanks your answer is also correct, since you specified that the fk would need to be explicitly defined in the assoc if not following cake's conventions! — LogixMaster, Jan 24 '14 at 08:52

cakePHP - data retrieval - Model Association & database design

2 Answers2