What are table associations?
From PHP on Trax
Contents |
Active Record Table Associations
belongs_to / has_one
Both express a 1-1 relationship, the difference is mostly where to place the foreign key, which goes on the table for the class saying belongs_to. For belongs_to the foreign key from another table is in this table. For has_one there is a foreign key in another table to this table. Association names should be singular for both association types. Example:
<?
class Post extends ActiveRecord {
public $has_one = "author"; # <= singular name
}
class Author extends ActiveRecord {
public $belongs_to = "post"; # <= singular name
}
?>
The tables for these classes could look something like:
CREATE TABLE posts (
id int(11) NOT NULL auto_increment,
title varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE authors (
id int(11) NOT NULL auto_increment,
post_id int(11) default NULL,
name varchar default NULL,
PRIMARY KEY (id)
)
has_many
This expresses a 1-M relationship. There should be a foreign key in another table to this table where there are many records in that table linking to this table. Association names should be plural.
Example:
<?
class User extends ActiveRecord {
public $has_many = "notes"; # <= plural name
}
class Note extends ActiveRecord {
public $belongs_to = "user"; # <= singular name
}
?>
The tables for these classes could look something like:
CREATE TABLE users (
id int(11) NOT NULL auto_increment,
fist_name varchar default NULL,
last_name varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE notes (
id int(11) NOT NULL auto_increment,
user_id int(11) default NULL,
note_text text default NULL,
PRIMARY KEY (id)
)
has_and_belongs_to_many ( habtm )
This expresses a M-M relationship. There should be three tables. Two tables with a third joining table. Association names should be plural. The joining table name must be named table1_table2, plural names for both in alphabetical order.
Example:
<?
class Product extends ActiveRecord {
public $has_and_belongs_to_many = "categories"; # <= plural name
}
class Category extends ActiveRecord {
public $has_and_belongs_to_many = "products"; # <= plural name
}
?>
The tables for these classes could look something like:
CREATE TABLE products (
id int(11) NOT NULL auto_increment,
sku varchar default NULL,
name varchar default NULL,
price DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories (
id int(11) NOT NULL auto_increment,
name varchar default NULL,
PRIMARY KEY (id)
)
CREATE TABLE categories_products (
category_id int(11) NOT NULL auto_increment,
product_id int(11) default NOT NULL
)
Notes
If you need to define more that one of any of the above associations in the same model class, you can do the following:
<?
public $belongs_to = "assoc1,assoc2,assoc3,etc....";
?>
If you need to specify additional parameters with the association beside just the name you will have to define it as follows:
<?
public $belongs_to = array(
"paid_users" => array(
"class_name"=> "User",
"conditions" => "paid = 1"
),
"over_25" = array(
"class_name"=> "User",
"conditions" => "age > 25"
),
"notes" => null,
etc...
);
?>
