FuelPHP Orm: set() for model with many_to_many relation and table_through

I have two models, Model_Post and Model_Category. I've managed to "find" all the related data (easy as $post->$categories), but now I need to create relationships (in the posts_categories table) between a post and multiple categories upon the post's create/update/delete.

Here's Model_Post

protected static $_many_many = array(
    'categories' => array(
        'table_through' => 'posts_categories',
        'key_through_from' => 'post_id',
        'model_to' => 'Model_Category'
    )
);

Model_Category

protected static $_properties = array(
    'id',
    'name',
    'created_at',
    'updated_at'
);

protected static $_many_many = array(
    'posts' => array(
        'table_through' => 'posts_categories',
        'key_through_from' => 'id',
        'key_through_to' => 'post_id',
        'model_to' => 'Model_Post'
    )
);

posts_categories table fields: id, name.

I'm stuck here. How should I build the query?

$post->categories = Model_Category::forge()->set(array(
       // I can't get any further
    ),
);

Do I need to make a Model for the relationship table as well?

Solution

For a many to many relation to work between the Post and Category models, you should have three tables in your database: posts, categories and categories_posts.

The first two shouldn't need explanation and the third one is to handle the many/many relation between the two models. It's structure should be similar to this:

CREATE TABLE `categories_posts`
(
    `category_id` BIGINT UNSIGNED NOT NULL,
    `post_id`     BIGINT UNSIGNED NOT NULL,

    PRIMARY KEY   (`category_id`, `post_id`)
);

Being $post a Model_Post object and $post->categories an array for associated categories, we can start working.

To start associating, we forge a new Model_Category object and add it to the array:

// forge object
$category = Model_Category::forge();

// set name
$category->name = 'Brand New Category';

// associate with Posts
$post->categories[] = $category;

/**
 * we can repeat the last three steps as many times as we want
 * or as many times as we need
 */

// finally, save the relation
if ($post->save(true, true)) {
    echo "Everything OK";
} else {
    echo "Houston, we've got a problem!";
}

Notice the two boolean parameters passed to the save() method. They are to cascade through the relations and to use transactions respectively. It's a good idea to use that when relating models in one go.

You should read the ORM documentation, were you'll find a similar example on many to many relations, among other things.