Proper way to handle pivot data operations (via has_many "through")

Question

In a basic blog application, I have an articles module which allows me to select the related categories. There are 3 tables; articles, article_categories, categories. 2 models, Model_Article and Model_Category extending ORM with has-many 'through' relationship assigned accordingly. During 'Edit Article' form submission, what is the proper way (or better way) to:

Retrieve all available category ids in array format to populate Form::select()
Retrieve assigned category ids from pivot table, in array format for selected categories
Update pivot table upon successful form submission, where some pivot data already exist. Preferably checking existing data before add/remove the relevant ones (instead of deleting all existing pivot data then adding all submitted category ids).

Basically I'm hoping there's a way to get the pivot data arrays without looping through the ORM objects.

Not sure if what I'm asking for can only be done via database prepared statements/query builder. If so, is it advisable for me to have my own methods (using prepared statements/query builder) added to my Model_Article extend ORM?

The current method I'm using are:

Get all category ids

    $categories_orm = ORM::factory('article_category')->find_all();
    foreach($categories_orm as $category_orm) {
        $categories[$category_orm->id] = $category_orm->name;
    }

Get selected category ids

    $categories_current = $article->article_categories->find_all();
    foreach($categories_current as $category){
        $categories_selected = $category->id;
    }

Update category ids

    $categories_orm = $article->article_categories->find_all();
    $categories_existing = array();
    foreach($categories_orm as $category){
        $categories_existing[] = $category->id;
    }
    $categories_selected = $this->request->post('category');

    $categories_to_remove = array_diff($categories_existing,$categories_selected);
    $categories_to_add = array_diff($categories_selected,$categories_existing);
    if(!empty($categories_to_remove)) {
        $article->remove('article_categories', $categories_to_remove);
    }
    if(!empty($categories_to_add)) {
        $article->add('article_categories', $categories_to_add);
    }

Tesh · Accepted Answer · 2012-05-31T07:00:42.263

You should probably rename your relationship table to articles_categories (both plural) to follow Kohana conventions, and categories is a better alias for the relationship.

The category model should be used to retrieve all category ID's. Then you loop through it the same as your example to populate an array.

Get all categories.

$categories_orm = ORM::factory('category')->find_all();
foreach ($categories_orm as $category)
{
    $categories[$categories_orm->id] = $categories_orm->name;   
}

Make sure your select element gives post values in an array by naming it like this:

name="categories[]"

Update relationships.

$article = ORM::factory('article', $post['article_id']);

// do stuff (validate, update the article etc.)

// remove all relationships
$article->remove('categories');

// add new relationships
$article->add('categories', $post['categories']);

I would probably use hidden form fields to check against to first verify if the categories relationship needs to be updated. I'd imagine the categories for an article rarely ever change.

Proper way to handle pivot data operations (via has_many "through")

1 Answers1