Cakephp HABTM associations

Question

I am working on a setup where I am unsure how to get correct associations setup.

Cakephp 1.3

I am thinking something like this, please correct me if I am wrong!

database:

CREATE TABLE IF NOT EXISTS `trips` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `location_to` bigint(20) NOT NULL,
  `location_from` bigint(20) NOT NULL,
  `published` int(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE IF NOT EXISTS `locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `locations_from` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `trip_id` bigint(20) NOT NULL,
  `location_id` bigint(20) NOT NULL
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `locations_to` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `trip_id` bigint(20) NOT NULL,
  `location_id` bigint(20) NOT NULL
  PRIMARY KEY (`id`)
);

But how to setup the models TRIP and LOCATION for correct HABTM ?

Trips belongsTo USER

Trips has a from location and to location => From New York, To Miami

Locations => New York, Miami +++

Use belongsTO og hasandbelongstomany? Working with several identifiers is a first for me.

Anyone that can give some assistance? Thanks!

-Tom

Answer 1

I recommend a hasAndBelongsToMany. Remember, a trip could have multiple locations, not just two.

I would re-construct your tables like this:

CREATE TABLE IF NOT EXISTS `trips` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `published` int(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;  
CREATE TABLE IF NOT EXISTS `locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `locations_trips` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `location_id` bigint(20) NOT NULL
  `trip_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
);

and let CakePHP bake this and take care of the rest (it will automatically pick up the HABTM relationship from the common table).

EDIT: Sorry, based on your comments, I think this would be better:

CREATE TABLE IF NOT EXISTS `trips` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `locationfrom_id` bigint(20) NOT NULL,
  `locationto_id` bigint(20) NOT NULL,
  `published` int(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;  
CREATE TABLE IF NOT EXISTS `locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL
  PRIMARY KEY (`id`)
);

and then use multiple relations to the same model as below: http://book.cakephp.org/1.3/view/1046/Multiple-relations-to-the-same-model

For your example, locationfrom_id and locationto_id would belongsTo two classes that would have the same "className", which is Locations.