Josh Benner

A very helpful article. Especially the tricky hasAndBelongsToMany explanation.

Just one thing, shouldn't it be Director hasMany Movies?

Yep, you're right. Fixed it. Thanks!

I just googled you up and I will bookmark your blog :)

I was searching for ways (if there are any) to deploy cake php on my bluehost page.

I'm new to cakePHP and I was just wondering if there is a way for me to edit my apache config file so that the www root will point to the app/webroot of my cake application (as I tested on my machine this must be done in order for cake to work correctly).

Jelka,

When I use Cake oh bluehost, I usually just make a new subdomain for the site I'm making. If that's not an option, you can use apache's mod_rewrite to have the "base" url point to a subdirectory using the RewriteBase directive. Apache has some great documentation on mod_rewrite available at http://httpd.apache.org/docs/1.3/mod/mod_rewrite.html

Joshua... you have just answered all my questions... Thanks, great post.

One thing... Shouldn't we have:
$hasMany = array( 'Movies' => array( 'className' => 'Movie' ) );
instead of
$hasMany = array( 'Movie' => array( 'className' => 'Movie' ) );
in model director? (I'm not sure...)

Thanks again for great post...

I'll note this on my blog (although it's just starting).

Cheers

This has been most helpful. Here a sql schema I came up with:

CREATE TABLE movies (
id int(11) unsigned NOT NULL auto_increment,
title varchar(50) collate utf8_unicode_ci default NULL,
release_date datetime default NULL,
director_id int(11) unsigned NOT NULL default '0',
created datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY director_id (director_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
CREATE TABLE directors (
id int(11) unsigned NOT NULL auto_increment,
first_name varchar(50) collate utf8_unicode_ci default NULL,
last_name varchar(50) collate utf8_unicode_ci default NULL,
created datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
CREATE TABLE reviews (
id int(11) unsigned NOT NULL auto_increment,
movie_id int(11) unsigned NOT NULL default '0',
author_name varchar(50) collate utf8_unicode_ci default NULL,
body_text varchar(50) collate utf8_unicode_ci default NULL,
created datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY movie_id (movie_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE genres (
id int(11) unsigned NOT NULL auto_increment,
name varchar(50) collate utf8_unicode_ci default NULL,
created datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE movies_genres (
id int(11) unsigned NOT NULL auto_increment,
movie_id int(11) unsigned NOT NULL default '0',
genre_id int(11) unsigned NOT NULL default '0',
created datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (id),
KEY movie_id (movie_id),
KEY genre_id (genre_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Shouldn't the movies_genres table be genres_movies, following the manual's criteria for placing the referenced tables in alphabetical order?

Yep, you're right. I never even noticed that little rule. Thanks!

Glad I could help.

I'm not sure if there is a rule about whether the array keys for associations need to be plural or singular. I typically use singular as you have said, but plural works just fine. You can actually call it anything you want and it works just fine, which I find very useful when I need to associate two table fields with the same table.

Thanks for the helpful post.

I too have been having trouble getting my head around HasOne vs. BelongsTo, and your post helped me make a distinction that I think clarifies the usefulness of HasOne (examples based on my record store project):

belongsTo == many:1 relationship, the current table may have many rows referencing a single foreign key (catalog:label)

hasMany == 1:many relationship, each row in the current table may reference many foreign keys (distro:catalog)

hasOne == 1:1 relationship, here I think the manual's User--Profile example is too simplistic

I have a table called addresses, with a primary key called address_id, which is referenced by several tables: contacts, users, artists, labels, distros, and customer_addresses

Address doesn't belongTo all of those objects, but rather each hasOne address (Customer hasMany CustomerAddress, and CustomerAddress hasOne Address)

and of course,

hasAndBelongsToMany == many:many relationships, which also require an intermediary table, but where the relationship can go either way (catalog:keywords or catalog:tags)

Finally, a concise, clear, and specific description of HasOne vs BelongsTo, which, as it turns out, I was constantly getting reversed.

Thanks!

Firstly thanks for this post!
Secondly I have a question :D If I want to implement a Many to Many relation using a table with other fields like:

Users

• id
  
• name
  
• surname

Groups

• id
  
• name

Users_Groups

• user_id
  
• group-id
  
• join_date
  
• kind

how can I implement this thing?
Thanks!

Ps. sorry for my little English! :D

Thank you so much for sharing this! I was also (and still am) struggling with associations, but after several hours of studying manuals, your blog post and playing around with different models and examples I think it is somewhat clear now ;-)

I accomplished this by using a custom finder query. I will write an article on this eventually, but for now, try to make use of this custom finder query example that I used in an application. The SQL is T-SQL for Microsoft SQL Server, but the concepts are the same for other database engines. Of special note is the {$__cakeID__$} where the foreign key is inserted.

	var $hasAndBelongsToMany = array(
	    'Activity' => array('className'  => 'Activity',
	                        'joinTable'  => 'facultyWorkload_activity_links',
	                        'foreignKey' => 'faculty_id',
	                        'associationForeignKey' => 'activity_id',
	                        'unique'     => true,
	                        'finderQuery' => '
SELECT 
	[Activity].[id] as [Activity.id],
	[Activity].[name] as [Activity.name],
	[Activity].[activity_type_id] as [Activity.activity_type_id],
	[Activity].[credits] as [Activity.credits],
	[ActivityLink].[students] as [Activity.students],
	[ActivityLink].[id] as [Activity.activity_link_id]
FROM 
	[facultyWorkload_activities] AS [Activity]
	JOIN [facultyWorkload_activity_links] AS [ActivityLink] ON [ActivityLink].[activity_id] = [Activity].[id]
WHERE 
	[ActivityLink].[faculty_id] = {$__cakeID__$}
ORDER BY
	[Activity].[name] ASC'
	                  ));

How would you handle 'related movies', movies related to other movies?

'Movies' Has and Belongs to Many 'Movies'

Thanks Joshua for the very well explained blog entry, it is helpful.

I do admit that the arrows do provide a problem for decifering when it comes to the equivalent relational models... so I've taken the step of creating a few diagrams to show the ER equivalents to the models.

I know when I teach Normalisation that I use an arrow along with 1:m style notation to make sure that my students get the differences.

So with this in mind... I've completed the relevant story at http://it.swantafe.wa.edu.au/index.php?option=com_content&task=view&id=1...

All the best, Ady

Joshua

thank you for a great article! Helped me get my head around the concepts. Also I have pointed my students to this URL so they can see the concepts.

I've taken the liberty of creating ER diagrams that represent each of the associations to help my students, as well as myself [it always the way] understand the link between the two concepts. It took me a litle while to make sure I got the arrows the right way round on the two different diagrams.

Anyhow you can find the article at: http://it.swantafe.wa.edu.au/index.php?option=com_content&task=view&id=1...

All the best

Adrian
Lecturer in IT, Swan TAFE, PERTH, Western Australia

Hi,

I want to use the belongsTo feature of cake, but in both of my used tables I have the field 'name'. How can I make use of 'Country.name AS cname' feature of the queries without having to write my own queries?

Array (
	[name] => Pennsylvania,
	[abbr] => PA,
	[capitol] => Harrisburg,
	[Country] => Array(
		[name] => United States of America
		[abbr] => USA
		[capitol] => Washington D.C.
	)
)

This was exactly the explanation I had been looking for, for days. Thanks for making it so clear! They should take this and put it in the manual.

Hey Joshua nice work!

In your examples After having set all the HABTM associations in the appropriate models,how can you retrieve the 1. id field from the following table?

genres_moview

1. id
2. movie_id
3. genre_id

I think that the only solution is by setting a custom SQL (findQuery)

Any ideas ?

I will probably write an article about this technique, but I need to look into some recent changes that have been generating some noise in CakePHP community about HABTM associations first.

One option, of course, is to create a model for the link table and perform a find* on that model once you have the information to build the conditions for the find. If I understand what you're asking, you'd like to retrieve additional information from the link table with the other data being pulled by the find call on one of the related models.

What you want to do is perfectly doable, but it's not the most straight-forward thing. You're correct that it requires a custom query, but with some thought, this isn't so difficult to rule it out.

In my first Cake project, I was developing a little app that interfaced with our MSSQL system to pull data about faculty members and details about their credit load, student count, etc to process overall "workload." There was of course a table (view actually) that had a row for each faculty member, but there was also a table that listed all the possible non-course activities a professor could engage in (coaching, chairing a department, etc). So, there was a HABTM relationship between faculty and activities.

The catch was that each activity that a professor had also had a student count associated with it -- data that was best stored along with the link itself in the association link table. It took a lot of digging and eventually trudging through the source yielded the most reliable answers. Here's the custom query I used to accomplish this:

var $hasAndBelongsToMany = array(
	    'Activity' => array('className'  => 'Activity',
	                        'joinTable'  => 'facultyWorkload_activity_links',
	                        'foreignKey' => 'faculty_id',
	                        'associationForeignKey' => 'activity_id',
	                        'unique'     => true,
	                        'finderQuery' => '
SELECT 
	[Activity].[id] as [Activity.id],
	[Activity].[name] as [Activity.name],
	[Activity].[activity_type_id] as [Activity.activity_type_id],
	[Activity].[credits] as [Activity.credits],
	[ActivityLink].[students] as [Activity.students],
	[ActivityLink].[id] as [Activity.activity_link_id]
FROM 
	[facultyWorkload_activities] AS [Activity]
	JOIN [facultyWorkload_activity_links] AS [ActivityLink] ON [ActivityLink].[activity_id] = [Activity].[id]
WHERE 
	[ActivityLink].[faculty_id] = {$__cakeID__$}
ORDER BY
	[Activity].[name] ASC'
	                  )

Note the {$__cakeID__$} token -- it's an identifier that cake will substitute with the ID of the model being retrieved. I'll be honest and note that I don't have a 100% understanding of how cake handles this -- but that's part of the magic of cake: understanding the inner workings is helpful and powerful, but not always necessary :).

Hope that helps!

Before reading your article, I never really understood the difference between belongsTo and hasOne! Good stuff!

Hi Josh,
can you extend your article or write another one that explains the consequences of the various relations in the case of deleting an object.

For example should "hasMany" implicate owner ship, saying if post has many comments than if a particular post is deleted all related comments should be deleted too.

K<o>

This is an awesome explanation! I was able to build a small movie database with grouping just by understanding what everything meant! I have a somewhat stupid question though:

Genre > Director > Movie

Everything works the way I want it to for my movie collection, but how do i display the genre on the movie page using cake? Right now scaffolding automatically links director which is fine, but I'd like to see the genre as well. Sorry if this is going a little beyond what you're trying to do here, any help would be appreciated. Thanks again!

Hi Joshua. Thank you for your great post. By the way, there is a little a bug in your code.

On the class definition of "director", "genre" and "review", there should be "var" before the $belongsTo, $hasMany and $hasAndBelongsToMany (like as var $belongsTo etc.).

Also, I have a question for you: If a model belongs to different models (more than one), how can we add our model code? For example, we shall add a "Production Firm" table for Movies. Than, every movies shall be belongs to a "Production Firm" and a "Director" etc.

To add more than one association of a given nature, it's simply a matter of adding another element to the respective association definition array.

Thanks for the note about the var... I actually think you can leave 'var' out and PHP is okay with it (though I always use var).

If you need to do association lookups in both directions, you should define a given associations from each end of the relationship.

If model A belongsTo model B, then model B can also hasOne model A, etc.

That consideration will be largely dependent on how your application is structured. In the example of a movie database there are no examples where deleting one record would mandate the deletion of one of its related records.

However, if your application has content items with associated comments, deletion of an item would logically include disposing of the comment records.

There is no magic rule here, just logic. If you think procedurally and logically enough to be programming web applications, you can determine when a record needs to be deleted, I'd say. If I were to look for a "rule of thumb," I'd say it's to delete information when it loses it's potential to be used again.

Fantastic article!

I've been playing with Cake for a couple weeks and my structures just weren't working. I didn't realize that you needed to put reciprocal association in both models. Your great example really showed me where I was making my mistakes.

Thanks

I tried to run the same thing but having a strange problem.
I'm asked to remove the s at the end of the movies_id colunm in the reviews table.

Cake1.2 naturaly puts need s so why in this case the s is not needed.

this is my model

CREATE TABLE categories (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name TEXT NULL,
PRIMARY KEY(id)
);
CREATE TABLE categories_places (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
categories_id INTEGER UNSIGNED NOT NULL,
places_id INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX categories_places_FKIndex1(categories_id),
INDEX categories_places_FKIndex2(places_id)
);
CREATE TABLE categoryinfos (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
categories_id INTEGER UNSIGNED NOT NULL,
name TEXT NULL,
lang VARCHAR(50) NULL,
PRIMARY KEY(id),
INDEX categoryinfos_FKIndex1(categories_id)
);
CREATE TABLE placeimageinfos (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
placeimages_id INTEGER UNSIGNED NOT NULL,
name TEXT NULL,
lang VARCHAR(50) NULL,
PRIMARY KEY(id),
INDEX placeimageinfos_FKIndex1(placeimages_id)
);
CREATE TABLE placeimages (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
places_id INTEGER UNSIGNED NOT NULL,
name TEXT NULL,
PRIMARY KEY(id),
INDEX placeimages_FKIndex1(places_id)
);
CREATE TABLE placeinfos (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
places_id INTEGER UNSIGNED NOT NULL,
name TEXT NULL,
lang VARCHAR(50) NULL,
PRIMARY KEY(id),
INDEX placeinfos_FKIndex1(places_id)
);
CREATE TABLE places (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name TEXT NULL,
PRIMARY KEY(id)
);

What I want to do is I have some places that belong to some categories(a place can belong to many categories).And the categories and the places have language information according to language
I want to retrieves a category with all the places that belong to it with the language information that is passed.
How can I write that with cake HABTM

1) You don't put the 's' on 'movie' in the field name, since the field will point to only one movie, it is singular. So, movie_id.

2) Forming an HABTM relationship between places and categories isn't a problem, and you already have the categories_places table defined (though note that you're using plurals in your field names again!). Just define the HABTM in the direction which you plan to retrieve the data: If you want to get all places when you know the category, you define the HABTM in the category model. If you want the categories for a given place, you define HABTM in the places model. If you want to be able to go both directions, define the relationship in both models.

Hey Joshua,
I've read through the cakephp manual and the cakephp tutorial on IBM - but never understood the difference between belongsTo and hasOne. I eventually thought, I could use them interchangeably.
Really appreciate the time you took in clearly explaining the concepts.
Awesome job.
Thanks Again

Thanks for the tutorial, it's getting clearer for me. I am trying to build a small application and have been hacking to do what associations do naturally, so now I am trying to use associations to achieve it cleaner and more easily.
Thanks!

Hi, I have 2 questions.

Firstly I have a database schema and I'm rather confused about how cake handles associations.

Does cakes implementation of the associations mean that the foreign key relationships don't have to be defined in the database?

Looking at Anon's database schema above, he/she has created a database schema with no foreign keys. Does Cake require the database design to be ridden of Foreign Key? And if not how does it deal with Foreign Key settings such as "On Update" and "On Delete" when Cake sends calls like save() and delete() to the database.

This is my one major road blocker at the moment. I would generally prefer to implement my db with all the necessary associations. It makes it functional across different applications since it is itself an individual tier.

Any idea what I should do?

Hello, nice tutorial but i have a problem because if i do this in a controller :
$this->Movie->findAll();
i will have a big table with all attributes of all tables but if i want only some attributes i can't filter the attributes of the associations tables. somebody know how i can do ?

Thanks for the good explanation. Can you expand on how one would define child models in an add view? I have a case where there will always be ten child models for every parent. When a new parent has been created, I'd like to have the ten child models fields defined on the view.

Thanks again!

Hi. a Very good tutorial, and many useful comments below !
First, please excuse my English since I'm French.

I've a problem with HABTM ( hasAndBelongsToMany ) relations.

like in the cake's manual examples, i have 3 things: Posts, Tags, and a relation table for them.

Assuming that a post haveAndBelongsToMany tags, i edited the Post Model and all works perfectly while i just want to retrieve data, but in the other hand, I'm being crazy by trying to save a new Post with tags ( some are new, other already exists ) and the relation between them.

At this point, I've few problems:

• How to save only those who doesn't already exists ( without querying db for each )
  
• How to do considering that the the relation tab must be filled -after- the new tags and posts have been created ? ( since the relation in constituted by a couple of primary key ( tags & posts )).

Anyhow, thanx a lot for this tutorial !

Can you please post, how the related-table (foreign-keys) look like for this example?

Just wanna say thanks for the great explanation :). Im doing some catalogue for personal use and i didnt know how to do the models part on CakePhp, now is all clear to me :).

Hi, I've followed the steps described but the controllor is not generating the associations when querying. Please see details below:

DB Structure:

CREATE TABLE categories (
id INT(10) NOT NULL AUTO_INCREMENT,
title VARCHAR( 100 ) NOT NULL,
created DATETIME NOT NULL ,
modified DATETIME NOT NULL,
PRIMARY KEY(id)
) ENGINE=INNODB;

CREATE TABLE blogs (
id INT(10) NOT NULL AUTO_INCREMENT,
category_id INT(10) NOT NULL,
title VARCHAR(100) NOT NULL,
body TEXT NOT NULL,
allow_comments INT(1) DEFAULT '0',
created DATETIME NOT NULL ,
modified DATETIME NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=INNODB;

CREATE TABLE comments (
id INT(10) NOT NULL AUTO_INCREMENT,
blog_id INT(10) NOT NULL,
body TEXT NOT NULL ,
approved INT(1) DEFAULT '0',
created DATETIME NOT NULL ,
modified DATETIME NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (blog_id) REFERENCES blogs(id)
) ENGINE=INNODB;

Models:

class Category extends AppModel
{
var $name = 'Category';
//Relationships
var $hasMany = array(
'Blog' => array(
'className' => 'Blog',
'foreignKey' => 'category_id',
'order' => 'Blog.created DESC',
'dependent'=> true
)
);
//Rules
var $validate = array(
'title' => array(
'rule' => array('minLength', 1)
)
);
}

class Blog extends AppModel
{
var $name = 'Blog';
//Relationships
var $belongsTo = 'Category';
var $hasMany = array(
'Comment' => array(
'className' => 'Comment',
'conditions' => 'Comment.approved = 1',
'order' => 'Comment.created DESC'
)
);
//Rules
var $validate = array(
'title' => array(
'rule' => array('minLength', 1)
),
'body' => array(
'rule' => array('minLength', 1)
)
);
}

class Comment extends AppModel
{
var $name = 'Comment';
//Relationships
var $belongsTo = 'Blog';
//Rules
var $validate = array(
'body' => array(
'rule' => array('minLength', 1)
)
);
}

Controller:

class HomeController extends AppController {
//mandatory
var $name = 'Home';
//layout used on view
var $layout = 'blog';
//Models used in the controller
var $uses = array('Categories','Blogs');
//Helpers used in the view;
var $helpers = array('Form','Html','Javascript','Ajax','Date');

//Main method
function index() {
//get all categories
$this->set('cats', $this->Categories->findAll());
}
}

----------

Even though everything seems fine, $cats variable does not contains associated nodes! (it should containg all the related blog elements per category!):

Array
(
[0] => Array
(
[Categories] => Array
(
[id] => 2
[title] => Other Info
[created] => 2008-03-12 00:27:36
[modified] => 2008-03-12 00:27:36
)

)

[1] => Array
(
[Categories] => Array
(
[id] => 3
[title] => Oracle SOA Suite 10.1.3.3
[created] => 2008-03-12 00:27:44
[modified] => 2008-03-12 00:27:44
)

)

[2] => Array
(
[Categories] => Array
(
[id] => 4
[title] => SOA Solution Architecture
[created] => 2008-03-12 00:27:53
[modified] => 2008-03-12 00:27:53
)

)

)

I have a problem in my cake aplication, i have a associate table which associate 'pages' and 'module', but in this table i have other columm 'position'. When I save, $this->date dont send de attribute position.

When cake save, it save only 'page_id' and 'module_id'.

how i insert this data with 'position'?

I haven't done any Cake code in quite a while, but what you're trying to do sounds like you may need a custom query to store the HABTM relationship. If you do some searching on the Cake Google Group you should be able to find what you're looking for. You can also post there, as the community is filled with some very intelligent, helpful people.

Check this thread: http://groups.google.com/group/cake-php/web/frequent-discussions