CODEWITHSUNDEEP
phpcakephp
user1443519
user1443519

Reputation: 589

CakePHP seems to ignore foreign key

I have three tables that I'm trying to query. Right now CakePHP is only pulling data from Visits and Guests, it's incorrectly joining Rooms as I'll explian.

Visits
id   |  guest_id   |   room_id   |   arrival_date

Guests
id    |    name   |  user_id

Rooms
id    |    user_id   |  description

I'm trying to get info on all three things when querying for a visit

$visits = $this->paginate('Visit', array(
            'arrival_date >=' => $date,
            'room.user_id' => $user_id
            )
        );

Here are the relationships I have in my models

Visit model

public $hasOne = array(
    'Guest' => array(
        'foreignKey' => 'id'
    ),
    'Room' => array(
        'foreignKey' => 'id'
    )
);

Room model

public $belongsTo = array(   //user creates a room
    'User' => array(
        'className' => 'User',
        'foreignKey' => 'user_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )
);

public $hasMany = array(
    'Visit' => array(
        'foreignKey' => 'visit_id',
    ),
);

On every single query when I use a sql_dump it shows the join of Rooms as

LEFT JOIN `TestReservations`.`rooms` AS `Room` ON (`Room`.`id` = `Visit`.`id`)

It should be Room.id = Visit.room_id

I accidentally changed the hasOne relation in my room model to room_id and I noticed it joined as "Room.room_id = visit.id". This foreign key is clearly being set through this, so why isn't the one in the visit model?

Upvotes: 0

Views: 611

Answers (1)

ndm
ndm

Reputation: 60453

Your tables are setup incorrectly for that kind of relationship, respectively your relationship configuration is incorrect for your database schema.

In a has-one relationship, the other table contains the foreign key, ie the guests and rooms tables would need a visit_id column for this to work (though I don't think this is what you want, you should probably go for a differnt relationship instead).

See http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasone

foreignKey: the name of the foreign key found in the other model. This is especially handy if you need to define multiple hasOne relationships. The default value for this key is the underscored, singular name of the current model, suffixed with ‘_id’. In the example above it would default to ‘user_id’.

Likewise with your Room model, in a has-many relationship the other model contains the foreign key too, ie in the configuration the foreign key should be room_id instead of visit_id (visit_id doesn't even exist according to your schema).

Your database schema currently reflects the following relationships:

  • Visit belongsTo Guest, Room
  • Guest hasMany Visit
  • Room hasMany Visit
  • Guest belongsTo User
  • Room belongsTo User
  • User hasMany Guest, Room

I don't know about the specific needs of your application, so I can't really make further suggestions for your schema (that would be something for a new question), but generally I'd say your schema looks OK so far. Changing it so that Visit would have hasOne relations to Room and Guest is something I'd definitely discourage, a visit is something specific to a guest and a room, but it's not exclusive, so it's correct that the visits table holds the foreign keys, the other way around it would be just plain wrong because a room and a guest could then only be associated with a single visit. So having that in mind, leave your tables as they are, and change to the reflected relationships for your CakePHP models as listed above.

Note that you don't have to explicitly define the foreign key in the relationship configuration, when following the CakePHP conventions for your database schema, the correct key based on the model name is generated automatically.

Upvotes: 1

Related Questions