Documentation
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_User s. 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, 'cascade_delete' => false, ) );
© FuelPHP Development Team 2010-2019 — FuelPHP is released under the MIT license.
Many To Many
A relation of type ‘Many To Many’ provides a more complex connection with the ability to use an intermediate entity for the connection. The relation requires the through option with similar rules as target .
‘Many To Many’ relations are, in fact, two relations combined together. This relation requires an intermediate (pivot) entity to connect the source and target entities. Example: many users have many tags, many posts have many favorites.
The relation provides access to an intermediate object on all the steps, including creation, update and query building.
#Definition
To define a ‘Many To Many’ relation using the annotated entities’ extension, use (attention, make sure to create pivot entity):
use Cycle\Annotated\Annotation\Relation\ManyToMany; use Cycle\Annotated\Annotation\Entity; #[Entity] class User < // . #[ManyToMany(target: Tag::class, through: UserTag::class)] protected array $tags; public function getTags(): array < return $this->tags; > public function addTag(Tag $tag): void < $this->tags[] = $tag; > public function removeTag(Tag $tag): void < $this->tags = array_filter($this->tags, static fn(Tag $t) => $t !== $tag); > >
use Cycle\Annotated\Annotation\Column; use Cycle\Annotated\Annotation\Entity; #[Entity] class UserTag
use Cycle\Annotated\Annotation\Relation\ManyToMany; use Cycle\Annotated\Annotation\Column; use Cycle\Annotated\Annotation\Entity; #[Entity] class Tag < #[Column(type: 'primary')] private int $id; #[Column(type: 'string')] private string $name; public function __construct(string $name) < $this->name = $name; > public function getName(): string < return $this->name; > >
By default, ORM will generate FK and indexes in through entity using the role and primary keys of the linked objects. Following values are available for the configuration:
Option | Value | Comment |
---|---|---|
load | lazy/eager | Relation load approach. Defaults to lazy |
cascade | bool | Automatically save related data with parent entity. Defaults to false |
innerKey | string | Inner key name in source entity. Defaults to a primary key |
outerKey | string | Outer key name in target entity. Defaults to a primary key |
throughInnerKey | string | Key name connected to the innerKey of source entity. Defaults to _ |
throughOuterKey | string | Key name connected to the outerKey of a related entity. Defaults to _ |
throughWhere | array | Where conditions applied to through entity |
where | array | Where conditions applied to a related entity |
orderBy | array | Additional sorting rules |
fkCreate | bool | Set to true to automatically create FK on throughInnerKey and throughOuterKey. Defaults to true |
fkAction | CASCADE, NO ACTION, SET NULL | FK onDelete and onUpdate action. Defaults to SET NULL |
fkOnDelete | CASCADE, NO ACTION, SET NULL | FK onDelete action. It has higher priority than . Defaults to @see |
indexCreate | bool | Create index on [throughInnerKey, throughOuterKey]. Defaults to true |
collection | string | Collection type that will contain loaded entities. By defaults uses Cycle\ORM\Collection\ArrayCollectionFactory . Read more about relation collections. |
You can keep your pivot entity empty, the only requirement is to have defined a primary key.
#Usage
To associate two entities using Many To Many relation, use proper way depended on collection type you use. In our example we use default collection factory Cycle\ORM\Collection\ArrayCollectionFactory :
Read more about relation collections here.
$user = new User(); $user->setName("Antony"); $user->addTag(new Tag("tag a")); $manager = new \Cycle\ORM\EntityManager($orm); $manager->persist($user); $manager->run();
Disassociation will remove the UserTag entity, and not the Tag entity.
#Loading
Use the method load of your Select object to preload data of related and pivot entities:
$users = $orm->getRepository(User::class) ->select() ->load('tags') ->fetchAll();
Once loaded, you can access the related entity data using the collection:
$users = $orm->getRepository(User::class) ->select() ->load('tags') ->fetchAll(); foreach ($users as $u) < print_r($u->getTags()->toArray()); >
#Accessing Pivot Entity
If you use Cycle\ORM\Collection\DoctrineCollectionFactory for ‘Many To Many’ relation, you have the ability to access the pivot entity’s data using the Cycle\ORM\Collection\Pivoted\PivotedCollectionInterface object. You can do that using the getPivot method:
use Cycle\Annotated\Annotation\Relation\ManyToMany; use Cycle\Annotated\Annotation\Entity; use Cycle\ORM\Collection\Pivoted\PivotedCollection; #[Entity] class User < // . #[ManyToMany(target: Tag::class, through: UserTag::class, collection: 'doctrine')] public PivotedCollection $tags; public function __construct() < $this->tags = new PivotedCollection(); > >
$users = $orm->getRepository(User::class) ->select() ->load('tags') ->fetchAll(); foreach ($users as $user) < foreach ($user->tags as $tag) < print_r($tag); print_r($user->tags->getPivot($tag)); > >
You can change the values of this entity as they will be persisted with the parent entity. This approach allows you to easier control the association between parent and related entities.
For example, we can add a new property to our UserTag :
use Cycle\Annotated\Annotation\Relation\ManyToMany; use Cycle\Annotated\Annotation\Entity; use Cycle\Annotated\Annotation\Column; #[Entity] class UserTag < #[Column(type: 'primary')] private int $id; #[Column(type: 'datetime', default: null)] private \DateTimeInterface $created_at; public function __construct(\DateTimeInterface $d) < $this->created_at = $d; > >
Now we can assign this entity to the newly created connection:
$u = new User(); $u->setName("Antony"); $tag = new Tag("tag a"); $u->tags->add($tag); $u->tags->setPivot($tag, new UserTag(new \DateTimeImmutable())); $t->persist($u); $t->run();
#Filtering
Similar to Has Many the entity query can be filtered using the with method:
$users = $orm->getRepository(User::class) ->select() ->distinct() ->with('tags') ->fetchAll();
You can filter the entity results using the where method on related properties:
$users = $orm->getRepository(User::class) ->select() ->distinct() ->where('tags.name', 'tag a') ->fetchAll();
Following SQL will be produced:
SELECT DISTINCT `user`.`id` AS `c0`, `user`.`name` AS `c1` FROM `users` AS `user` INNER JOIN `user_tags` AS `user_tags_pivot` ON `user_tags_pivot`.`user_id` = `user`.`id` INNER JOIN `tags` AS `user_tags` ON `user_tags`.`id` = `user_tags_pivot`.`tag_id` WHERE `user_tags`.`name` = 'tag a'
#Chain Filtering
Pivot entity data is available for filtering as well, you must use the keyword @ to access it.
$hour = new \DateInterval("PT40M"); $users = $orm->getRepository(User::class) ->select() ->distinct() ->where('tags.@.created_at', '>', (new \DateTimeImmutable())->sub($hour)) ->fetchAll();
You can also load/filter the relations assigned to the pivot entity.
$users = $orm->getRepository(User::class) ->select() ->distinct() ->where('tags.@.subRelation.value', $value) ->fetchAll();
Cross-database Many To Many relations are not supported yet.
#Complex Loading
You can load related data using conditions and sorts applied to the pivot table using the option load .
For example, we can have the following entities:
- category (id, title)
- photo (id, url)
- photo_to_category (photo_id, category_id, position)
$categories = $orm->getRepository('category')->select();
We can now load categories with photos inside them ordered by photo_to_category position using a WHERE IN or JOIN query:
$result = $categories->load('photos', [ 'load' => function (\Cycle\ORM\Select\QueryBuilder $q) < $q->orderBy('@.@.position'); // @ current relation (photos), @.@ current relation pivot (photo_to_category) > ])->fetchAll();
SELECT "category"."id" AS "c0", "category"."title" AS "c1" FROM "categories" AS "category"
SELECT "l_category_photos_pivot"."id" AS "c0", "l_category_photos_pivot"."position" AS "c1", "l_category_photos_pivot"."photo_id" AS "c2", "l_category_photos_pivot"."category_id" AS "c3", "category_photos"."id" AS "c4", "category_photos"."url" AS "c5" FROM "photos" AS "category_photos" INNER JOIN "photo_category_positions" AS "l_category_photos_pivot" ON "l_category_photos_pivot"."photo_id" = "category_photos"."id" WHERE "l_category_photos_pivot"."category_id" IN (1, 2, 3, 4) ORDER BY "l_category_photos_pivot"."position" ASC
We can force the ORM to use a single query to pull the data (useful for more complex conditions):
$result = $categories->load('photos', [ 'method' => \Cycle\ORM\Select::SINGLE_QUERY, 'load' => function (\Cycle\ORM\Select\QueryBuilder $q) < $q->orderBy('@.@.position'); // @ current relation (photos), @.@ current relation pivot (photo_to_category) > ])->orderBy('id')->fetchAll();
SELECT "category"."id" AS "c0", "category"."title" AS "c1", "l_l_category_photos_pivot"."id" AS "c2", "l_l_category_photos_pivot"."position" AS "c3", "l_l_category_photos_pivot"."photo_id" AS "c4", "l_l_category_photos_pivot"."category_id" AS "c5", "l_category_photos"."id" AS "c6", "l_category_photos"."url" AS "c7" FROM "categories" AS "category" LEFT JOIN "photo_category_positions" AS "l_l_category_photos_pivot" ON "l_l_category_photos_pivot"."category_id" = "category"."id" INNER JOIN "photos" AS "l_category_photos" ON "l_category_photos"."id" = "l_l_category_photos_pivot"."photo_id" ORDER BY "category"."id" ASC, "l_l_category_photos_pivot"."position" ASC