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.

Answer 2

belongsTo is the correct way to represent it because you have only 2 locations by trip and those 2 locations have different meanings.

HABTM would have been correct if you had defined a trip this way :

During a trip, you go to many locations and you don't know how many.

Answer 3

If you are really planing on using more than two locations for a single trip you should create a new table which associates the trips with the locations and give them an order.

CREATE TABLE IF NOT EXISTS `location_trips` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `location_id` bigint(20) NOT NULL,
    `trip_id` bigint(20) NOT NULL,
    `number` bigint(20) NOT NULL
    PRIMARY KEY (`id`)
);

The HABTM association would than be applied to the Trip model.

If you want to find trips while using the Location model as well, you have to define it there, too. ;)

Greetings

func0der