Orm
Orm is short for Object
Relational Mapper which does 2 things: it maps your database table rows to objects and it allows you
to establish relations between those objects.
It follows closely the
Active Record Pattern, but was also influenced by other systems.
Relations: Many to Many
Specifies a many-to-many relationship to another model. The target model will have the same type of relationship in the other direction. To allow for these relations you need a table in between with just the 2 IDs from both sides of the relation as dual-primary key.
Keeping values with the relationship
If you need to save values with the relationship table you don't want to use ManyMany but create a model in between instead which belongs to both sides of what you would otherwise call the ManyMany relationship. It is described under "Usage with nested relationships how you can fetch all this.
Configuration Options
ManyMany relations have three additional configuration options compared to the others. All of these are still optional when using the most common naming conventions.
Option | Default | Notes |
---|---|---|
table_through | Calculated from model_to and model_from alphabetically ordered | This is the table that connects the 2 models and has both their IDs in it. For 2 models like Model_User and Model_Post it will be named posts_users by default (both plural). |
key_through_from | Calculated from the current model name | The key that matches the current model's primary key. If your current model is Model_Post this will be post_id by default |
key_through_to | Calculated from the related model name | The key that matches the related model's primary key. If your related model is Model_User this will be user_id by default |
Ordering on a column in the through table
In addition to the normal default order you can define in a relationship definition, you can also define now the records from table_through need to be ordered:
protected static $_many_many = array(
'users' => array(
'table_through' => 'posts_users', // both models plural without prefix in alphabetical order
'conditions' => array(
'order_by' => array(
'posts_users.status' => 'ASC' // define custom through table ordering
),
),
)
);
// other fields that may be required have been ommitted for this example
The conditions array can also contain where clauses, which will act as a permanent filter on the related table results.
Example
Let's say we have a model Model_Post and it has many and belongs to many
Model_Users. The ID of the Model_Post is along with the ID of the Model_User in a table
called posts_users (default order is alphabetical). That table has just 2 columns:
post_id and user_id which are together the primary key of that table.
If you keep to the defaults all you need to do is add 'users' to the
$_many_many static property of the Model_Post:
protected static $_many_many = array('users');
And you need to add a table like this one to your SQL:
CREATE TABLE IF NOT EXISTS `posts_users` (
`post_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`post_id`,`user_id`)
);
Below are examples for establishing and breaking has-many relations:
// both main and related object are new:
$post = new Model_Post();
$post->users[] = new Model_User();
$post->save();
// both main and related object already exist
$user = Model_User::find(8);
$user->posts[1] = Model_Post::find(1);
$user->save();
// break the relationship established above
$post = Model_Post::find(1);
unset($post->users[8]);
$post->save();
Full config example with defaults as values
// in a Model_Post which has and belongs to many Users
// = multiple posts per user and multiple users (authors) per post
protected static $_many_many = array(
'users' => array(
'key_from' => 'id',
'key_through_from' => 'post_id', // column 1 from the table in between, should match a posts.id
'table_through' => 'posts_users', // both models plural without prefix in alphabetical order
'key_through_to' => 'user_id', // column 2 from the table in between, should match a users.id
'model_to' => 'Model_User',
'key_to' => 'id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_SETDEFAULT,
)
);
A hybrid many-many relation
There are situations where you have a many-many relation between two tables, but you have values that are not a property of either table, but of the relation.
An example of this could be a relation between "Recipes" and "Ingredients", where a recipe has multiple ingredients, and ingredients can be used in multiple recipes. But something like how much of an ingredient is needed in the recipe is a property of the relation between the two. In this case, you can define a hybrid relation between the two:
// recipe model relationship definitions
class Model_Recipe extends /Orm/Model
{
protected static $_many_many = array(
'ingredients' => array(
'key_from' => 'id',
'key_through_from' => 'recipe_id', // column 1 from the table in between, should match a recipe.id
'table_through' => 'recipes_ingredients', // both models plural without prefix in alphabetical order
'key_through_to' => 'ingredient_id', // column 2 from the through-table, should match an ingredient.id
'model_to' => 'Model_Ingredient',
'key_to' => 'id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_SETDEFAULT, // don't delete the ingredients when deleting the recipe
)
);
protected static $_has_many = array(
'comments' => array(
'key_from' => 'id',
'model_to' => 'Model_RecipeIngredient',
'key_to' => 'recipe_id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_CASCADE, // delete the through table records on delete of the recipe
)
);
}
// ingredient model relationship definitions
class Model_Ingredient extends /Orm/Model
{
protected static $_many_many = array(
'recipes' => array(
'key_from' => 'id',
'key_through_from' => 'ingredient_id', // column 1 from the table in between, should match an ingredient.id
'table_through' => 'recipes_ingredients', // both models plural without prefix in alphabetical order
'key_through_to' => 'recipe_id', // column 2 from the through-table, should match a recipe.id
'model_to' => 'Model_Ingredient',
'key_to' => 'id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_SETDEFAULT, // don't delete the recipes when deleting the ingredient
)
);
protected static $_has_many = array(
'comments' => array(
'key_from' => 'id',
'model_to' => 'Model_RecipeIngredient',
'key_to' => 'ingredient_id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_CASCADE, // delete the through table records on delete of the ingredient
)
);
// through-table model relationship definitions
class Model_RecipeIngredient extends /Orm/Model
{
protected static $_table_name = 'recipes_ingredients';
protected static $_belongs_to = array(
'recipe' => array(
'key_from' => 'recipe_id',
'model_to' => 'Model_Recipe',
'key_to' => 'id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_SETDEFAULT,
),
'ingredient' => array(
'key_from' => 'ingredient_id',
'model_to' => 'Model_Ingredient',
'key_to' => 'id',
'cascade_save' => true,
'constraint' => \Orm\Relation::CONSTRAINT_SETDEFAULT,
)
);
}
If you're just using it as a "through-table", that table doesn't have to have its own primary key, you could define a compound primary key for it, using both the "recipe_id" and "ingredient_id" properties. Although this works fine for the hybrid relationship too, you'll have to use array('recipe_id', 'ingredient_id') for the "key_from" of the "has_many", and "key_to" of the "belongs_to" relation definitions. Depending on your requirements it might be better to give the through table its own primary "id" key (as used in this example) and use unique indexes for the foreign keys.